Is VBA a Scripting Language?

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.

A scripting language is a programming language that can automate a sequence of actions in a runtime environment. So, a language that has the features to assist in this kind of automation is a scripting language. Also, these languages do not need compilation. Someone can run them directly. Major examples are JavaScript, PERL, Python, ASP, JSP, and VBScript.

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.

Comments

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.

Image of VBA offering several datatypes to store different types of variables

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.

Image showing a VBA error when a variable has not been defined or declared

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.

Image displaying a conditional flow of statements based on the result/values then and there

Nested ifs and if-else ladders can also help in a variety of conditional branching while building the logic for the program.

Loops

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.

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

Operators

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 &gt; 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 " &amp; simple_interest &amp; vbCrLf &amp; "The maturity amount is " &amp; 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.

Conclusion

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.

Leave a Reply

Your email address will not be published. Required fields are marked *