How To Use “Regular” Microsoft Excel Formulas In Visual Basic

If you are just starting out using Visual Basic or even if you are already familiar with it you might have wondered how to use “regular” formulas in Visual Basic for applications (VBA). If you are familiar with Visual Basic you are probably aware of how to setup functions and userforms etc. What you might not be aware of is that you can actually use regular Excel formulas in your VBA code. What “regular” formulas means is that any function you can use in excel you can actually copy and paste the exact same formula into visual basic.

Let’s look at an example. Suppose you wanted to use the SUM function in your visual basic code and you already have the function written in your worksheet. You could possibly have something like the following:

=SUM(A1:A15)

This will obviously sum the values in the cells from A1 through A15 and output the answer. Suppose you wanted to be able to do this in VBA with this same formula with a button and an output box (text box). In order to do this you would need to have the following when you call your function:

Private Sub button1_Click()

Me.outputbox = [SUM((B2:B15))]

End Sub

Here “button1” is the name of  a button that gets the sum of the range of cells and Me.outputbox is saying that you want the code to run in the currently open excel window (Me.) and the output should go to the created text box (outputbox). You will have to change the range in the above code to suit your needs but this code would work for its intended purposes. Basically, to use “regular” Excel formulas in your VBA code you need to surround the code with square brackets and be sure to open the sheet you want the data to be pulled from. This method works with any Excel function. It can be particularly useful for completing multiple simple tasks across different workbooks since all you need do is open a different workbook and run the code again. If you have any trouble leave a comment below!