VBA Programming

Home/VBA/VBA Programming

VBA programming for beginners and advanced users will help you to learn vba programming language and concepts. Our objective of this topic is to provide complete programming reference to write VBA Programs.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

BROWSE ALL TEMPLATES

50+ Project Management Templates Pack
Excel PowerPoint Word

VIEW DETAILS

Advanced Project Plan & Portfolio Template
Excel Template

VIEW DETAILS

Business Presentations Templates Pack
PowerPoint Slides

VIEW DETAILS

20+ Excel Project Management Pack
Excel Templates

VIEW DETAILS

20+ PowerPoint Project Management Pack
PowerPoint Templates

VIEW DETAILS

10+ MS Word Project Management Pack
Word Templates

VIEW DETAILS


VBA Programming Learning Objective

vba programming

By learning VBA Programming, you will be able to write VBA programs to automate any task in Micro soft office Tools (MS Excel, MS Access, MS Word, MS PowerPoint)
1. VBA Introduction
2. VBA Applications
3. Visual Basic Programming Concepts
o Keywords
o Constants
o Variables
o Data Types
o Operators
4. Advanced VBA Programming Concepts
o Conditional Statements
o Sub Procedures
o Functions
o User Defined Functions (UDFs)
5. VBA Programming Environment (VBE)
o VBA Windows
o Modules
6. Forms and Controls
7. Miscellaneous

1. VBA Introduction: In this session we will see what is VBA and its history and Evaluation of the VBA in Software Industry.
o What is VBA?

2. VBA Applications: In this topic we are going to see the different kinds of VBA applications and its use in real world. We will see, how we are going to use VBA Programming in different applications in MS Office, such as MS Excel, MS Access, MS Word, MS PowerPoint, MS Outlook and other applications.

o VBA Programming in Excel: Introduction
o VBA Programming in Access: Introduction
o VBA Programming in Word: Introduction
o VBA Programming in PowerPoint: Introduction
o VBA Programming in Outlook: Introduction

We can also use VBA in other applications like AutoCAD, SAP, SAS, SPSS, etc. We can also develop VBA Applications using VB.Net, C#.Net, C++, VC++ and Java.
3. Visual Basic Programming Concepts: This topic will provide you basic VBA programming concepts.
o Keywords
o Constants
o Variables
o Data Types
o Operators
o Conditional Statements
Variables: Variables in VBA Programming helps you to store any value in a temporary memory for further calculations. We can declare different kinds of variables by specifying its data type and use it in our programs.
o Dim Statement in VBA
o Scope of the Variables
o Local Variables
o Module Level Variables
o Global Variables
Data Types: Data types in VBA Programming helps to declare a variable to store the specific type of data. For example String is to store text, Integer is to store numeric data and Date is to store date and time values.
o Integer Data Type
o Long Data Type
o String Data Type
o Date Data Type
o Arrays
o Arrays and Loops
o Multidimensional Arrays
o Arrays and the Split function
Operators: Operators in VBA programming helps to deal with expressions to perform different kinds of calculations.
Arithmetic Operators: Arithmetic Operators in VBA programming helps to deal with expressions to compare the data in expression and calculations.

Logical Operators: Logical Operators in VBA programming helps to deal with expressions to form expressions with more than one condition or expression.
o And Statement
o Or Statement
4. Advanced VBA Programming Concepts: This topic will provide you the advanced topics for VBA programming.

Conditional Statements: Conditional statements in VBA Programming are to check the conditions in Expressions.
o If Statement -Syntax Examples Macros
o If Else -Syntax examples Macros
o If Else If – Syntax examples Macros
o For Loop – Syntax examples Macros
o Do Loop – Syntax examples Macros
o While Loop – Syntax examples Macros
o Case Statement – Syntax examples Macros
o Select Case – Syntax examples Macros
o Switch Case – Syntax examples Macros

