Unit 3 – Excel VBA Built-in Function

This Excel VBA tutorial explains how to calculate net present value using Excel VBA NPV Function.

You may also want to read:

Excel worksheet NPV Function

Net Present Value

Net Present Value is to calculate the present value of future cash flow. For example, your client will give you $10000 when the project completes after 1 year, assume there will be inflation, the future $10000 will be less than the present $10000. Assume the inflation is 4%, the present value of $10000 is 10000/(1+4%) = 9615.384615    This calculation is just a simple maths, you don’t even need to memorize any formula.

If your company needs to invest $9900 today in order to gain $10000 after 1 year, then you should not take this project because $9615 you will gain is less than $9900 you invest today.

If you receive $10000 after 1 year, and $5000 in the second year, then

Net present value = $10000/(1+4%) + $5000/(1+4%)²

= 9615.38 + 4622.78

= 14238.17

Excel VBA NPV Function

Instead of calculating the Net Present Value manually, Excel NVP Function provides a quick way to calculate the Net Present Value.

Excel worksheet NPV Function is similar to Excel VBA NPV Function.

Syntax of VBA NPV Function

NPV(rate,valueArray())

It is important to note that array has to start from item 0. If you start from item 1, the first cash flow item is assumed to be $0.

Example 1

Using the above example, if the discount rate is 4%, while the cash flow return is $10000 after the first year, $5000 after the second year

Public Sub testNPV()
    Dim npvArray(1) As Double
    npvArray(0) = 10000
    npvArray(1) = 5000
    MsgBox (NPV(0.04, npvArray()))
End Sub

Result

Example 2

If you have a lot of cash flow, you can type the values in worksheet first and then convert them into array in VBA.

Public Sub testNPV()
    Dim npvArray() As Double   
     For r = 2 To Range("A" & Rows.Count).End(xlUp).Row
        ReDim Preserve npvArray(r - 2)
        npvArray(r - 2) = Range("B" & r).Value
    Next r    
    MsgBox (NPV(0.04, npvArray()))
End Sub

Result

This Excel VBA tutorial explains how to use Hyperlinks Function to send email and add hyperlink to Cell.

Excel VBA Hyperlinks Function

The purpose of the Hyperlinks Function is to remove or add hyperlink to a Cell, the hyperlink can be an email or a website.

There is another Worksheet Function Hyperlink (without s).

Hyperlinks.Add Method

Syntax

expression.Add(Anchor, Address, SubAddress, ScreenTip, TextToDisplay)

Example 1 – Add website hyperlink

The below example adds a text “Access-Excel.tips” in Cell A1, and add a hpyerlink to http://access-excel.tips. ScreenTip is the yellow pop up box when you hover the move over the hyperlink.

Public Sub add_hyperlink()
    ActiveSheet.Hyperlinks.Add anchor:=Range("A1"), Address:="http://access-excel.tips", ScreenTip:="This is my website", TextToDisplay:="Access-Excel.tips"
End Sub

Example 2 – Add file hyperlink

Simply replace the website with the file path to add file link.

Public Sub add_hyperlink()
    ActiveSheet.Hyperlinks.Add anchor:=Range("A1"), Address:="C:\example.docx", ScreenTip:="Open file", TextToDisplay:="example.docx"
End Sub

Alternatively, you can insert an object as below, click here for details.

Example 3 – Add Email hyperlink

The below example adds a text “Email me” in Cell A1, and add an email hpyerlink to my email address.

Public Sub add_hyperlink()
    ActiveSheet.Hyperlinks.Add anchor:=Range("A1"), Address:="mailto:scammera1@yahoo.com.hk", ScreenTip:="This is my email", TextToDisplay:="Email me"
End Sub

Click on the link to open the default email application.

There are other methods to send email via Excel VBA where you can do more customization, click on the below post.

Send Email in Excel VBA using CDO and OLMailItem

Remove Hyperlink using Hyperlinks.Delete Method

The below example removes all hyperlinks in active worksheet.

Public Sub del_hyperlink()
    ActiveSheet.Hyperlinks.Delete
End Sub

The below example removes hyperlink in Cell A1. You can customize the If Condition to suit your needs such as removing hyperlink of specific range value.

Public Sub del_hyperlink()
    For Each hyper In ActiveSheet.Hyperlinks
        If hyper.Range.Address = "$A$1" Then
            hyper.Delete
        End If
    Next
End Sub

Outbound References

https://msdn.microsoft.com/en-us/library/office/ff822490.aspx

 What does VBA Excel Split Function do?

VBA Excel Split Function separates a text by delimiter into an array, for example, you can split a text a;b;c;d into array {a,b,c,d}, afterwards you can get the desired array item (starting from 0). Split Function is similar to the action of “Text to Columns”.

Syntax of VBA Excel Split Function

Split(text,[delimiter],[limit],[compare])

Example of Excel VBA Split Function

The below code assign an array name arr() to the split array, and then loop through all array items to count number of “Mary” (case insensitive)

Public Sub convtArray1()
    Dim arr() As String
    arr() = Split("Mary Ann MARY", " ")
    For i = 0 To UBound(arr())
        If UCase(arr(i)) = "MARY" Then
            Count = Count + 1
        End If
    Next i
    MsgBox (Count)
End Subb

Outbound References

https://msdn.microsoft.com/en-us/library/6x627e5f%28v=vs.90%29.aspx