VBA Function in Excel

By Karthikeyan Subburaman

(Table of Contents)

We also have functions in VBA as well which are completely inbuilt in it. Almost all the excel functions can be used VBA as well with almost the same syntax. And if there is any change in the syntax, we will be able to see that also when we are about to use VBA function. To declare a function in VBA, first, open a Private-End procedure and select the data type as per functional need.

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Visual Basic

VBA stands for Visual Basic for an application. VBA is normally a programming language of excel which is embedded in an individual Microsoft application i.e. Excel or Access where we can do the automation of several tasks which is also called as “MACRO” in excel. The Visual Basic application comes with several command buttons and other function which makes it easier for the user for automation.

VBA User Defined Function (UDF)

A User-defined function is just a function where we can create our self in VBA for automation purpose.

This user-defined function is nothing but a code module attached to the worksheet where the user will actually type the coding.

How to Use VBA Function in Excel?

In excel we can find the VBA function under “DEVELOPER” tab by default excel does not have this menu. So in order to display it, we need to add this “DEVELOPER” menu manually by following the below steps.

• Go to File menu. Click on Option menu which has highlighted in Yellow Color.

• Once we click on the option we will get the below dialogue box as follows.

• Select Customize Ribbon option and we will get the below dialogue box as follows

• In customize ribbon option we can see add or remove the ribbon manually. Here we can see that on the “Right-hand side” of the customize ribbon “DEVELOPER” Tab is not checked. Checkmark that “DEVELOPER” Tab and click OK

• Once you check mark the “DEVELOPER” Tab it will get added in excel menu which is displayed as follows

Now we can see that the “Developer” Tab is added and it has the features like VISUAL BASIC, MACROS, etc.

Adding VBA in Excel

In Excel, VBA workbook can be added easily by following the below steps.

• Open Excel workbook. Use the shortcut key ALT+F11 to open Visual Basic Editor which is also called as VBE in excel shown in the below screenshot

• Right-click on your workbook name in the ” Project VBAProject” pane (at the top left corner of the editor window) and select Insert -> Module from the context menu as shown below

• Now we can see that new module has been added as MODULE 1 on the left-hand side of the panel.

• In the Right-hand side where we can use the UDF function using VBA.

The function of Visual Basic

In Visual Basic, we will use the various function. A function is nothing but a collection of visual basic statements enclosed by start and an end function. This function normally performs a task and then returns controls to the calling code. If it returns the control it will also return a value to the specific code.

VBA Function

Visual basic contains a large number of built-in function like Message Function, String and Text Function, Information Function, a Conversion function, Date and Time, Math and Trig Functions, Financial function

We will see the mostly used VBA function which is listed as follows.

• Input Box- Which displays an input dialogue box
• Msg Box- Which displays Message box i.e. alert message box
• Format- Which applies format for the specific string.
• Len- Which returns a length of the string
• Replace – Which will replace the specific string with another string.
• Is Array- Which will check for the supplied value is an array.
• Is Date- Which will check for the supplied expression is date format.
• Is Empty – Which will check for the specific variant is empty or not
• Is Null – Which will check for the supplied value is null or not
• Date – Returns the current date.
• Month – Returns the month number.
• Now – Returns the current date and time.
• Year – Returns the current year.

Examples of VBA Function in Excel

VBA Function is very simple to use. Let us now see how to use VBA function in Excel with the help of some examples.

Example #1

Addition of Two Numbers

In this example, we will see how to add two numbers using VBA function by using simple programming language step by step as follows.

• Open excel workbook. Use shortcut key ALT+F11 to open VBE editor. We get the VBE editor as follows

• Go to Insert Menu and select module

Once we click on the module a new module will be added in a worksheet Where we will write the VBA function coding on the right-hand side of General page.

Now to get the result of adding two numbers we have to use the below coding as follows

Function addtwo(arg1, arg2)
addtwo = arg1 + arg2
End Function