Sub Procedures: Sub procedure are the main VBA Programming coding blocks to write the VBA Programs.
o Calling Sub procedures in VBA
o VBA Sub procedures with Parameters
Functions: Functions in VBA Programming will take some input values and return output values, we can use different kinds of built-in functions in VBA to fasten our programming.
o VBA Sting Functions:
o VBA Numeric Functions:
o VBA Date Functions:
o Etc…
o VBA UDFs -User Defined Functions
VBA Programming Environment (VBE): We will see the VBA Environment in detailed, and different kinds of Windows and its uses in VBA Programming.
o VBA Windows
o VBS Objects – Object Explorer
o Project Explorer
o Immediate Window
o Watch Window
o Properties Window
o Code Editor
o Debugging VBA Code

o Modules
o Module
o Class Module
o Forms
Forms and Controls: ActiveX Controls in User Forms will help use to different kinds of placeholders to build a Form or windows applications.
o Command Button
o ComboBox
o CheckBox
o ListBox
o Label
o TextBox
o Option Button (Radio Button)
o SpinButton
o Scroll Bar
o Toggle Button
o Image
Miscellaneous: Here are the few more useful VBA programs which can help you in your automation.
o Concatenate
o Comments
o Block Comments
o Date Format
o Hello World VBA Program
o VBA Timer

 

LIMITED TIME OFFER - Get it Now!
Advanced Project Plan Excel Template
LIMITED TIME OFFER - Get it Now!
Business PowerPoint Presentations Templates Pack
 
 
Related Resource External VBA Reference
By |May 1st, 2014|VBA|14 Comments

About the Author:

PNRao is a passionate business analyst and having close to 10 years of experience in Data Mining, Data Analysis and Application Development. This blog is his passion to learn new skills and share his knowledge to make you expertise in Data Analysis (Excel, VBA, SQL, SAS, Statistical Methods, Market Research Methodologies and Data Analysis Techniques).

