Excel is extremely powerful even using just the basic functionality of adding data to cells and spreadsheets, sorting and cultivating that data into a beautiful work of cellular delight. Yet Excel is capable of far more than the standard editing of cell contents allows, through the magic of Visual Basic for Applications scripts, or VBA.
We’ll briefly explore one simple example of using VBA in Excel to extract values from one worksheet, but this is just the tip of the iceberg for what Excel and VBA can accomplish together.
Accessing the Visual Basic Editor
To begin, you’ll need to locate the Visual Basic Editor in Excel. Finding this will depend on what version of Excel you are running, but for most modern versions, the Visual Basic Editor can be found under the Developer tab of the menu ribbon.
Once located, simply click Macros, enter a macro name (we’ll use MySum for this example), then click create to open the editor and begin scripting.
The Script
Once inside the editor you’ll see the declaration of your MySum macro, which is just a standard vba function that we can edit to do our bidding.
Sub MySum()
End Sub
For our example, perhaps we have a worksheet named Sales and we want to use this macro to total (sum) the first 25 values in column B.
Thus to begin our script, we need to select the appropriate worksheet named Sales, and then using that selected object, narrow our selection by grabbing the specific cells (known as a range of cells) that we want to total.
Sub MySum()
Worksheets("Sales").Range("B1:B25")
End Sub
Now we actually need to get the total or sum of all those values, so we wrap the previous object call in a WorksheetFunction of Sum, like so:
Sub MySum()
WorksheetFunction.Sum(Worksheets("Sales").Range("B1:B25"))
End Sub
Yet that’s not the final step. In our example, we want to extract this sum total from the Sales worksheet and then insert that total value into the current cell we have selected when running this macro.
To accomplish this, we’ll use the ActiveCell object, setting its Value to be equal to our summed total:
Sub MySum()
ActiveCell.Value = WorksheetFunction.Sum(Worksheets("Sales").Range("B1:B25"))
End Sub
Alright, now our macro is complete, so let’s save and go back to our actual spreadsheet to test it out.
Running the Macro
To ensure it is functional, select a different spreadsheet, then select a single cell where you want the sum to be inserted.
To run a saved macro, just go back to the Developer tab, select Macros again, and choose your MySum macro. The cell contents should now be replaced with the sum total of the Sales column B values we specified.