Have you ever looked for an answer to your Excel question on the web only to find out that it requires VBA, and you don’t know VBA? This happens to many people everyday and they think “I am not a programmer”, and they give up.  I believe you do not need to know VBA, to use VBA.

VBA stands for “Visual Basic for Applications”.  VBA is closely related to VB (Visual Basic) with the exception that VBA can only run in a host product (such as Excel) while VB Can run as a stand alone program. VBA is a programming language, but as with other Microsoft products, it is much more simple than other languages.

In this article, I am going to show you how to bring code to create your own function to life. I will show you how to post the code into Excel and then how to use it.

All VBA code goes into the the Visual Basic editor window.  To open the VB editor window, press alt+f11, or go to tools=>macro=>visual basic editor. On the left side of the VB editor you will see a project window. The project window displays all of the sheets in your workbook and an icon called thisworkbook.  The first thing we will do is insert a module, I will not go into the differences in modules, sheets and this workbook, but for our purposes, all custom functions go in modules. To insert the module, right click on VBA Project in the project window. Choose insert=>module. You should now see module1 in the project window.

Now that we have a module, double click on module1. This will open a window where we will place our code. The custom function we are creating will tell us the day of the week that any date is. Copy the code below and paste it in the open window. 

Function Daytitle(InputDate As Date)
    Dim DayNumber As Integer
    DayNumber = Weekday(InputDate, vbSunday)
    Select Case DayNumber
        Case 1
            Daytitle = "Sunday"
        Case 2
            Daytitle = "Monday"
        Case 3
            Daytitle = "Tuesday"
        Case 4
            Daytitle = "Wednesday"
        Case 5
            Daytitle = "Thursday"
        Case 6
            Daytitle = "Friday"
        Case 7
            Daytitle = "Saturday"
    End Select
End Function

The result should look like this:



Now that our code is in the editor, we can close the editor (X out of the editor, do not close the whole excel workbook) and use our function.

To use your new custom function, use it as you would the built-in functions. Type the date 04/01/1922 in cell a1. In cell b1 type out your function: =daytitle(a1). The result should be “Saturday”.

That is how to use VBA, without knowing VBA. You can find hundreds if not thousands of custom functions (AKA User Defined Functions) on the web.

Next step is to begin writing your own custom functions. A good place to start is by using the macro recorder, and then looking at the code the macro recorder places in the VB Editor. Check out this article on the macro recorder to learn more.

Now go find some code!

Joseph Harris

Related posts:

  1. Removing Macros from a Workbook Q: How do I remove all the macros from my...
  2. Conditional Formatting with Multiple Criteria in Excel Microsoft Excel has a limit of 3 criteria in Conditional...
  3. Use Excel Macro Recorder to Handle Repetitive Steps If you have ever had to change the same thing...
  4. User Passwords in Excel – Multiple Passwords Simple multiple user password protection in Excel....

This entry was posted on Monday, October 27th, 2008 at 1:27 pm and is filed under Excel Hints. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

2 Responses to “Playing with VBA in Excel, Without Knowing VBA!”

  1. Excel Yogi’s » Blog Archive » Sum by the Color of a Cell - Excel on February 18th, 2009 at 3:24 pm

    [...] Surfing the web today, I came across a great user defined function (UDF) called sumcolor(). This function sums a range of cells based on the color of the cells. My head began to spin with the possibilities! Below is the VBA for the function. If you are unfamiliar with how to install a UDF, here is a User Defined Installation Guide. [...]

  2. Dag on March 11th, 2009 at 2:47 am

    Great view on the technique, I haven’t seen it done this way before

Leave a Reply

You must be logged in to post a comment.