vba consultants | vba expert | vba programmer | Excelaccessconsultant

VBA

VBA is based on VB (Visual Basic). This is a programming language derived from BASIC (Beginner’s All-purpose Symbolic Instruction Code). VBA shares a common core of objects and commands as VB but also includes Microsoft application specific objects and language elements. For example, references to Excel objects.

VBA stands for Visual Basic for Applications. VBA is a programming language created by Microsoft. You can use VBA to automate operations in MS office applications like Excel, Word, Access, but we will mainly discuss Excel applications here.

So what can we do in Excel using VBA? The answer is simple: whatever you can do in excel manually, there is a way to automate it with VBA.

How to automate or streamline tasks using VBA:

  • You can record a macro which automatically carries out a series of actions that you frequently perform.
  • Writing a code in the VBA editor: the code will perform various actions in a certain order.
  • Interacting with the user by creating forms or dialogue boxes.
  • Using VBA you can manipulate files that are not currently active.
  • You can also manipulate one application with another application for example, you can make extract a query from Access into your Excel file. Or you can use Excel to send an email through Outlook.
  • You can download information from web sites, such as stock information from Yahoo finance

Writing you first VBA code in excel

Step 1: Open excel workbook, press ALT+F11 or go to Developer tab in Excel Ribbon, and click Visual Basic







Step 2 – Write in the code modular below code

Sub ShowMessagbox()
Msgbox(“Hello world”)
End Sub



When you will run the code below message box will pop at excel workbook.



Congratulation, you have created your first macro and have written your first VBA code.

Variable and declaring the variable:
A variable is one of the most important weapons for applying logic and writing codes dynamically.

A variable is a named area in memory that you use for storing data while a procedure is running.
Variables can be of different types:


String variable – to store text
Integer variables – for storing integer values
Date variables – storing date and time
Boolean variable – for storing True or False
Object variable –to store objects
Array variable – to store multiple pieces of information at the same time (advanced concept)

