Option Explicit
Function Add(a As Double, b As Double) As Double
Dim c As Double
c = a + b
Add = c
End Function
Option explicit forces us to declare our variables before we define them.
Function Sum_Range( A As Range ) As Double
Dim total As Double
Dim r, c As Integer
For r = 1 To A.Rows.Count
For c = 1 To A.Columns.Count
total = total + A(r, c)
Next c
Next r
Sum_Range = total
End Function
Given date in cells A1 to A5, we can call this function in Excel as:
=Sum_Range( A1:A5 )
Now, let’s write a sub-routine.
Sub Sum_Values()
Dim a, b As Double
a = Range("A1").Value
b = Range("A2").Value
Range("B1").Value = a + b
End Sub
This sub-routine is a macro. An Excel macro contains instructions to be executed. We
often use macros to eliminate the need to repeat steps of common performed tasks. We
can cause this sub-routine to run using a button. To add a button to your spreadsheet,
click open View | Toolbars | Forms. From the Forms toolbar, left-click on the button icon
and paint an area on your spreadsheet. When the Assign Macro window shows up, select
Sum_Values and click OK. When you click on the button, the sum of A1 and A2 should
appear in B1.
