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