Here in the above coding, we have named the function as “addtwo” and the coding will be displayed as:

Now in the excel sheet, we can use the “addtwo” function to add the new number as:

Example #2

Date Function

In VBA, date function is a built-in function DATE() which return date and time we will now use the date function to get the date in the particular sheet. In VBA we can use UDF to get the result.

Insert a new Module as we have discussed above in Example 1.

Once we click on the module a new module will be added in the worksheet and on the right-hand side of General page, we will write the VBA function coding.

Sub todayDate()
Range(«A1»).Value = Date
End Sub

Here in the above coding, we have named the function as “todayDate” and the coding will be displayed as:

Hit the RUN button or Press F5 to execute the code

This coding will only return the current date in the mentioned cell name A1 and once we execute, the result will be shown as:

Example #3

VBA Message Box

Visual basic normally has two message box function

1. The Msg Box method.
2. The Msg Box Function.

The MsgBox Method

The MSGBOX method is one of the VBA languages where it displays a msgbox for the given string, it normally has the ok button, so that user can see the MSGBOX and continue with the specified task.

The MsgBox Function

The MSGBOX Function which returns value normally this MSGBOX function will be a question type like Yes or No or Abort or Continue.

We will see both the MSG box with the below examples as follows.

Msg Box method Example:

Insert a new Module as we have discussed above in Example 1.

Once we click on the module a new module will be added in the worksheet and on the right-hand side of General page, we will write the VBA function coding.

Sub Firstmsg()
MsgBox «Hello»
End Sub

In the below screenshot, we have written coding to display the MSG BOX as Hello.

Hit the RUN button or Press F5 to execute the code.

We will get the below output.

We can see the HELLO message which comes from the MSG box.

Msg Box Function Example:

In this example, we will see how to use MSG BOX function using vbYesNoCancel

Insert a new Module as we have discussed above in Example 1.

Once we click on the module, a new module will be added in the worksheet and on the right-hand side of General page, where we will write the VBA function coding.

Sub Firstmsg()
MsgBox «Welcome User or Guest?», vbYesNoCancel, «Login?»
End Sub

In the below screenshot, we have written coding to display the MSG BOX. Here in the MSG BOX function, we have given YES or NO option to check the end user is User or Guest.

Hit the RUN button or Press F5 to execute the code

After execution, we will get the below MSGBOX as follow

Things to Remember About VBA Function

• In VBA we have many built-in functions where it can be used in the automation process.
• While applying multiple MACROS function make sure that we have assigned a specific key to run it.
• Name each module in VBA so that to avoid confusion in coding.

Recommended Articles

This has been a guide to Excel VBA Function. Here we discuss how to use VBA Function in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –

Как округлять значения переменных в VBA?

Весьма нередко при разработке алгоритмов подсчета тех либо других значений приобретенные результаты имеют значение десятичной дроби с огромным количеством символов опосля запятой. Работать с таковыми данными и показывать их не весьма комфортно и безобразно.
Разглядим какими функциями следует округлять данные в коде vba Excel.

1-ый метод округления при помощью функции int().

Данная функция выводит целое значение до запятой. При округлении отрицательных чисел int округляет десятичную дробь до большего целого значения.
К примеру: число Пи с минусом опосля округления — int (-3.1415926535 ) будет равно 4 (четырем целым).

2-ой метод округление с помощью функции fix()

происходит аналогично с int . Различается эта функция тем, что округляет отрицательные числа до наименьшего значения. Так округлённое число Пи со знаком минус будет fix( -3.1415926535) = 3.
Если требуется не попросту убрать дробную часть, а округлить до целого числа, можно употреблять последующую функцию cint().
Пример:
Cint(fix( -3.1415926535)) = 3
Cint(int( -3.1415926535)) = 3

Как округлять до 10-х и сотых (один и два знака опосля запятой, соответственно).

