In VBA, there are two kinds of code blocks: procedures and functions.
- A procedure performs actions.
- A function is different: it returns a value.
If this notion still seems unclear, don’t worry: this article explains why a function is so important 😉
This notion matters even when you work with an AI. Depending on how you craft your prompt, the AI may generate code differently: either as a standalone procedure or as a procedure that calls a function.
Start by understanding the difference between a procedure and a function, and you’ll be able to guide the AI to incorporate functions effectively.
1. A first version without a function
Our example: in column A, you have the radius of 10 spheres. In column B, you want their volume. The formula for the volume of a sphere is 4/3 × π × radius³ (Thanks AI for the formula 😉)
Here is a procedure that does the job in VBA. It reads each row, from row 2 to row 11, and writes the volume directly in column B:
Sub CalculateVolumes()
Dim MyRow As Long
For MyRow = 2 To 11
Cells(MyRow, 2) = 4 / 3 * Application.Pi() * Cells(MyRow, 1) ^ 3
Next MyRow
End Sub
The code works. But look at the line with the formula: for someone who doesn’t know VBA well, it’s hard to read.
- What is Application.Pi()? 🫣
- Why ^ 3? 🤔
- And if you need the same calculation in another macro, you have to copy this line again 😤
2. The same program with a function
Functions let you isolate a calculation, give it a name, and use it wherever you need it, in your current Module or in another one 😉👍
A function starts with Function and ends with End Function. Inside, you give the result to the name of the function:
Function VolumeSphere(Radius As Double) As Double
VolumeSphere = 4 / 3 * Application.Pi() * Radius ^ 3
End FunctionNow, we can simplify the calculation line by replacing the formula with a call to the function:
Sub CalculateVolumes()
Dim MyRow As Long
For MyRow = 2 To 11
Cells(MyRow, 2) = VolumeSphere(Cells(MyRow, 1))
Next MyRow
End Sub
The result is exactly the same. But the procedure now reads like a sentence: “in column B, write the volume of the sphere of column A”.
3. What happens when the function is called
When VBA reads the line Cells(MyRow, 2) = VolumeSphere(Cells(MyRow, 1)), four things happen:
- The procedure sends the information: the value of the cell in column A is given to the function.
- The function receives the cell’s value as an argument and assigns it to the parameter named Radius:
Function VolumeSphere(Radius As Double). - The function does the calculation with the parameter Radius it has received: 4/3 × π × Radius³.
- The function returns the result: the number is stored in the name of the function, VolumeSphere, and goes back to the procedure, which writes it in column B.
What happens for the first row when radius = 1
- The procedure CalculateVolumes invokes the function VolumeSphere with the argument 1
Cells(2,1)=VolumeSphere(1) - The function VolumeSphere receives the value
- Then, VolumeSphere uses this value in its formula to calculate the sphere’s volume,
VolumeSphere = 4/3*3.1416*1^3 - The result is stored in the variable named VolumeSphere,
VolumeSphere = 4.19 - The procedure receives the result like this
Cells(2,1) = 4.19
In fact, for a function, the convention is that its name matches the variable used to store its result 😮
4. A function never starts alone
You never begin a program with a function; it is always a procedure that calls a function. This makes sense, just as in Excel. Excel offers hundreds of functions, such as SUM or VLOOKUP, but they cannot operate on their own—you need a worksheet, a cell, and data to use them.
In VBA, it’s the same: the procedure is the program’s body, and the functions are the tools it calls to do the calculations.
5. How to get optimized code from the AI
If you just ask “calculate the volume of the spheres in VBA”, the AI doesn’t know you want a function. Most of the time, it writes everything in one procedure, like version 1. The code works, but the calculation can’t be reused; /function or /sub command doesn’t exist to write a procedure or procedure + function.
/vba only tells the AI the language. You have to describe the code structure in words. The best way is to write your prompt with all the following keywords: “Put the calculation in a separate Function. The procedure only reads the cells, calls the function and writes the result.” 😮👍
/vba
Calculate the volume of the spheres. The radius is in column A, rows 2 to 11.
Put the calculation in a separate Function named VolumeSphere.
The procedure only reads the cells, calls the function and writes the result in column B.💡 This sentence works for any calculation: a VAT, a discount, a date. Add it to all your VBA prompts (see our Prompt Tips for Excel).