Excel VBA Basic Training

Excel VBA Basic Training

Concepts

VBA Core Concepts

Its very important to make sure your Macros are dynamic.  This means the macro performs as expected when the datasets change format, i.e. different number of rows, different order of columns.  This ensures your output is correct everytime the macro is run.  Often times macros are not dynamic resulting in incorrect output at the end of the procedure.

Record macro defaults to using R1C1 referencing for formulas which is quite confusing to interpret.  Its basically saying offset current cell by R rows and C columns.  Follows format: R[row offset]C[column offset].  So if you’re in cell A1 and you multiply cell B2 by C3 you will get =RC[1]*RC[2].  Row offset is 0 because you are multiplying cells from the same row.  Column offset is 1 and 2 because you are offsetting column A to Column B and C.   As you can see its quite confusing the way macro recorder creates formulas.  Its best practice to change R1C1 to normal Excel formula format, i.e =B2*C2

For example:

ActiveCell.FormulaR1C1 = "=RC[1]*RC[2]"
Is equivalent to:
ActiveCell.Value = "=B2*C2"

If you’re creating simple macros using macro recorder you don’t need to worry about variables.  But if you’re creating complex macros its likely you’ll need to rely on variables, i.e  varX = 3. Variables are used to store values that are either derived from a calculation or pulled in from the excel data.  They can be used interchangeably just like variables in an equation. If you continually need to reference a certain cell in your macro you can save that cell as a variable and then use the variable instead of having to reference the cell directly.

 

Variables data type should be declared as best practice.  For example if the variable is going to be a small number then declare it as an integer, If its a decimal number use variable type Double or if its a text value use String.  You declare a variable using the Dim keyword.  You can require all variables be defined using the keyword “Option Explicit”.  This will require all variable data types to be defined before the code will run.

Example:

Option Explicit
Dim varX as integer

varX = 3

Dim varStr As String

varStr = "This is a text value"
Data Type or SubtypeRequired MemoryDefault ValueVBA ConstantRange
Integer2 bytes0vbInteger–32,768 to 32,767
Long Integer4 bytes0vbLong–2,147,483,648 to 2,147,486,647
Single4 bytes0vbSingle–3402823E38 to –1.401298E–45 or 1.401298E–45 to 3.402823E38
Double8 bytes0vbDouble–1.79769313486232E308 to –4.94065645841247E–324 or 1.79769313486232E308 to 4.94065645841247E–324
Currency8 bytes0vbCurrency–922,337,203,477.5808 to 922,337,203,685,477.5807
Date8 bytes00:00:00vbDateJanuary 1, 100 to December 31, 9999
Fixed StringString’s lengthNumber of spaces to accommodate stringvbString1 to 65,400 characters
Variable String10 bytes plus the number of charactersZero- length string (“”)vbString0 to 2 billion characters
Object4 bytesNothing (vbNothing)vbObjectAny Access object, ActiveX component or Class object
Boolean2 bytesFalsevbBoolean–1 or 0
Variant16 bytesEmpty (vbEmpty)vbVariantSame as Double
Decimal14 bytes0vbDecimal-79,228,162,514,264,337,593,543,950,335 to 79,228,162,514,264,337,593,543,950,335 or –7.2998162514264337593543950335 to 7.9228162514264337593543950335
Byte1 byte0vbByte0 to 255

When creating complex macros you will likely need to use some IF statements.  Which check IF a condition is TRUE or FALSE.  IF TRUE perform one action IF FALSE then perform another action.  For example if you have a data set related to client orders and you need to perform one action for completed orders and a different action for pending orders than an IF statement could be used for this.

Example of VBA IF statement

 Dim OrderStatus As String
OrderStatus = Range("A1").Value
If OrderStatus = "Completed" Then
   'Copy row to Completed Tab
ElseIf OrderStatus = "Pending" Then
   'Copy row to Pending Tab
End If

Loops are a important tool in moving throughout your dataset.  For example you want to check all order statuses in the table.  You would use a Loop to start from the top row and loop through all rows until the last row.  A loop will perform a function multiple times until the exit condition is met.  For a for loop you can declare it run 10 times or you can tell it to run from 1 to the last row.  Or you can tell a loop to run while a condition is true.  Loops enable you to perform a certain action multiple times without having to replicate your code multiple times.

Using a Loop Example:

 Dim i, lastRow As Integer
Dim OrderStatus As String
i = 1
lastRow = ActiveSheet.UsedRange.Rows.Count
For i = 1 To lastRow
  OrderStatus = Range("A1").Value
  If OrderStatus = "Completed" Then
    'Copy row to Completed Tab
  ElseIf OrderStatus = "Pending" Then
    'Copy row to Pending Tab
  End If
Next i

Same example not using a loop

  OrderStatus = Range("A1").Value
  If OrderStatus = "Completed" Then
    'Copy row to Completed Tab
  ElseIf OrderStatus = "Pending" Then
    'Copy row to Pending Tab
  End If
    OrderStatus = Range("A2").Value
  If OrderStatus = "Completed" Then
    'Copy row to Completed Tab
  ElseIf OrderStatus = "Pending" Then
    'Copy row to Pending Tab
  End If
    OrderStatus = Range("A3").Value
  If OrderStatus = "Completed" Then
    'Copy row to Completed Tab
  ElseIf OrderStatus = "Pending" Then
    'Copy row to Pending Tab
  End If
    OrderStatus = Range("A4").Value
  If OrderStatus = "Completed" Then
    'Copy row to Completed Tab
  ElseIf OrderStatus = "Pending" Then
    'Copy row to Pending Tab
  End If
    OrderStatus = Range("A5").Value
  If OrderStatus = "Completed" Then
    'Copy row to Completed Tab
  ElseIf OrderStatus = "Pending" Then
    'Copy row to Pending Tab
  End If

[efb_likebox fanpage_url="https://www.facebook.com/vbamarket/" box_width="840" box_height=340"" responsive="1" show_faces="1" show_stream="1" hide_cover="1" small_header="0" hide_cta="0" locale="en_US"]

Add comment