A few important things you should know about variables and when you use variables:
Variable name must start with a letter and can be up to 255 characters in length.
Variable names must not contain spaces, for example ‘final row’ is wrong it should be ‘final_row’.
Variable names can not contain characters such as periods, mathematical operators (+,-,/,*), exclamation points, comparison operators(=, <>,>, etc), or type declaration characters (&,#, $).

Declaring Variable:
It’s a good idea to declare your variable first:
Dim x as integer
or
Dim x as String
Then you can use the variable in your code, such as:
X = 45
Or
X = Range("A1")

IF statements:

If statements are used in most programming languages for making decisions. Types of If statement for VBA:
If ……Then
If ……Then ………..Else
If …….Then ……..Elself ……..Else

If …….Then
In this statement, you tell VBA that if the condition is met, then execute the next statement. If the condition is not met, skip the next line and reach the End If statement. If statements always begin with “If” and end with “End If”.
If condition Then
[Statements]
End If
Example:
If StAge < 21 then
MsgBox “You may not purchase alcohol”
End If

If ……..then ……..Else:
This statement is used when you decide between two courses of action. You can take one course of action if a condition is True and another course of action if it is False. Syntax:
If condition Then
Statements 1
Else
Statements 2
End If

Example:
If StAge < 21 then
MsgBox “You may not purchase alcohol…underage”
Else
MsgBox “You may purchase”
End If

If ……..Then ……..Elself ……..Else:
This If statement is used when you want VBA to decide between multiple courses of action. You can use multiple Elself statements, it depends on the complexity of your algorithm.

Syntax:
If condition 1 Then
Statement 1
ElseIf condition 2 Then
Statement 2
[ElseIf condition 3 Then
Statement 3]
[Else
Statement 4]
End If

If the condition expressed in condition1 is True, VBA executes statement1, the first block of statements, and then resumes execution at the line after the End If clause. If condition1 is False, VBA branches to the first ElseIf clause and evaluates the condition expressed in condition2. If this is True, VBA executes statement2 and then moves to the line after the End If line; if it’s False, VBA moves to the next ElseIf clause (if there is one) and evaluates its condition (here, condition3).

Loops in VBA:
In VBA you can use loops to repeat actions. By using loops you can transform a simple recorded macro into a powerful program. You usually use loops to repeat an action until a certain condition is met.
Various kind of loops available in VBA –
For ---Next
For Each……Next
Do While…Loop
While…When
Do Until……Loop
Do…Loop While
Do….Loop Until

Each of these have different times and applications when they are most appropriate.

For…Next :
Very useful loop but is limited because it has a fixed number of repititions.

Syntax
For counter = start to end [Step stepsize]
[statement]
[Exit For]
[statement]
Next [counter]

Example:
Sub Example ()
For x = 1 to 10
Cells(x,1).value = x
Next
End Sub
When you run this code it will loop 10 times and will put 1 to 10 values in range A1:A10.

For Each …Next Loops:
Same as For ..next loop where you work with a known number of repetitions but in For Each Next loops a known number is the number of objects in a collection.

Syntax:
For Each object in collection
[statements]
[Exit For]
[statements]
Next [object]

Do …Loops:

Do loops give you more flexibility than For loops because you can test conditions in them and direct the flow of the procedure accordingly.
Loops that test a condition before performing any action. Do While…Loop and Do Until…Loops are used.
Loops that perform an action before testing a condition. Do..Loop While and Do …Loop Until are used.

Syntax for Do While ….Loop:
Do While condition
[statements]
[Exit Do]
[statements]
Loop

Syntax for Do…Loop While:
If the condition is True, the loop continues to run until the condition becomes False, the actions in the loop are executed at least once, whether the condition is True or False

Do
[statements]
[Exit Do]
[statements]
Loop While condition
Syntax for Do Untill…Loop
Do Until condition
[statements]
[Exit Do]
[statements]
Loop

Syntax for Do …Loop Until:
Do
[statements]
[Exit Do]
[statements]
Loop Until condition

Referencing Cells and Ranges in VBA:

You can select a single cell using this code.
Range("A1").Select

If you want to select set of contiguous cells you will use the colon and write:
Range("A1:G5").Select

If you want to select set of non-contiguous cells you will use the comma and write:
Range("A1,A5,B4").Select

If you want to select a set of non-contiguous cells and a range you will use both the colon and the comma:
Range("A1,A5,B4:B8").Select

Cells(1,1).Select is the same thing as Range("A1").Select and Cells(2,4).Select is the same as Range("D2").Select.

The Cells method is useful when using variables and combining with loops ie:
Cells(x,y).select
And
Do Until Cells(x,1) = 5
Loop

You can use Cells when you want to select all the cells of a worksheet. For example:
Cells.Select

To select all cells and then to empty all cells of values or formulas you will use:

Cells.ClearContents

You can combine the offset feature with VBA:
To move one cell down (from A1 to A2): Range("A1").Offset(1,0).Select
To move one cell to the right (from A1 to A2): Range("A1").Offset(0,1).Select
To move one cell up (from B2 to B1): Range("B2").Offset(-1,0).Select
To move one cell to the left (from B2 to A2): Range("B2").Offset(0,-1).Select
To move one cell down from the selected cell: ActiveCell.Offset(1,0).Select
As you can see, the first argument between the parentheses for Offset is the number of rows and the second one is the number of columns. So to move from A1 to G6 you will need: Range("A1").Offset(5,6).Select

This gives you enough to get started in exploring the power of VBA and Excel. Good luck!
Request Quote by Email

*Required fields

  1.  
  2.  
  3.    
  4.  


Testimonials
  • I needed an Excel macro that had a lot of moving parts. Rob helped me spec out exactly what I needed, then delivered the exact functionality, on time. He's a professional.
    Travis Van, ITDatabase
  • I have used ExcelAccessConsultant.com with great satisfaction for many years and continue to use their services. Their ability to efficiently and effectively devise elegant solutions to complex problems is unsurpassed in this space. I could not recommend EAC more highly.
    Justin Barr , Managing Principal, Loan Workout Advisers, LLC.
  • The Access project ExcelAccessConsultant did for me was on time and exceeded the specifications.
    Anonymous
  • (Access project) Rob does excellent work and is a pleasure to work with. He knows his stuff and is very thoughtful about putting solutions together to solve problems. Most importantly, he's extremely collaborative; I feel as though he is a true partner when we work on something together. I hope we get to work together again.
    Bart Schwartz, Industrial Channel Research
  • ExcelAccessConsultant was awesome. I contacted them with my problem. They created a simply spread sheet with formulas that cut my work in half. This project was done in 1 day.
    Kim Shower, Maximum Day Services
  • Excel Access Consultant always delivers a great product on time and within budget. They make sure the customer is happy with the end product. They make sure on the front end that the deliverables are clear so that no time is needed on the back end to correct formatting, calcs, etc.
    Glen Casanova, Principal Consultant, Cobia Capital
  • Rob took the time to listen to my needs and work with me to understand my Access project. His work exceeded my expectations.
    Dino Accettone, Accettone Funeral Home Ltd
  • I found EAC via Google search. In the search field I entered "Excel Guru" - enough said!
    Genie Wood, Marketing Director, TBS Factoring Service
  • EAC has a thorough and professional-level understanding of Access reports. We will definitely call upon them with any of our Access needs.
    Anonymous
  • EAC did a very good job and in a very timely manner. I would use ExcelAccessConsultant again and recommend them.
    Anonymous
  • Rob did an excellent job customizing an Excel spreadsheet that meet our company's need to manage information. He was very responsive and delivered the product in a timely manner. We are very pleased with the entire process and the results. I will not hesitate to use Rob for the company's future needs.
    Alex Leung, Artichoke Joe's Casino
  • VERY HELPFUL, MADE MY REPORT A LOT BETTER. VERY SATISFIED WITH HIS WORK.
    Marc
  • I found Rob when searching the internet. During the project, I was always able to contact Rob directly (as advertised!) and the quality of his work and meeting time schedules exceeded my expectations. Rob is a true professional and I'm very pleased that I choose him for my excel project! Rob was a pleasure to work with and was very fair with billings--thanks again, Rob!
    Darwin Brown, B4CRM
  • Excellent service and response time. I had a tight deadline and EAC was able to work with me to achieve the deadline.
    Anonymous
  • The excel project worked better than I expected it could and made my client very happy.
    Rita
  • Rob showed me he was extremely adept at writing some very difficult codes for a spreadsheet project I worked on.
    Jay Jennings, North Texas Surfaces
  • I contacted EAC with a relatively small project, but needed it fast. They delivered quickly and even helped with a small revision quickly.
    Tom
  • Rob listened to my needs and figured out a solution. His work was timely and he was always responsive. Excellent to work with.
    John Kelly, Managing Director, Janney Capital Markets
  • EAC is customer focused and provides an excellent level of service. They are responsive and also highly capable. I highly recommend the services.
    Tim
  • Rob was very responsive and made adjustments to the project due to some of the miscommunications on my end that made the project more complicated than needed. In the end, Rob sorted it out and we have a functional simple program that is saving us a significant amount of time. Thanks Rob!
    Paul
  • We had a project which would have taken me days to create, I sent it to Rob over a holiday weekend and it was done when I came in the next morning.
    Ryan
  • Responded quickly and accurately. Just what I needed.
    Julie
  • On time . Nailed it first time. Extremely satisifed.
    JR Fogarty, Managing Member, Paradise Enterprises, LLC
  • We needed help fast! We are a medical laboratory that tests 25% of this nation’s blood supply and are under strict FDA regulations for obvious safety reasons. Mr. Terry provided us with a swift, accurate system that not only performed as expected but continues to function. We automated tube the check-in process and Mr. Terry’s system checks every tube # for accuracy and duplication (average of 22,000 tubes per week). The system was supposed to be temporary but is still going strong 7 months later. I do not hesitate to recommend Mr. Terry. Not only did he respond quickly, he stayed around to make sure his work was appropriate for our needs.
    Harry Felker, Database Administrator, Laboratory Information Management Creative Testing Solutions
  • Rob was great to work with. Understood my needs and delivered a solution to fit my needs.
    Anonymous
  • Rob took the time to listen and fully understand what I was trying to accomplish, both tactically and strategically. He then came up with suggestions and executed the project in a timely and efficient manner. His support after the project was outstanding too!
    Frank Weber, Ask Forensics
  • The answers to my questions were explained in a way that I could understand and the results were outstanding.
    Wilfred Dennis