Using the ByVal argument in VBA is a way to pass values to a function in VBA while retaining the original value of the variable.

For example, you may have a list of names and dates of birth in your Excel sheet, and you wish to loop through this list of names, and calculate the age of each person based on the date of birth of that person.  You need to pass the date of birth of each person to a function, and return the correct age of that person.

In Excel VBA, we can write a function that will use the date of birth as a value to calculate the age of the person

Function GetAge(ByVal DOB As Date) As Integer
'use the DOB to calculate the age
    GetAge = (Now() - DOB) / 365.25
End Function

In the above example, I am sending the date of birth as a date variable to the GetAge Function in Excel.   This is then used to calculated the age of the person by using the built-in Now() function that Excel has (which returns todays date) – and then dividing the number returned by the number of days in a year.

HOT TIP:  According to Excel, day 1 is the 1^st of January 1900 so the date functionality in Excel is calculated using this – therefore the calculation will work out how many days there are between Now() and the DOB variable – each of these – although shown as a date in Excel, actually represents a number calculating the number of days since the 1^st of January 1900.

You then use the function in Excel to calculate the date of birth for the first person in your list.

You can then use the little handle in the bottom right hand corner of your cell to double-click and copy the formula down to the remaining people in the list.   The ByVal variable will pick up each individual date of birth and will use that variable to calculate each person’s own date of birth.

You can also use the ByVal variable syntax when you are using VBA forms.  For example, you might want to enter information into the forms that will then be used at a later stage in a calculation.  Say you want to buy a product –a bar of soap, for example – and the bar of soap is $1.30.   Now you want to buy 4 bars or soap, or you want to buy 6 bars of soap – and you want VBA to work out the cost of the transaction – ie the total cost for the bars of soap.

To do this, you need to pass a value to the VBA code in order for the VBA code to do the calculation.   Many VBA programs that are written use forms in which the user can type in the information, and then they use the code behind these forms to perform the calculations that are required.

For example, I run a shop and I am selecting a product to sell to my client.

When I click on Calculate, the Price and Quantity of the product will be passed to the event behind the calculate button, and will then in turn be passed to a function to calculate the total price.

The click event of the calculate button:

Private Sub cmdCalculate_Click()
    Dim dblPrice As Double
    Dim dblQty As Double
    'get the values from the form
    dblPrice = Me.txtPrice
    dblQty = Me.txtQty
    'pass the values to the CalculatePrice function and return the result
    MsgBox CalculatePrice(dblPrice, dblQty), vbInformation, "RESULT"
End Sub

Which in turn requires the Calculate Price function to return the value

Function CalculatePrice(ByVal dP As Double, ByVal dQ As Double) As Double
    CalculatePrice = Round(dP * dQ, 2)
End Function

When I click on Calculate, the following message box is returned.

I am returning the result in a message box for the purpose of thie overview.  However, once you have obtained the price, you could of course return the price to an excel sheet, or use it further on in your code.  You could also use the calculate price function directly in an Excel sheet, rather than using an VBA form.

Where B2 is the ByVal dP as double in the function, and C2 is the ByVal dQ as double in the formula – you can then add to your list of products, enter the prices and quantities ordered and the total formula and populate your sheet. 

Should the order quantity change for some reason, the Total will automatically update based on the values entered as the function will re-calculate.

A third way of using the ByVal argument is when you are writing quite complicated code which requires you to loop through a list of data, and then return and perhaps update the data based on what is returned from your function.  This is more common in VBA when you are programming in Access databases.  

For example, you may need to loop through a list of products in your Access table, obtain the Price and Quantity from the table, and then for each record in the table, you need to update the total column in the table. 

Say you have this table in Access:

You then need some code to update the price automatically.

NOTE: Yes, we can do this in a query for those of us who use Access and I will show that below, but for the sake of this example, I am going to use code to update a table.

You can then write this code in an Access module:

Sub UpdatePricing()
    Dim dbs As Database
    Dim rst As Recordset
    Dim dblPrice As Double
    Dim dblQty As Double
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("Orders", dbOpenDynaset)
    With rst
        .MoveLast
        .MoveFirst
        Do Until .EOF = True
            dblPrice = rst.Fields("ProductPrice")
            dblQty = rst.Fields("ProductQty")
            .Edit
            rst.Fields("TotalPrice") = CalculatePrice(dblPrice, dblQty)
            .Update
            .MoveNext
        Loop
    End With
    rst.Close
End Sub
Function CalculatePrice(ByVal dblP As Double, ByVal dblQ As Double) As Double
    CalculatePrice = Round(dblP * dblQ, 2)
End Function

If you then click in the UpdatePricing routine, and click Run (F5), the code will open the Orders table, loop through each record, and update the total price accordingly.

As I mentioned in my note, if you have created your table without the total price column, you can then use a query to calculate the total price, and use the CalculatePrice function to return the same information dynamically into the query.

The result of which would be:

In conclusion, this general overview of the ByVal argument in VBA has just introduced you to the tip of the iceberg in using this very useful variable functionality in VBA – it can be used directly in Excel sheets, in both Excel and Word VBA forms and of course in Access VBA modules and queries.  It can be used to store all type of variables (strings, dates, numbers etc) and adds a huge amount of flexibility to your code.