Last Updated on January 11, 2023 by token
Introduction to functions in Excel VBA
You have already come to the chapter about VBA functions – Congratulations !!! In VBA we have a large number of useful functions. Using them our programs work in the right way. With their use, you will do mathematical activities, manipulate text data and dates.
You can reference the function in VBA in a standard way by entering the function name, arguments and parentheses:
The VBA functions can be group into the following categories:
- Character functions (string functions)
- Mathematical functions
- Date and time functions
- Logical functions
- Excel Sheet functions – sheet functions
- User defined functions (UDF)
- Other functions
Using the Excel Spreadsheet functions, you can refer to any function from the Excel Worksheet. The function names are displayed in English. You can refer to the Excel Spreadsheet function by entering statement:
The VBA functions, like in the datasheet, can be combined with each other using the following syntax: function_2 (function_1 (arguments of function_1)). So the result of function_1 is also an argument to function_2. They are so-called nested functions . You will learn all the functions you need in the next chapters of this course.
An example of using a VBA function
Firstly, let’s try to write a short macro using InputBox and MsgBox , which will extract the square of the value we enter. We use the VBA Sqr function in this example.
'OfficeInside.Org Sub FunExample1() Dim intValue As Integer intValue = InputBox("type value") MsgBox (Sqr(intValue)) End Sub
In the next step, let’s round our value to 2 decimal places using the Round() function. The brackets of both functions need to be closed:
'OfficeInside.Org Sub FunExample2() Dim intValue As Integer intValue = InputBox("type value") MsgBox (Round(Sqr(intValue))) End Sub
In the next example, we will use the character function. As in the previous example, we use the InputBox and MsgBox. Our goal is to change the text data entered into the UpperCase text. We will use the Ucase function for this example.
'OfficeInside.Org Sub FunExample3() Dim intValue As String strValue = InputBox("type text") MsgBox (UCase(strValue)) End Sub
In the next chapters of this course we will learn all the categories of functions in Excel VBA.
Tasks (You can leave the result in a comment)
- Process the function example listed above. Make the result displayed in cell B2 of Sheet3.
More examples of Excel VBA basics can be found in the Excel VBA Tutorial section and Examples in VBA section. If you have any questions about this chapter, you can post them in comments without logging in.
One thought on “Functions in VBA Excel”
Do you have any video of that? I’d care to
find out some additional information.