Insert a module and basics of programming

  VBA| Insert a module & VBA programming basics | Part-3

In this blog we will discuss how to "insert a module in VBA" and also we will see the basics of programming in VBA using sub procedures and functions.

First of all we will discuss the steps to insert a module in VBA which are as follows:-

1. Open the Excel Application.
2. Once Excel Application is opened then select blank workbook.
3. Now the Excel file is opened then press the Alt+F11 keys simultaneously.
4. A new window will open. In that window click on Insert and then module.
5. A new Editor window will open where we can write our code.

Now we will discuss about the Sub procedures and functions in VBA.

Sub Procedures - A sub procedure is the block of statements which is enclosed by a particular declaration statement and an End declaration, The main purpose of sub procedures is to carry out a particular task or action. To define a sub procedure use the sub keyword followed by the name.

Syntax to define a sub procedure is as follows:-

sub subname()

Statements to be executed

End Sub

Functions - A function is a VBA code that performs calculations and returns a value. To define a function in VBA we need to use the function keyword followed by the name of the function and the parameters or arguments of the function if any.

Syntax to define a function is as follows:-

function functionname(parameters)

statements to be executed

End function

The main difference between a sub procedure and a function is that sub procedure does not return a value whereas we can return a value through a function.

We can call a function inside a procedure but a procedure cannot be called inside a function.

So the basic syntax of programming in VBA will look like this.

sub SubName()

Dim variable1 As Datatype, variable2,variable3 As Datatype

Statements to be executed

End Sub

So in the above syntax Dim stands for dimension.

Now we will look at the basics of programming in VBA. So as we can see that variable1 and variable3 has their respective datatype defined but we have not defined the datatype for variable2 so what will be it's datatype in such scenario. So variable2 will take variant as the datatype by default.

Suppose for example if we want to add two numbers by using a sub procedure so the code that we will create will look like this as follows:-

Sub AddTwoNumbers()

Dim firstNum As Integer, SecondNum As Integer, result As Integer

firstNum=1

SecondNum=2

result=firstNum+SecondNum

msgbox result

Now we will create a function for adding two numbers and will call that function inside a sub procedure.

function AddTwoNumbers(ByVal vFirstNum As Integer,ByVal vSecondNum As Integer)

Dim vResult As Integer

vResult=vFirstNum+vSecondNum

End Function

Sub Test()

Dim FirstNum As Integer, SecondNum As Integer, result as Integer

FirstNum=1

SecondNum=2

result=AddTwoNumbers(FirstNum,SecondNum)

msgbox result

End Sub

In case of your queries then do please feel free to reach out to me with your queries and I will respond to your queries.

Read More

VBA Architecture

 VBA| Developer Tab & VBA Architecture | Part-2

In this blog we will discuss about what is "Developer Tab" and the "Architecture of VBA". So let's start our discussion with what is Developer Tab, how to add the developer tab in excel.

Developer Tab - The Developer tab is a built in tab in Excel, provides the features needed to use in Visual Basic for Applications and perform a macro operation. The developer tab is disabled by default. It must be enabled first in the Options section under File menu to make it visible on the toolbar at the top of Excel Window.

Now let's take a look at the steps to add Developer tab in Excel which are as follows:-

1. Click on File Menu and then select the Options menu. Click Options from the drop down menu to open the Excel options window as follows.

2. Click on Customize ribbon option in the Excel options window. Then on the right side under the list of Main Tabs, click on the "Developer" checkbox and then click on OK.

3. The Developer tab will appear on the default tabs at the top of the Excel window. The tab contains a list of all features that we will need for advanced functions.

Next now we will discuss about the Architecture of VBA.

1. Excel Application - When we install the Excel while downloading the office then the installed Excel is also termed as Excel Application.
2. Workbook - The files which are created and saved which we need to work upon is known as Workbook.
3. WorkSheet - The Sheets which are contained under Excel or Workbook is also referred to as WorkSheet. There can be one worksheet or multiple worksheets under one workbook.
4. Range - Range represents a cell, a row, a column, a selection of cells containing one or more contiguous blocks of cells or a 3D range. For example, if we want to access range from A2 to C5 then the syntax will be Range("A2:C5").
5. Cells - Cells is a property of a range or worksheet or Application objects. Cells returns a Range object. Cells can be used without any parameter passed or a single parameter passed in it. For example, if we want to refer first row and first column then the syntax would be Cells(1,1).
6. Columns & Rows - The Excel contains a combination of rows and columns and in VBA we can calculate the last row and column in which the data is present which we will discuss in our next discussion.

If you have any queries related to this article you can post your queries in the comment section and i will respond to your queries. Comments and suggestions are always appreciated.

Read More

Introduction to VBA

 VBA| Introduction to VBA | Part-1

VBA | VBA also known as "Visual Basic for Applications" is another programming language which is developed and owned by Microsoft. With VBA we can automate various applications such as Outlook, Word, Excel, Web Scrapping and we can use excel as database in VBA and many more applications.

Now we will take a look at some of the key points of VBA which are as follows:-

1. VBA is also known as Visual Basic for Applications.
2. VBA is a programming language which is developed and owned by Microsoft.
3. With VBA we can create macros to automate repetitive tasks such as word and data processing functions and also we can generate custom reports, graphs and forms.
4. VBA functions with MS Office application since it is not a standalone product.

Let us now see the advantages and disadvantages of using VBA which are as follows:-

Advantages:-

1. It always executes the task in exactly the same way as it is expected to perform.
2. It performs the task much faster as compared to the employees or the resources performing the same task manually.

Disadvantages:-

1. We need to know about how to write the program/code in VBA.
2. Other people who want to use VBA must have their own set of copies of excel on their system or desktop or machine.

In the next blog we will discuss about the Developer Tab and the VBA Architecture.

In case if you have any queries then do please feel free to reach out to me with your queries in the comment section and I will be more than happy to respond to your queries. 

Comments and suggestions for improvement are always appreciated.

Read More