Excel VBA programming | VBA for Beginners

Microsoft Excel has been used for a great deal of assistance to the applications for storing the data, creating graphs, sorting and filtering the data conditionally etc. for number of years. It’s really a handy tool for many small scale business players as well. Excel provides in-built functions and formulas for doing such kind of stuff. However, when we want to develop an application which requires more features, for more advanced users, the in-built features are sometimes not enough. So, one will have to explore Microsoft Excel VBA programming capabilities which enables the users these more advanced applications.

Read.

Microsoft excel formulas list with examples.

Repair corrupted excel file in Microsoft excel.

This article will walk you through excel VBA programming introduction and will touch upon the basics of what you can do with it. We will also walk you through a small example to help you understand it more in detail and you can build some confidence to do some stuff yourself.

What is Excel VBA? Excel VBA programming

VBA (Visual Basic for Applications) is a programming language for enabling various tasks in Excel. You can create Macros which can be triggered by a button on a spreadsheet etc.Learning Excel VBA programming enables you to do many more things than you would do the normal spreadsheet view. You can do a lot of automation in excel using excel VBA programming.

Visual Basic Programming in Excel

VBA is vastly based on Visual Basic 6 and anyone who is already aware of it or has worked on it before will find the excel VBA programming much easier to deal with. However, even if you are not so familiar with visual basic, learn excel VBA programming is not a hardest thing in the world. Visual Basic was one of most used programming methodology and very popular with users and developers before Microsoft’s .Net technology came into existence.

Excel VBA Programming Examples step by step

1. If you don’t already have a tab called Developer in your excel Ribbon like shown below, you will have to enable it.For this, go to File, Options and select “Customize Ribbon”. Check the Developer box to allow the Developer icon to appear.

   

    

2. Next thing you will have to check is whether the security options allow the macros to be enabled or not.For this, Go to File -> Options –> Trust Center -> Trust Center Settings.In Macro Settings, check if “Enable All Macros” option is already selected or not. If not, you will have to enable the same. Without this setting, all the macros recorded or written in the excel file will have no effect and you can’t run them.

   

    

3. Writing macro: Inside Developer tab, you can see various options.For creating a new macro, select on “Macro” option. This will give you options such as Create a new Macro, Edit existing macro, Delete existing macro etc.For a sample application, we will create a new macro which will color the cells based on the value mentioned in the cell.For example, we will color the cell with a different color for ranges of values such as 0 to 24, 25 to 49, 50 to 74 and 75 and above.

   

    

4. Add a button from Insert option like below. Place the button somewhere on the visible part of the spreadsheet.

   

5. We will add some dummy data for this application. Add some figures like shown below. You can change the figures to whatever you want. Fill the 13 rows of first column of your spreadsheet. Like below.

   

   When you add the button, you will be prompted for select a macro or create a new macro. You can create a new macro and you will see a “Microsoft Visual Basic For Applications” workspace opened. Copy the code from below and paste it in the sub tags like shown below. Save the contents.

    Dim iCntr
   
      For iCntr = 1 To 15
   
        If Cells(iCntr, 1).Value < 25 Then
   
          Cells(iCntr, 1).Interior.ColorIndex = 33
   
        ElseIf Cells(iCntr, 1).Value < 50 Then
   
          Cells(iCntr, 1).Interior.ColorIndex = 12
   
        ElseIf Cells(iCntr, 1).Value < 75 Then
   
          Cells(iCntr, 1).Interior.ColorIndex = 45
   
        ElseIf Cells(iCntr, 1).Value > 74 Then
   
          Cells(iCntr, 1).Interior.ColorIndex = 23
   
        ElseIf IsEmpty(Cells(iCntr, 1).Value) = True Then
   
          Cells(iCntr, 1).Interior.ColorIndex = 66
   
        End If
   
      Next iCntr
   
   

    

   

    

6. Run: Now you can go back to the spreadsheet screen by closing the VBA screen. To run the macro, click on the button you added in one of the previous steps. If you have done all the steps correctly and as per mentioned above, you should see the screen like below. You should see all the cells with the value in those particular ranges have been colored with the color mentioned the code.

   

    

7. Code: Though the main intention of this Excel VBA Programming article is to explain you the features of this programming methodology, I will still explain the code so that you can build similar codes as per your requirements for simpler applications. See below.

   

Check PCB : 5 Best free PCB design software,What does pcb stand for with infographics.

So with this, we are ready with a simple application in Microsoft Excel VBA programming which is not very complex, but does some automation. It automatically changes the color of any cell based on the content of the cell. Similarly, we can build more advanced and complex applications which can help us in various fields such as scientific projects, business accounting, mathematics etc.

 If you are interested in programing check this .

• Microsoft SQL Server Reporting Services.
• How to perform SQL Server performance monitoring?
• How to create stored procedure in SQL server 2008?
• What is Relational database system ?
• SQL queries interview questions and answers.
• Structured Query Language  query join tables.
• SQL Server group by  clause.
• What is triggers in sql server?
• Vlookup Formula and Examples

Microsoft Excel is very powerful tool in itself. This article Microsoft Excel VBA Programming was a brief attempt to familiarize you with automation powers of the tool. There are many aspects of the excel tool. However, it will be difficult to cover all aspects in this Excel VBA Programming article.