EXCEL VBA PRIMER |
CCE 170Brigham Young University Norm Jones |

INTRO - | [Table of Contents] |

EXCEL - | [Formulas] [VLOOKUP] [Validation] [Names] [Operators] [IF Function] [Goal Seek] [Charts] [Annotation] [Array Formulas] |

VBA - | [VB Editor] [Macros] [Controls] [Ranges] [MsgBox] [Variables] [Objects] [If Then] [Debug] [Loops] [Arrays] [Strings] [User Forms] [Functions] [Excel Funs] [Subs] [Events] [Standards] |

ADVANCED - | [Graphics] [E-mail] [Files] [Apps] [Google Sheets] |

One of the nice things about writing VB code inside Excel is that you can
combine all of the power and flexibility of Visual Basic with the many tools and
options in Excel. One of the best examples of this is that you can take
advantage of all of the standard Excel worksheet functions inside your VB code. Calling an Excel worksheet function is simple. The Excel functions are
available as methods within the **WorksheetFunction** object. You
simply invoke the method and pass the arguments that the function requires
(typically a range).

For example, if we were writing a simple formula to put in a cell to find the minimum value in a range of cells, we would write the following:

```
=Min(B4:F30)
```

The following code uses the same **Min** function, but invokes the
function using VB code. The min value is stored in a variable called ** minval**:

```
Dim minval As Double
minval = Application.WorksheetFunction.Min(Range("B4:F30"))
```

Notice the difference in how the range is specified. In the VB code, the range is specified as a range object.

The Application. portion is actually optional and can be omitted in most cases. Thus, the following code achieves the same thing:

```
Dim minval As Double
minval = WorksheetFunction.Min(Range("B4:F30"))
```

Here are some more examples:

```
Range("e5") = WorksheetFunction.sum(Range("b5:b29"))
'This is useful since VB does not have an inverse sin function
Dim x As Double
x = WorksheetFunction.Asin(0.223)
Dim i As Integer
i = 5
Range("H4") = WorksheetFunction.Fact(i)
```

You may wish to complete following exercises to gain practice with and reinforce the topics covered in this chapter:

Description |
Difficulty |
Start |
Solution |

Harmonic Mean - Use an Excel function within a custom function to calculate the harmonic mean from the tabulated data. |
Easy | harmonic_mean.xlsm | harmonic_mean_key.xlsm |

Law of Cosines - Calculate the Law of Cosines using an Excel function for Cosine within your sub. |
Medium | law_of_cosines.xlsm | law_of_cosines_key.xlsm |

Bill Payment - Use the APR Excel function within a custom function to calculate the number of months required to pay off a credit card bill. |
Hard | bill_payments.xlsm | bill_payments_key.xlsm |