REAL-TIME

VBA Projects

Full Access with Source Code
  • Designed and Developed by PNRao

  • Full Access with VBA Source Code

  • Well Commented Codes Lines

  • Creative and Professional Design

Effortlessly
Manage Your Projects

120+ Project Management Templates

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

120+ PM Templates Includes:
  • 50+ Excel Templates

  • 50+ PowerPoint Templates

  • 25+ Word Templates

Share Post

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.

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

 

Effortlessly Manage Your Projects and Resources
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Save Up to 85% LIMITED TIME OFFER
Project Management Templates

All-in-One Pack
120+ Project Management Templates

Essential Pack
50+ PM Templates

Excel Pack
50+ Excel PM Templates

PowerPoint Pack
50+ Excel PM Templates

MS Word Pack
25+ Word PM Templates

Ultimate Project
Management Template
Ultimate Resource
Management Template
Project Portfolio
Management Templates
Categories: VBATags: Last Updated: June 17, 2022

14 Comments

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

    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

  2. PNRao October 18, 2014 at 1:52 PM

    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!

  3. CAH October 29, 2014 at 12:14 PM

    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.

  4. PNRao October 29, 2014 at 10:44 PM

    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!

  5. PNRao October 30, 2014 at 8:01 PM

    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!

  6. Ali November 1, 2014 at 7:22 AM

    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

  7. PNRao November 1, 2014 at 11:42 AM

    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!

  8. Vaasu December 3, 2014 at 5:56 PM

    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

  9. PNRao December 4, 2014 at 7:00 PM

    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!

  10. vasu December 10, 2014 at 4:17 PM

    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

  11. vasu December 10, 2014 at 7:35 PM

    Hi PNRao,

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

    Regards,
    Murugan

  12. vasu December 15, 2014 at 10:56 PM

    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

  13. Ali January 16, 2015 at 2:01 AM

    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.

  14. PNRao January 16, 2015 at 2:55 PM

    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!

Leave A Comment