14 Comments

  1. HA October 14, 2014 at 11:07 PM - Reply

    Hi,
    I am trying to run a VBA macro which would delete the first 6 rows from each tab of an excel (2013) and save the file back with the same name. I am new to VBA and would be grate if you can help me out with a sample code.

    Thanks
    -H

    • PNRao October 18, 2014 at 1:52 PM - Reply

      Hi HA,
      You can use the below macro:

      Sub Test()
      Call sbDeleteRows(6) ‘6 will delete first 6 rows
      End Sub

      Sub sbDeleteRows(ByVal intNumberOfRows As Integer)
      For Each sht In ThisWorkbook.Sheets
      For iCntr = 1 To intNumberOfRows
      sht.Rows(1).Delete
      Next
      Next ‘sheet
      ThisWorkbook.Save
      End Sub

      Thanks-PNRao!

      • Ali January 16, 2015 at 2:01 AM - Reply

        I have learned a lot from your website about VBA Macro. I am actually doing a project for my final class project and I need to create VBA Macro. I need help on how we can compare two rows and display onto a new worksheet.
        I was able to create a macro that displays the fields but I dont know how to display the description because the size of the columns are different and I need to display only the specific cells.

        I am not sure how to do that. I believe using 2D array would help me solve this issue but unfortunately I am lost.

        If you can please guide me i would greatly appreciate.

        • PNRao January 16, 2015 at 2:55 PM - Reply

          Hi Ali,
          We are happy that we could help!
          You can loop through the columns using a for condition and compare using an if condition. Arrays are not required to do this.

          I am assuming you have different kind of data or requirement. It is good to post a sample data and explain your problem more elaborately. This helps me to understand your exact need to provide better solution.

          Thanks-PNRao!

  2. CAH October 29, 2014 at 12:14 PM - Reply

    Hi,
    I need to read a list of e.g. Position IDs in a range C2:aa2, in Column A6:A160 is a range of codes with some of these codes relevant to some positions but not all. In the columns below each PositionId the corresponding codes which is relevant to the position is indicated by an “M” or “NM” (only a small number of codes will be relevant to each position, but will be different for each position) I then need to print the resulting data in a table with 3 columns with Column 1 the Code, column 2 the position id and column 3 the “M” or “MN” I have various sheets that I need to compile into one table. Can anyone perhaps assist with an easy solution? Any help or advice will be appreciated.

    • PNRao October 29, 2014 at 10:44 PM - Reply

      Hi CAH,

      Could you please provide example workbook with some dummy data in one worksheet and required output format in another sheet.
      You can send your file to info@analysistabs.com

      Thanks-PNRao!

      • PNRao October 30, 2014 at 8:01 PM - Reply

        Great! Thanks for the sample file, here the code:

        Sub Atprepare()

        lastRow = Sheets(“Data”).Range(“A100000”).End(xlUp).Row
        col = 3
        jCntr = 4 ‘Print the out from this row
        ‘Clear Output sheet
        tmp = Sheets(“Output”).UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
        ‘clear output and paste the headers
        Sheets(“Output”).Cells.Clear
        Sheets(“Home”).Range(“B4:L6”).Copy Destination:=Sheets(“Output”).Range(“A1”)

        Do While Trim(Sheets(“Data”).Cells(3, col)) <> “”

        For iCntr = 6 To lastRow ‘Here 6 is starting row number of Learning requirement data
        ‘check if value is M or NM
        If Sheets(“Data”).Cells(iCntr, col) = “M” Or Sheets(“Data”).Cells(iCntr, col) = “NM” Then
        ‘Print the fixed values from Home Sheet’Blue Cells
        Sheets(“Output”).Cells(jCntr, 1) = Sheets(“Home”).Cells(7, 2)
        Sheets(“Output”).Cells(jCntr, 2) = Sheets(“Home”).Cells(7, 3)

        Sheets(“Output”).Cells(jCntr, 4) = Sheets(“Home”).Cells(7, 5)
        Sheets(“Output”).Cells(jCntr, 5) = Sheets(“Home”).Cells(7, 6)

        Sheets(“Output”).Cells(jCntr, 7) = Sheets(“Home”).Cells(7, 8)
        Sheets(“Output”).Cells(jCntr, 8) = Sheets(“Home”).Cells(7, 9)

        Sheets(“Output”).Cells(jCntr, 10) = Sheets(“Home”).Cells(7, 11)

        ‘Now print the remaing values from Data sheet
        Sheets(“Output”).Cells(jCntr, 3) = Sheets(“Data”).Cells(iCntr, 1) ‘Learning requirement Number
        Sheets(“Output”).Cells(jCntr, 6) = Format(Now(), “dd.mm.yyyy”) ‘Current data
        Sheets(“Output”).Cells(jCntr, 9) = Sheets(“Data”).Cells(3, col) ‘Trainee Position code
        Sheets(“Output”).Cells(jCntr, 11) = Sheets(“Data”).Cells(iCntr, col) ‘M/NM

        jCntr = jCntr + 1
        End If
        Next

        col = col + 1 ‘increase the column to check the next employee

        Loop

        MsgBox “Done!”
        End Sub

        Thanks-PNRao!

  3. Ali November 1, 2014 at 7:22 AM - Reply

    I am having an issue on Excel. I have a drop down menu in cell D5, with three options, A, B, and C.

    If A is to be selected, cell content in D6 should become zero/clear-out [i.e., available for manual input by user], and cells D7, D8 and D9 should be locked while having formulas assigned to them as follows: for D7, =D6*G9, for D8 = D6*G10, for D9 = D6*G11

    Similarly, if B is to be selected, cell content in D7 should become zero or clear-out, and cells D6, D8 and D9 should be locked while having the following formulas assigned to them for D6 = D7*G8, for D8 = D6*G10, for D9 = D6*G11

    And lastly, if C is to be selected, cell content in D8 and D9 to be zeroed out and available for user input, while cells D6 and D7 are locked out and have following formula assigned to them: D6=(D8+D9)*I9, D7= D6*G9

    I am completely novice on VBA in Excel and clueless as to how to solve this issue.

    I got a code by googling which helps me to lock out cells, but I have no idea how to blank out cells and assign formulas.

    The issue is, if I just type in the formula, lets say in D6, and user selects option A in dropdown, he will have option to overwrite value in D6 and later on if option B or C is chosen, D6 will remain static instead of moving in line with the values input in D7-D9 when options B/C are chosen.

    Can someone please help

    • PNRao November 1, 2014 at 11:42 AM - Reply

      Hi Ali,
      Below is the code you can use Sheet Change event, you can download the example file here .
      ‘Lock/ unlock a Range of Cells based on Change at a Range and Populate the formulas in Cells
      Private Sub Worksheet_Change(ByVal Target As Range)
      Application.EnableEvents = False
      ‘do if any changes at D5
      If Target.Address = Range(“D5”).Address Then

      ActiveSheet.Unprotect
      Range(“D6:D9”).Locked = False
      If Range(“D5”) = “A” Then
      ‘If A is selected D6 should be cleared And D7, D8 and D9 locked : Formulas D7, =D6*G9, for D8 = D6*G10, for D9 = D6*G11
      Range(“D6”) = “”
      Range(“D6”).Locked = False

      Range(“D7”).Formula = “=D6*G9”
      Range(“D7”).Locked = True

      Range(“D8”).Formula = “=D6*G10”
      Range(“D8”).Locked = True

      Range(“D9”).Formula = “=D6*G11”
      Range(“D9”).Locked = True

      ElseIf Range(“D5”) = “B” Then
      ‘D7 should become zero or clear-ou
      ‘Lock and Formulas D6 = D7*G8, for D8 = D6*G10, for D9 = D6*G11
      Range(“D7”) = “”
      Range(“D7”).Locked = False

      Range(“D6”).Formula = “=D7*G8”
      Range(“D6”).Locked = True

      Range(“D8”).Formula = “=D6*G10”
      Range(“D8”).Locked = True

      Range(“D9”).Formula = “=D6*G11”
      Range(“D9”).Locked = True

      ElseIf Range(“D5”) = “C” Then
      ‘Cell content in D8 and D9 to be zeroed out and available for user input
      ‘D6 and D7 are locked| D6=(D8+D9)*I9, D7= D6*G9
      Range(“D8”) = “”
      Range(“D9”) = “”
      Range(“D8:D9”).Locked = False

      Range(“D6”).Formula = “=(D8+D9)*I9”
      Range(“D6”).Locked = True

      Range(“D7”).Formula = “=D6*G9”
      Range(“D7”).Locked = True

      End If
      ActiveSheet.Protect
      End If

      Application.EnableEvents = True

      Hope this helps!
      Thanks-PNRao!

  4. Vaasu December 3, 2014 at 5:56 PM - Reply

    Hi,

    Please help me how to go about this I have excel tab which populate 30 months of data in column wise, I need a help to hide the column programmatically when there is no data for particular month.

    This will help me in freezing the months which does not have any data. Please let me know how to go about this.

    I appreciate your help

    Thanks,
    Murugan

    • PNRao December 4, 2014 at 7:00 PM - Reply

      Hi Vaasu,
      Assuming you have populated your data from colum 1 to 30. To hide the column with no data or based on any condition, you can use the below code:

      Sub hideColumnsBasedOnCondition()
      Dim iCntr As Integer
      ‘Note: Change the sheet name “YourSheetName” and use this code
      For iCntr = 1 To 30

      ‘check if column is blank
      If Application.WorksheetFunction.CountA(Sheets(“YourSheetName”).Columns(iCntr)) = 0 Then ‘if you have column headings then it should be 1, instead of 0
      Sheets(“YourSheetName”).Columns(iCntr).Hidden = True
      Else
      Sheets(“YourSheetName”).Columns(iCntr).Hidden = False
      End If

      Next

      End Sub

      Thanks-PNRao!

      • vasu December 10, 2014 at 4:17 PM - Reply

        Hi PNRao,

        Thank you very much for the response with an answer for my question.

        My Data is as similar as below table, By default the column will be with zero/-.
        When I run query the yearly fields with get update with the values since I have around 25 to 30 Months, if any of the month does not have value those column should get freezed.

        Year1 Year2 Year3 Year4
        $2,280 $411 $445 $479
        $2,292 $412 $446 $480
        $2,304 $413 $447 $481
        $2,316 $414 $448 $482
        $2,328 $415 $449 $483

        Thanks for looking into this.

        Regards,
        Murugan

        • vasu December 10, 2014 at 7:35 PM - Reply

          Hi PNRao,

          I would able to do this. Thank you very much for your help on this.

          Regards,
          Murugan

  5. vasu December 15, 2014 at 10:56 PM - Reply

    Hi Rao,

    I am looking for your online Excel VBA program course, But I see the dates are keep postponing.

    Please let me know what is the exact date for this course.

    Regards,
    Murugan

Leave A Comment