Is VBA a Scripting Language?
- What Defines a Scripting Language?
- What is VBA?
- Scripting Language Features: A Deep Dive
What Defines a Scripting Language?
A computer programming language is nothing but a set of instructions that the computer can understand and carry out a sequence of actions.
What is VBA?
Visual Basic for Applications is a subset of Visual Basic 6.0 and its features. It does not need a separate frontend/backend as it works in combination with the Microsoft Office applications. This enables VBA to have all the required features to automate anything on the system.
So, for sure, it can be called “a scripting language.”
Scripting Language Features: A Deep Dive
A scripting language has several features that help in proper coding to automate the execution of some actions. Some of those are highlighted below.
There are labels/descriptive sentences in the coding window explaining the code in a language humans can understand. The comment lines start with an apostrophe (‘). As soon as you use an apostrophe in a line of code, the right side of the line, along with the apostrophe, turns into comments (in a green color font in the VBA coding window) that will not be executed during runtime.
Declaration – Datatypes & Variables
As shown in the image above, VBA offers several data-types to store different variables like string, integer, long, double, Boolean, and many more. It also has a common datatype called variant to store any type of value.
Option Explicit Statement
“Declaration of variables” is not mandatory by default. VBA has this statement to make declaration mandatory. This should be the first statement in the coding window for it to work effectively. It can point out the usage of variables without declaration during runtime.
In the example below, the variable “i” is pointed to in the message as it has not been defined/declared but has been used directly in the code. This error has been pointed during runtime only because of the “Option explicit” statement on top of the coding editor page.
Conditional Statements and Control Flow
Conditional statements like if, else, else if, and so forth can help direct and control the flow of the program/decide and execute the sequence of actions based on the results/reactions during runtime.
For example, validation of a button action based on the data filled in a form/or validate a form data before submission can be made using conditional/control statements.
Nested ifs and if-else ladders can also help in a variety of conditional branching while building the logic for the program.
Loops like “do until,” “do while,” and “for and for each” can help in a repetitive run of blocks of code based on some condition. This is an important feature of all programming and scripting languages and is supported in VBA.
Here is a small piece of a program to print 25 numbers using a loop:
Sub print_numbers() For i = 1 To 25 If i = 25 Then Exit Sub End If Debug.Print i Next i End Sub
Goto Statement, Message Boxes & Input Boxes
A message box is a great feature that is mandatory in any programming/scripting language, as the end user needs to know the progress of execution. Our eyes turn bright when we see messages like “Task completed,” “The calculated value is…” or any other responsive message.
Inputbox is a feature that allows the program to receive input from the end user during runtime. The input value can be used in coding, calculations, and decision-making.
The Goto label is like a tag to a block of statements to which the control is redirected for execution during runtime.
Here is a piece of VBA code with an inputbox, message box, and some other features of a scripting language like the Goto statement, inbuilt functions, etc.
The program receives the age as input from the user. Then, it validates the input value at the first level and displays a message if it is found invalid, and the user is redirected to the same message box to receive a valid age as input again (Goto statement is used in this scenario). At the second level, using the inbuilt condition, it decides whether the user is a senior citizen/not. Then the relevant message is displayed to the user.
Sub msgbox_demo() ' declare variables Dim str_age1, int_age1 ' one goto label to iterate this in case the age provided by user is invalid get_age_of_user: ' get the age from the user str_age1 = InputBox("Please enter your age in numeric form") ' convert it to an integer int_age1 = CInt(str_age1) 'validate that the input is a number If IsNumeric(int_age1) Then ' validate and display if the user is a senior citizen If int_age1 >= 60 Then MsgBox "Good. You are a senior citizen. You can benefit from our schemes. " Else MsgBox "Sorry! You are not a senior citizen. Currently we have no schemes for you. " End If Else MsgBox "Invalid value. Please try again. " GoTo get_age_of_user End If End Sub
Arrays and Collections
Arrays are collections of data items stored in continuous memory locations. They have index values using which the values can be assigned/fetched. In VBA, their size may/may not be defined while declaring.
Here is a sample program that assigns values to an array from data in an excel sheet and then prints back all the array values except the one in index “4” i.e., the fifth item in an array.
Arithmetic operators like +, –, * (multiplication), / (Division), % (modulus), > (greater than), = (equal to), < (Less than) are used in calculations. These calculations can be done using code as in any other programming/scripting language.
Here is a sample program for the calculation of simple interest.
Sub simple_interest_calculation() ' declare all required variables Dim Prin, no_of_years, cut_age, roi, simple_interest, mat_amt ' Receive necessary inputs from the end user Prin = InputBox("Enter the Principle amount") no_of_years = InputBox("Enter the number of years") cut_age = InputBox("Enter the cut_age of the customer") ' Set rate of interest depending on the cut_age of the customer ( varies for senior citizens ) If cut_age > 59 Then ' senior citizens roi = 10 Else ' non- senior citizens roi = 8 End If ' Calculate the simple interest and maturity amount simple_interest = (Prin * no_of_years * roi) / 100 mat_amt = simple_interest + Prin ' Display the calculated output MsgBox "The interest amount is " & simple_interest & vbCrLf & "The maturity amount is " & mat_amt End Sub
Procedures and Functions, Parameters
Functions and procedures are an integral part of any scripting language as they can be invoked any number of times and in any number of modules based on the need. Passing parameters to functions and receiving return values add additional value to this feature and put them to better use.
Here is a simple example of a function called from a sub-procedure. The function to print numbers from 1 to “X” number (passed as a parameter here), is called thrice from the sub-procedure with three different parameters each time.
Sub print_numbers() ' Print numbers from 1 to 10 Call fn_print_numbers(10) ' Print numbers from 1 to 50 Call fn_print_numbers(50) ' Print numbers from 1 to 75 Call fn_print_numbers(75) End Sub Function fn_print_numbers(till_int) ' loop to print numbers For i = 1 To till_int Debug.Print i Next i
Calling and Called Functions—Invoking Functions
In the above example, the function that is called is called as the called function. The function inside which a function call happens is a calling function. Invoking a function/calling a function can be done any number of times from any modules in VBA if the scope of the function or procedure is also defined along with it.
A scripting language supports many more features in addition to what has been discussed here. In short, Visual Basic for Applications is a visually pleasing scripting language as one can use it as a complete package with frontend and backend tools. The code editor and form editor are very developer friendly, and the syntax is also self-explanatory in VBA. It is easy to learn the language and automate our small windows tasks. You may also look into our other articles that elaborate on each feature and provide guidance to the developers’ errors in VBA.