You can use Visual Basic for Applications to write macros in Excel, or other Microsoft Office apps. Macros are programs that can be used to perform an action repetitively. For example, you can write a macro to get the data from two cells in two distinct columns, add it and then return the output to the user. Now, whenever you want to add the data in two different columns, you can call the macro that you wrote earlier instead of typing a formula in the formula bar each time. You can see why macros are useful- and also why it’s a good idea to learn how to create them.
Functions in VBA
In this tutorial, we’ll take a look at a couple of the most-used functions in VBA. We’ll also teach you how to write a basic function of your own. VBA has many inbuilt functions that can help you perform a wide range of tasks. These inbuilt functions are more than enough for your everyday needs. Some of these functions include:
- Date and Time functions: You can use date and time functions to insert the current system date and time. They let you handle and manipulate time values, in general.A few basic date and time functions are: DATE, MINUTE, MONTH and YEAR.
- Finance-Related functions: Excel is a spreadsheet program. It incorporates extensive support for finance related activities. Finance-related functions can be used to handle money, in general. Some of the basic functions include: IPMT, PV and SYD.
- Directory functions: Directory functions can be used to link files on the computer with the spreadsheet. Some of the common directory functions include: CHDRIVE, DIR and GETATTR.
- Logical functions: Logical functions can be used to execute code if a certain statement is satisfied. The ones most used are: AND, IF THEN ELSE and OR.
- Mathematical functions: You can use mathematical functions to manipulate numbers. The most common arithmetic functions include: INT, LOG and VAL.
- String functions: String functions let you work with strings. Some of the common ones include: RIGHT, REPLACE, LEN and STR.
If you’re going to be using VBA, you need to have a working familiarity with most of these functions.
MsgBox function in VBA
The MsgBox function is one of the most used functions in VBA. The MsgBox function creates a dialog box that can be used to display a message or return a value to the user. The dialog box has, by default, an ok button and a cancel button (just like a typical dialog box you find in Windows). The syntax for the message box function is as follows:
MsgBox(prompt[,buttons][,title][.helpfile, context])
The prompt parameter is essential. It is the message the MsgBox will display to the user. The other three parameters are optional. The title parameter is the title of the message box. If you don’t want to specify the title, the title of the application is used as default. The buttons parameter is used to select the buttons you want your message box to display. By default, the value is taken as 0 (the OK button). If you wanted to display the Yes, No and Cancel buttons, you can set the button value to 3.
Now let’s write a simple message box function that displays a message to the user. We will need to place a command button on our spreadsheet and then edit the source code, like this:
Private Sub CommandButton1_Click()
MsgBox ("This is a Message Box")
End Sub
When the user clicks on this command button, the message box will pop up:
You will find the command button in the developer tab, in the insert option. You may have to customize your ribbon to add the developer tab (it’s disabled by default).
The If Then Else function
Now let’s take a look at another easy function in VBA- the If Then Else function. You can execute two separate bodies of code with the help of this conditional statement. Depending on the condition, one of the two bodies of code you specified will be executed.
The syntax for the If Then Else function is as follows:
If your_condition Then code1_to_be_executed Else code2_to_be_executed
Let’s write a simple program to help you understand it better. Much like the example above, we’ll first place a command button on our spreadsheet and then edit the source code:
Private Sub CommandButton1_Click()
Dim a, b, c As Integer
a = 1
b = 2
c = a + b
If c < 4 Then
MsgBox ("c is less than 4")
Else
MsgBox ("c is greater than 4")
End Sub
Explanation: First, we’ve taken three variables a, b and c and set them as integer data types. We’ve specified the values of a and b. Finally, c holds the sum of the values of a and b. Our conditional statement asksVBA to print “c is less than 4” in a MsgBox if the value of c is less than 4. Otherwise, it must print “c is greater than 4”. Of course, this is a contrived example. In a real VBA program, the values of a and b might be taken from two cells in your columns. The code to be executed will also be different.
User Defined Functions in VBA
You can write user defined functions in VBA that you can call from the formula bar. These functions will usually take values from the cells in your spreadsheet, perform operations on them and return a value. For example, you can write a simple function to calculate the area of a square:
Function AreaSq(LengthofSide As Double) As Double AreaSq = LengthofSide*LengthofSide End Function
Explanation: Here, we’ve declared AreaSq and LengthofSide as Double data type (able to hold decimal values). Now, you navigate to a blank cell in the worksheet and type in the formula bar:
=AreaSq (C1)
Now, assuming there was a number value in cell C1, your blank cell will now hold the area of the square.
Code Module
Please note that you must save the function in a module before it can be called in your spreadsheet. First, navigate to the Visual Basic screen and click on the Insert menu and then choose the Module option. A blank project screen will show up. You can type your code here. A module can hold many functions – you don’t have to create a new module every time you write a new function.
The module that you create will be attached to the current workbook. It’s possible to use functions attached to other workbooks if you specify the workbook name before using the function. For example, if the AreaSq function was located in a workbook called “Book2”, you can use it by typing the following in the formula bar:
= ‘Book2.xls’ !AreaSq(C1)
You can also define a function as an Add-In. This way, you don’t need to specify the name of the workbook before you use it.
We’ve tried to walk you through the most important VBA functions
Courtesy: udemy.com