Functions in VBA Excel

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:

FUNCTION_NAME(arguments)

The VBA functions can be group into the following categories:

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:

Application.WorksheetFunction. FUNCTION_NAME

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.