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
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.
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
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
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
B values we specified.