When you write functions and subroutines in VBA (Visual Basic for Applications), you’ll probably take advantage of some of the built-in worksheet functions of Excel itself. VBA has some functions of its own, but to get the most functionality in VBA, you’ll need to use worksheet functions.
In this post, you’ll see how to use worksheet functions within VBA. An example worksheet has been set up for you to create your own sine and inverse since functions using VBA.
You can start by entering values in the inputs. For the angle, input:
=PI()/2
This will give you 90° in radians. You’ll later write a simple function to calculate the sine of that angle.
The input below will need the sine of an angle as the input. The sine of 90° is 1, so just enter 1 for sin(x). Next, open the VBA editor with Alt-F11. Right-click the workbook and insert a module.
Next, you’ll write a function to take the sine of an input cell. Let’s call this function MySin. Begin the function by typing:
Function MySin(
This function will have one argument, the angle:
Function MySin(angle)
As mentioned, VBA has several functions built in, and the sine function is one of them. You can just type the name of the function to use it. Therefore, your next line of code will be:
MySin=Sin(angle)
In this line, you’ve defined the MySin function to be the built-in Sin function. This function is now complete. VBA should have already added the ending line of code End Function.
Next, create a function for inverse sine called MyASin. Move your cursor below the last line of code and hit Enter. Begin the function:
Function MyASin(
This function also has one argument which you can call x.
Function MyASin(x)
When you’re working in a worksheet, the function for inverse sine is Asin. Try using the Asin function in the code like you did with the Sin function above.
MyASin=Asin(x)
Now you can go to the worksheet and test these functions. In the result cell for sine, enter the MySin function and select the angle as an argument:
=MySin(C5)
This function should return 1, the sine of .
Next, enter the MyASin function to find the inverse sine of 1.
=MyASin(C11)
When you hit Enter, you’ll get a VBA error:
VBA doesn’t recognize ASin as an internal function. However, we know that ASin is a worksheet function that’s available when typing a formula in a cell. We must reference this kind of function differently.
Click OK in the error message and switch over to the VBA editor. Click the Stop button in the toolbar to stop the code from executing.
Since Asin is a worksheet function that isn’t recognized by VBA, you’ll have to prefix the function with Application.WorksheetFunction. before Asin:
MyASin=Application.WorksheetFunction.Asin(x)
As you type the period after WorksheetFunction, VBA will show a list of all the Excel worksheet functions that you could choose from:
Since it will find a function named Asin there, the code will now work. Return to the worksheet to test it out. Double-click the cell where you entered the MyAsin function and type Enter. You should get the expected result.
VBA has some functions that work directly, but Excel has many functions that will need to be preceded by Application.WorksheetFunction. You can find a list of the functions included in VBA at this link. Any function that is not on this list will need the prefix Application.WorksheetFunction.