Для округления до 10-х следует помножить переменную на 10 (10), опосля что округлить до целых и поделить на 10 (10).
Пример:
Cint(int( -3.1415926535*10))/10 = 3.1
Cint(fix( -3.1415926535*10))/10 = 3.1

Для округления до сотых следует сделать ту же самую операцию, лишь множить и разделять не на 10, а на 100.
Пример:
Cint(int( -3.1415926535*100))/100 = 3.14
Cint(fix( -3.1415926535*100))/100 = 3.14

Пример кода округляющего значения из textbox, запускающийся нажатием клавиши CommandButtom1:

Private sub CommandButtom1_click()
textbox2.value = Cint(int(textbox1.value *100))/100
END SUB

Видео с примером работы макроса:

Видите ли, ничего сложного в округлении чисел в vba Excel нет. Фортуны Для вас в исследовании программки

Схожее:

Макрос проверки наполнения ячеек. Временами при разработке.Использования функции преобразования текста в число в.Когда комфортно поменять формулы на значения нажатием.

Округление значений в коде VBA в Excel. Функции int () и fix().: 1 комментарий

Cint(int(-3.1415926535*100))/100 = 3.1
А это надёжно? Я заместо соткой поставил 1000 и в одном из случаев произошла ошибка переполнения. Снова поставил сотки.

Excel VBA Function vs VBA Sub – Excel Functions and Procedures

A VBA Function can accept parameters and return results. Functions, however, can’t be executed directly. On the other хэнд a VBA Sub procedure can be executed directly and can also accept parameters. Procedures, however, do not return values.

We often use Subs and Functions often not thinking about their true potential and how much we can squeeze out of a regular VBA Function. Let’s start with a reminder how both differ from each other and then dive into the details.

VBA Sub, VBA Function Syntax

Below structure resembles a VBA Sub and VBA Function:

Below are simple examples of Subs and Functions:

VBA Sub procedure syntax

The code block of a VBA Sub procedure is marked by the Sub and End Sub statements.

VBA Function procedure syntax

The code block of a VBA Function is marked by the Function and End Function statements.

VBA Function vs VBA Sub

We often tend to mix up procedures, Subs and Functions in VBA. So let’s get it right this time. There are 2 main differences between VBA Procedures (Subs) and VBA Functions:

• VBA Functions return values, VBA Subs don’t
• You can execute a VBA Sub, you can’t execute VBA Functions – they can only be executed by VBA Subs

Executing Functions and Subs

Although I provided examples above there are multiple ways to execute a VBA Function and a VBA Sub:

Passing arguments ByVal and ByRef

The common knowledge is that only VBA Functions can return values. That indeed is mostly true. However, instead of a value (the result of the Function), we can assign values to variables that can be passed to the procedure by reference. What does that mean?

Variables can either by passed to a procedure (Function, Sub etc.) by their value or their reference.
Passing by value translates to making a copy of the variable. Thus any changes to the copy will not be reflected in the original variable.
Passing by reference is passing the address of the variable to the procedure. This means that any changes to a argument will be reflected in the original variable.

ByVal and ByRef examples

Let’s look at some examples now:

When passing by value the variable someLong is not modified. However, when we pass it by reference its value is changed within the Sub procedure.

Passing arrays to Subs and Functions

You can also easily pass arrays to Subs or Functions, even redefining their length. See example below:

Optional parameters

VBA Functions and Subs permit optional parameters, ones that need not be provided when executing the Function or Sub. It is a good practice to specify the default value for such parameters. See example below:

You can verify if a parameter has not been passed to an Sub or Function by using the IsMissing function. The IsMissing function works however only for Variant type parameters. See the same SayHi procedure as above, this time with the IsMissing function.

Dynamic parameter list

Say you want to create a VBA function like the Excel SUM or AVERAGE formulas – that can be provided with a dynamic list of parameters. VBA extends a neat solution for such a scenario called the ParamArray.

The ParamArray transforms a list of Variant variables passed as parameters into a neat Variant array.