Introduction to VBA Part 1: User-Defined Functions
CIS 418
User-Defined Functions
• •
We would focus on how to write and troubleshoot simple user functions in Excel.
Some syntax we will discuss:
– Functiondeclaration
– Variablesasargumentstothefunction – Variablesinsidethefunction
– Returnedvalue
– If..Then..Else..Endif
– For To Next
Simon Business School
CIS-418 Ricky Roet-Green
2
Programming a function in VBA
• get into VBA (Developer ->Visual Basic or Press Alt+F11)
• Select the file you are working in from the project panel
• Insert a new module (Insert -> Module)
Function divideAbyB (A,B)
Rem This function divides A by B divideAbyB = A/B
End Function
• Get out of VBA (Press Alt+Q)
Simon Business School CIS-418 Ricky Roet-Green
3
Syntax of a function
• • • •
Function divideAbyB (A,B)
Rem This function divides A by B divideAbyB = A/B
End Function
Function name (divideAbyB)
Arguments (A and B)
Returned value must be the same as the name of the function Comments help maintain code (Rem … )
Simon Business School CIS-418 Ricky Roet-Green
4
Function wizard
Function wizard is available with user defined functions like with regular Excel functions
Simon Business School CIS-418 Ricky Roet-Green
5
Function description
You can add a description to your function through the Macros menus
Simon Business School CIS-418 Ricky Roet-Green
6
Function description
Now when the function is accessed from the function wizard it has a description
Simon Business School CIS-418 Ricky Roet-Green
7
Condition: If.. Then.. Else .. End if
Function divideAbyB (A,B) If B<> 0 Then
divideAbyB = A/B Else
divideAbyB = “Cannot divide by 0” End if
End Function
Simon Business School CIS-418 Ricky Roet-Green
8
Loops: For – To – Step – Next
• Variable i is a new variable defined inside the function
• As the function gets executed the values of i and addIntegersFrom1ToX
change
Simon Business School CIS-418 Ricky Roet-Green
9
Debug
The debugger is useful for checking what is going on inside the function while it is being executed
Simon Business School CIS-418 Ricky Roet-Green
10
Breakpoint
First set a breakpoint so that the execution will halt when this point in the code is reached, then execute the function
You can see the values of the variables in the locals
window
Simon Business School CIS-418 Ricky Roet-Green
11
Declare Variables
Generally it is a good idea to declare variables, VBA will then check that you did not misspell variable names
Simon Business School CIS-418 Ricky Roet-Green
12
Variable types
There are lots of different variable types, some you might find particularly useful now:
– Variant – “catch all”
– Double (numeric that might be non-integer) – Integer
Simon Business School CIS-418 Ricky Roet-Green
14
Functions can call other functions
Simon Business School CIS-418 Ricky Roet-Green
15