What are VBA Variables?
A variable is nothing but a name given to a storage area that our programs can manipulate. Each variable in C has a specific type, which determines the size and layout of the variable’s memory; the range of the values that can be stored within that memory; and the set of operations that can be applied to the variable.
The name of a variable can be composed of letters, digits, and the underscore character. It must begin with either a letter or an underscore. Upper and lowercase letters are distinct because C is case-sensitive.
List of Constraints to Name the VBA Variable:
1. The name of your variable cannot be more than 255 characters.
2. Letters must be used as the first character or not begin with a number.
For example, you can’t write a variable name that starts from a number”1_helloworld”, the appropriate way to write is, “helloworld1”. It’s not clear that a variable name cannot start with a number.
3. You cannot start with special characters like @, &, #, (.), (!) or cannot have spaces because all these are not allowed.
4. Keyboards are not considered variable names.
Syntax for VBA Variable
It is important you declare the variables before using them by assigning names and data types in VBA.
Sub Exercise () Dim variable_name As variable_type End Sub
There are two main variable declarations in VBA: implicitly or explicitly.
Implicitly: let’s check the below example to know more about variables declared implicitly.
Public Sub Example_Of_Variable_Declaration() IntVariable = 10 StrVariable = "Hello World" 'Both of these variables are of the Variant type. End Sub
Explicitly: let’s check the below example to know more about variables declared explicitly.
Option Explicit Public Sub ExampleDeclaration() Dim LngVariable As Long IntVariable = 10 Dim StrVariable As String StrVariable = "Hello World" End Sub
if Option Explicit is specified, the code will interrupt because it is missing the required Dim statements for LngVariable and StrVariable.
How to Execute the Variables?
Before taking any step of executing the variables, we need to record a macro in Excel. To record a macro, the following steps will help you:
Step 1: Select the Record Macro from the ribbon.
Step 2: After clicking, the Record Macro will appear. You can enter the macro name such as Macro1.
Step 3: Click the OK button
Step 4: At the top of your screen, direct your cursor to Stop Recording and click on it.
Step 5: Open the macro editor and insert the code for the variable in Macro1.
Step 6: Execute the code for Macro1, and you will get the results below in your sheet.
Before declaring variables, We will create a simple VBA program that displays an input box to ask for the user’s name and then shows a greeting message.
Step 1: Navigate to the Developer tab and click on the Insert drop-down box.
Step 2: Select a Command Button, as shown in the below screenshot.
Step 3: A new dialogue window will appear on the screen.
1. Enter the Macro name and click on the New option on the right side of the window.
2. You will get the code window and enter the following code.
Step 4: Close the code window. And, then
1. Right-click on the button named Button 1 which appears on your sheet and then select the Edit Text option.
2. Clik on “Button 1”.
3. After click on it. Then you will get the input box as shown in the below screenshot.
4. Enter the name, i.e. Umesh Manral.
5. You will get the message box as shown in the below screenshot.
How to Declare Variables?
A Dim statement is generally used to declare variables, which can be placed inside a procedure to create a procedure-level variable. It can be placed at the top of a module, in the Declarations section, to create a module-level variable.
Let’s look at a simple VBA way of declaring variables. We will explain the four types of variables, such as string, Integer, Long, and Date.
Step 1: Before, we inserted a Command Button in the excel sheet.
Step 2: Go to the Macros and select a created Macro, click on the “Step Into“ button and It opens the code window as shown in the below screenshot.
Step 3: Write down your code to declare the variables.
Step 4: After the process, click on the Save button and save your file or press (Ctrl S).
1. Click on the Excel icon to return the Excel sheet and you will get the Design Mode “ON” as shown in the below screenshot.
2. Turn off the Design Mode before clicking on the command button and then click on the Command button. It will show the variables as an output for the range we declared in the code.
