Excel - VBA course
This free course by Stelling Consulting teaches you how to program in MS Excel VBA. It goes far beyond being able to adjust a macro a bit. Some programming expercience will make it easier to follow this challenging course.

This webpage is printer-friendly. Voor de Nederlandse versie, klik hier



Part I is more generic and theoretical


Part II is specifically about VBA in combination with Excel

Index

Part I - VBA "theory"

  • Course goal
  • Command button and user message
  • Procedure syntax
  • Variables
  • Array variables
  • Decisions
  • Loops - unconditional
  • Loops - conditional
  • Loops - direct exit
  • Integral exercise
  • Debugging

Part II - VBA and Excel

  • Reading and writing to a Range of a worksheet
  • Range properties / methods
  • Range and loops
  • Project explorer
  • Macro recording
  • User defined function
  • Option buttons and check boxes
  • Speed up code

Part III - Appendices

  • String functions
  • Error handling
  • Variables: constants & user defined
  • Miscellaneous
  • Elaboration of the integral exercise of Part I

Course goal

After completing this course you are able to create VBA procedures in MS Excel.

VBA can be used to Always use Excel standard functionality to the maximum extent possible and try to limit the amount of VBA code (within reasonable bounds).

Exercise: create a command button

We will learn to write VBA code. That code must be executed. Code execution is normally triggered by pressing a button. So, we will create a command button first. Below you see the final result of this exercise.


Make the Developer ribbon visible, if it is not visible yet. Go to menu File → Options → Customize ribbon. Check Developer in the right screen.


Go to the Developer ribbon → Insert → Command button, in the upper left corner of section ActiveX-controls.


While pressing the mouse left button, draw a rectangle in your worksheet, then release the mouse button.


Give the command button a logical Name (the link to VBA code) and Caption (text on the button). This is done in the properties screen, via right mouse click on the command button. Use some capitals in Name and start the name with
cmd
. Some naming conventions: command buttons start with
cmd
, option buttons with
opt
, check boxes with
chk
, etc.


Double click (left) on the command button to jump into the VBA programming environment (or via ALT-F11). Notice that the 'header and footer of the command button' have been generated automatically. You will recognize the button name and you can tell it is a procedure (Sub) to be executed after a mouse click (_Click).


Enter the follwing code: MsgBox "Hello world. Continue?", vbYesNo, "Continue?". Notice that if you type the comma, a popup list appears with user message options (that can be combined with +). vbYesNo is often used. It creates a Yes and No button on the user message popup screen.


Close the VBA mode (press x at the top right), and exit the Design mode.


Press your command button. If the 'Hello World' user message appears, then you have earned your first study points.
Save your file as *.xlsm or as *.xlsb file!

A user message is often used to inform a user a procedure has finished. Below an example of another, often used, type of message: if the user choses no, then the procedure is not executed. This construct will be used in a later exercise.



Sub procedures and functions - syntax

A Sub procedure performs a task, a Function procedure also gives an output value. The syntax of both is similar. Both use input variables and local variables (temporary memory within a procedure).
Syntax Sub procedure
Sub NameProcedure(InputVariable1 As Type,...,InputVariabeleN As Type)
   Dim LocalVariable1 As Type
   Dim LocalVariableN As Type
   instruction(s)
End Sub
Syntax function procedure
Function NameProcedure(InputVariable1 As Type,...,InputVariableN As Type) As Type
   Dim LocalVariabele1 As Type
   Dim LocalVariabeleN As Type
   instruction(s)
   NameProcedure = LocalVariableM
End Function

Tip: Using some capitals in procedure and variable names prevents typing errors. If you type in the name somewhere else in your code - without capitals - it will automatically change into the name including capitals. If not, then you know you made a typing error.

Variables

Variables are input and local memory of procedures. The word
Dim
meane 'create'. Shown below is a function that sums two integers.

Function Plus(ByVal NumberOne As Integer, ByVal NumberTwo As Integer) As Integer
   Dim SumBoth As Integer
   SumBoth = NumberOne + NumberTwo
   Plus = SumBoth
End Function

Give variables logical names and declarere their type explicitly.

Variable typeMeaningConversion function
IntegerWhole numbers between -32.768 and 32.767CInt   
CInt("10")→10
LongWhole numbers between -2.147.483.648 and 2.147.483.647CLong
SingleFractional numbers between -3,402823 E38 and 3,40283 E38CSng
DoubleFractional numbers between -1,7976931486232 E308 and 1,7976931486232 E308CDbl
CurrencyFractional numbers between -922.337.203.685.477,5807 and 922.337.203.685.477,5807 (always 4 decimals, no rounding error)CCur
StringUp to 65.000 charactersCStr   
CStr(10)→"10"
BooleanTrue or FalseCBool
Date1st of January 100 to 31st of December 9999CDate
Object/RangeEvery object / worksheet cell or ranges
VariantVariabele that automatically detects its type based on input. Can be a matrix, or Null (empty). Default type, if the type has not been declared explicitly.CVar

Intermezzo: go to the VBA environment (via ALT-F11), menu Extra → Options, and check Require Variable Declaration. Line
Option Explicit
will be set automatically each time you create a new file. Each variable has now to be declared explicitly. This reduces the risk of errors.


Array variables

An array variable can be regarded as a list or multidimensional table. Shown below is an example of how to create a table with 100 random numbers. Each element is controlled via its index, here named
iNumber
. Standard VBA function
Rnd()
draws a random fractional number between 0 and 1.

Dim RandomNumber(100) as Single
Dim iNumber as Integer
For iNumber = 1 to 100
   RandomNumber(iNumber)= Rnd()
Next iNumber
An example of a 2-dimensional table with 3 sets of 100 random numbers.

Dim RandomNumber(1 to 3, 1 to 100) as Single
Dim iSet as Integer, iNumber as Integer
For iSet = 1 to 3
   For iNumber = 1 to 100
      RandomNumber(iSet, iNumber)= Rnd()
   Next iNumber
Next iSet
RandomNumber(100) in fact contains 101 elements, because 0 is the first index (like in most programming languages). If you want to start index with 1, then add the following at the top of your code
Option Explicit

Option Base 1


Alternative method is to declare the lowerbound of the array explicitly
as Dim RandomNumber(1 to 100) as Single
You can make array variables grow dynamically, if their size is unknown upfront. Then do not specify dimensions when declaring the array variable:

Dim RandomNumber() as Integer


Suppose you have the first number to be stored:

Redim RandomNumber(1)
RandomNumber(1) = 7


Use
Preserve
if you want to keep values when redimensioning:

Redim Preserve RandomNumber(2)
RandomNumber(2) = 28


Function
UBound
gives the array length, for example
NumberOfRandomNumbers = Ubound(RandomNumber)

With multidimensional array variables
NumberOfRandomNumbersInSet2 = Ubound(RandomNumber,2)

Decisons

Create decisions (conditional program execution) with If Then Else or with Select Case

If condition then
   instructions
End If
If condition Then
   instructions1
Else
   instructions2
End If
If inputNumber<10 Then
   msgBox "This number is smaller than 10"
Else
   msgBox "This number is larger or equal to 10"
End If

If condition1 Then
   instructions1
ElseIf condition2 Then
   instructions2
ElseIf condition3 Then
   instructions3
End If

In above Else If-construction you can use variables of different types. In below Select Case-alternative you cannot.

Select Case VariableValue
   Case 1: instructions1
   Case 2,3: instructions2
   Case 4 to 10: instructions3
   Case Else: instructions4
End Select
Select Case strDay
   Case "MON": dayNr = 1
   Case "TUE": dayNr = 2
   Case "WED": dayNr = 3
   Case "THU": dayNr = 4
   Case "FRI": dayNr = 5
   Case "SAT": dayNr = 6
   Case "SUN": dayNr = 7
End Select

Use
And
and
Or
and
( )
to combine multiple conditions.
If ((i=1) And (t="abc")) Then ...
If ((i=1) Or (t="abc")) Then ...

Use
Not
to create the opposite condition.
If Not((i=1) and ((b=false) Or (t="abc"))) Then ...

Unconditional loops - For Next


A loop executes the same instructions multiple times. Use For Next if it is known upfront how many times instructions should be executed, for example 10 times.

For i=1 to 10
   instructions
Next i
If the step counter should not increase with 1 (default), then specificy step size (note that you can also make it decrease)

For i=2 to 10 step 2
   instructions
Next i
For i=20 to 2 step -4
   instructions
Next i
Use of For Next in combination with an array variable (Ubound gives the length of an array variable).

For i = 1 to Ubound(array)
   instructions
Next i
For Each element In array
   instructions
Next element

Conditional loops - Do While Loop / Do Until Loop

A loop executes the same instructions several times. Use Do While or Do Until if it is unknown upfront how many times the instructions should be executed.

Do While condition
   instructions
Loop
Do Until condition
   instructions
Loop
In case instructions have to be executed at least once:

Do
   instructions
Loop While condition
Do
   instructions
Loop Until condition

Exit loops immediately / halfway

Though code-technically less beautiful (regarded sloppy), it can be handy to use
exit
to exit loops and procedures immediately.
  • End loops: use
    Exit For
    for an unconditional loop, and
    Exit Do
    for a conditional loop
  • End procedures: use
    Exit Sub
    for a sub procedure, and
    Exit Function
    for a function procedure
  • End program: use
    End

For i = 1 to 100
   ...
   If ThisValue = ValueToFind then Exit For
   ContinueSearching
   ...
Next i
Public Sub deleteRecords()
   ...
   If MsgBox("Delete all records?", vbYesNo) = vbNo Then Exit Sub
   DeleteAllRecords
   ...
End Sub

Integral exercise part I: prime number generator

In this exercise you will create a prime number generator. Prime numbers are whole numbers that can only be divided by 1 and itself (1, 2, 3, 5, 7, 11, 13, 17, 19, 23, etc.).

This exercise contains many elements of the previous topics and is meant as an integral exercise. Having an instructor for guidance is handy.

Follow the instructions and step-by-step approach strictly. Also review the code examples given in previous sections. First, read all steps to get an idea of the approach, then start with step 1. Do not try to create shortcuts and skip a step. Resulting code may not be the most efficient, but that is not the goal of this exercise.

If your program does not work correctly at some time - which is likely to happen - then you will have to debug (= detect and remove code errors). Therefore, read the following section about debugging as well.

The program code after each step can be found at the end of this page. Trying yourself first, you will learn/experience the most!
  1. Create a command button that starts a procedure showing numbers 1,2,3,4,etc. (each in a separate message) until the user wants to stop
    • Create a counter variable named iNumber that increases during execution. Use a Do While loop.
    • Use a Msgbox and exit sub to stop.
    • Combine text and a variable in a message as follows "Number = " & str(iNumber)

  2. Create an 'is odd' check
    • Use If Then
    • Function Mod gives the remainder of a division, e.g.(41 Mod 3) gives 2.

  3. Create an array named primeNumber and store all odd numbers in it.
    • Use a counter variable nPrime.
    • Optional: create a dynamically growing array.

  4. Replace the 'is odd'-check of step 2 by an 'is prime number'-check. Store only prime numbers.
    • Use a boolean named isPrimeNumber and initialize its value each time as isPrimeNumber = true.
    • Use a For Next loop to check if the current number is divisible by any of the previously stored prime numbers.
    • Optional: use Exit For to end this check as soon as it turns out a number is divisible.

Debugging

Debugging is detecting and removing code errors (bug = code error).
  • Debugging is often done using 'break points', points at which the code is interrupted/halted.
  • Click in the grey vertical bar at the left of the code to enter a break point.


  • Put the cursor 'within' the first executable line (not 'within' a variable declaration line starting with Dim) and press F5 or the green "play"-triangle. The program will run until it reaches the first break point.


  • Press F8 to execute the next line.
  • Press F5 to execute all lines until the next break point.
  • Press the blue square to stop.

  • Press [BREAK] to kill endless loops.
  • Debug by investigating variable values during execution.


  • Method 1. Put the mouse on top of a variable to see its value directly displayed in a small popup screen.


  • Method 2. Write variable values to a debug screen via
    Debug.Print
    named_variable
    Open the Immediate Window (menu View → Immediate Window; or press CTRL + G).
    Each time the program passes Debug.Print, the Immediate Window is extended with the current value of named_variable.


  • Method 3. Open a debug screen (menu Debug → Add watch) and add variables. You can view arrays (or complete objects) and expand those in the debug screen.

Reading and writing to a worksheet

Reading - Direct cell reference versus cell reference via range name

Direct cell reference
Two alternatives
X = Range("D5").Value
→ X becomes 76

X = Cells(5,4).Value
→ X becomes 76
with 5 = row number, 4 = column number


If you move the complete table,
the code will give a wrong answer
so avoid direct cell reference!

Cell reference via range name (cell with name)
Reference to the cell with a name
X = Range("UpperLeft").Value
→ X becomes 67.

Reference to another cell via the cell with a name
X = Range("Upperleft").Cells(5,4).Value
→ X becomes 20
X = Range("Upperleft").Offset(4,3).Value
→ X becomes 20
X = Range("Table").Cells(5,4).Value
→ X becomes 20

If you move the complete table, the code will still give the correct answer,
as long as range name upperLeft and Table refer to the correct range.

Writing

Writing to a worksheet works the other way round, so for example
Range("Table").Cells(5,4).Value = X


In case of multiple worksheets, specify the sheetname
Sheets("Sheet1").Range("UpperLeft").Value = X


Exercise


Create a single line program that reads values of two input cells named InOne en InTwo and writes the sum of both values to a cell named OUT after pressing a command button Sum.


Exercise


Revisit the prime number generator. Write prime numbers to the worksheet during execution. Do this in such a way the user can drag and drop the numbers somewhere else, so next time you execute the program the generated numbers will start appearing there.


Often used Range properties and methods

Range("TABLE").Value = 7
Gives all cells in range TABLE a value of 7
Range("TABLE") = 7
.Value is default so you may leave it out
Range("TABLE").Value2 = = Range("Copy").Value2
Gives all cells in range TABLE a value of 7. Value2 captures value only, whereas value captures value plus currency format plus date format. Value2 works faster.
Range("TABLE").Copy
Copies all
Range("TABLE").Paste
Pastes all
Range("TABLE").PasteSpecial xlPasteValues
Pastes values only
Range("TABLE").PasteSpecial xlPasteFormulas
Pastes formulas only
Range("TABLE").PasteSpecial xlPasteFormats
Pastes formats only
Range("TABLE").Value = ""
Removes all values
Range("TABLE").ClearContents
Removes all values
Range("TABLE").Clear
Removes everything including formats and protection settings (sometimes unwanted)
Range("TABLE").Rows.Count / .Columns.Count
Gives the number of rows/columns of a range
Range("B:B").EntireColumn.Hidden = true/false
Hides/shows column(s)
Range("5:9").EntireRow.Hidden = true/false

For Each C In Range("ShowHide")
   C.EntireRow.Hidden = (C.Value=0)
Next C
Hides/shows row(s)

Handy to create a range with a formula that becomes 0 if a row should be hidden, 1 if a row should be visible.
Then, simply loop through this formula range to hide/unhide rows.
(Note: faster VBA method possible, but this requires longer and more advanced code.)
Range("TABLE").SpecialCells(xlCellTypeVisible).Value = 7
Gives all visible cells a value of 7. Handy in combination with autofilter.
Range("A1").CurrentRegion = 7
Gives all cells connected to range A1 a value of 7
Range("A1:A100").Offset(row,column).value = 7
Works like standard function OFFSET. With Offset you jump to other cell(s) row rows further and column columns further. Negative values means a jump backwards.
Range("A1").Cells(row,column).value = 7
Cells lijkt op offset maar start in cel zelf: Range("A1").Cells(1,1) points to Range("A1")
Range("A:A").Delete
Range("A1:A100,C1:C100").Delete
Deletes a range
Multiple ranges at once is also possible.
For i = 1 To Sheets.Count
   Sheets(i).Range("A1") = 9
Next i1
Looping through all sheets


Resizing a selected range is done by 
Range("A1").Resize(number_of_rows,number_of_columns)

Range and Loops

Method I
Dim iRow As Long
Dim iCol As Long
Dim R As Range
Set R = Range("MyTable")

For iRow = 1 to R.Rows.Count
   For iCol = 1 to R.Columns.Count
      R.Cells(iRow,iCol).Value="hallo"
   Next iCol
Next iRow
Method II
Dim C As Range
Dim R As Range
Set R = Range("MyTable")

For Each C in R
   C.Value = "hallo"
Next C

Method I uses a row and column counter to loop through all cells in a range; this gives control over the order. Method II is leaner.

Exercise

Fill a range of 5 rows and 5 columns with random numbers between 1 and 10. Create a button that hides all rows and columns with a sum less than 15. Create another button that shows the full table again. Both should keep functioning if the number of rows and columns would change.

Project explorer

Go to the VBA editor. If you do not see the explorer at the left, go to menu View → Project explorer (or press CTRL+R).

VBA code can be found at different places:
  • Sheet1, Sheet2, etc. contain ('private') procedures that logically belong to the sheet (with command buttons in the sheet)
  • ThisWorkbook contains procedures that are executed when opening or closing the file (Private Sub Workbook_Open en Workbook_Close).


  • Module1, Module2, etc. contain procedures usable throughout the whole file. Recorded macro code is found in a module (next chapter). Recording a macro will automatically create a module. Gebruikersgedefinieerde functies moeten in een module staan. Modules can be insterted manually via VBA editor menu Insert.


Macro recording

If you do not know how to convert a manual procedure into VBA code, it is handy to record a macro (Excel knows too many methods/object/properties to learn them all by heart). All manual tasks performed during the macro recording are automatically written into a sub procedure within a module. You can study that code afterwards or adjust it to your needs.


Macro recording often generates 'too much' code
Range("B3:C14").Select
Selection.Copy
Range("J5").Select
ActiveSheet.Paste
Application.CutCopyMode=false

versus
Range("B3:C14").Copy Destination:=Range("J5")
Application.CutCopyMode=false

Application.CutCopyMode=false clears the Clipboard.

If you only want to copy data without format and no filters are ever active, you can do the following:
Range("J5:J16").value2=Range("B3:C14").value2

Or if you want to keep date-format or currency-format:
Range("J5:J16").value=Range("B3:C14").value

This works faster and has as additional advantage that it bypasses the clipboard, so it uses less RAM.

Exercise

Create a table with data. Create a command button that sets the autofilter in a very specific way. Create another command button that clears the filter settings (without removing the filter as such).

User defined function

You can create your own Excel function as a
Public Function
in a module. Add modules via VBA-editor menu Insert. Apart from sitting in a module, the functions themselves are identical to other VBA function procedures.
  • Advantages: you can add missing functionality. You can create smaller/better readable files.
  • Diadvantages: user defined functions run slower than standard functions. Users may have to activate macro's. Sometimes they are not aware of macro's being deactivated, or do not know how to activate them (via File → Options → Trust center → Macro settings).



The example above is a function that calculates the as-the-crow-files-distance between two geographical locations.
On this website there is also an extensive example (including downloadable file) of a user defined function that interpolates within a 2-dimenional tableinterpolateXY example

Exercise: create a user defined function closestLocation

Create a table with fields: Number, X, Y. Create 10 random locations. Create two input cells above the table: X and Y. Create a function that gives the number of the location closest to X,Y as a result.


This exercise is just an exercise. The task itself can be done without VBA, with the following array formula:
{=MATCH(MIN(SQRT((C7:C16-C3)^2+(D7:D16-D3)^2));SQRT((C7:C16-C3)^2+(D7:D16-D3)^2);0)}

Array formulas are not that easy to create and tend to become 'unreadable'. On the other hand, they are standard Excel and often run faster than a VBA-function.

Option buttons and check boxes

Besides command buttons you will often see option buttons and check boxes in user interfaces.

They come in two 'flavors': Forms en ActiveX.
  • Forms: can be read indirectly and are controlled via its linked cell on the worksheet (see picture).
  • ActiveX: can be read directly and controlled by VBA code (e.g.
    Me.chkDieren = true
    ).
Nevertheless we recommend using Forms, because ActiveX controls do not work on Apple computers and they often malfunction when your computer is connected to a beamer (causing your demo to fail).

How to convert an ActiveX button into a Forms button

  1. Given an ActiveX button and code
    Private Sub cmdHelloMsg_Click()
       MsgBox "hello"
    End Sub

  2. Change the word Private to Public
    Public Sub cmdHelloMsg_Click()
       MsgBox "hello"
    End Sub

  3. Create a Forms button and assign the code. This Assign macro screen opens up automatically during the butoon creation process. Remove the ActiveX button.


Exercise - Link VBA code to a Forms command button

Note that in the first exercise you have created an ActiveX command button. This was handy because the syntax of the sub procedure was generated automatically. Now, create a Forms command button and link it to the code as follows. Change the Sub procedure from Private into Public. Select the Forms button → right mouse click → Assign macro → select the sub procedure.

Exercise - Link VBA code to Forms option button and check box

Create a button that gives a user message with the value of the option button and selection box.

Speed up code

  • Use a variable to refer multiple times to a specific range. This keeps the code shorter as well.
    dim R as Range
    Set R = Sheets("SheetXYZ").Range("InvoerXYZ")
    R.value=7

  • Prevent screen refresh during execution / writing of values to the worksheet
    Application.ScreenUpdating = false

  • Prevent recalculation when writing values to the worksheet
    Application.Calculation = xlCalculationManual
  • Do While ...

       First write all values to the worksheet
       Recalculate just once:
    Calculate

       Store outcomes
       Eventually show the progress:
    Application.StatusBar = "Busy calculating item " & str(iItem)

    Loop


    Reactivate regular Excel behavior at the end of the procedure:
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = true
    Application.StatusBar = ""

  • Prevent page break 'bug'
    ActiveSheet.DisplayPageBreaks = False

  • Get rid of any linked pictures, as these cause extreme delay.

  • Use (array) variables instead of the worksheet itself to store data (temporarily) during execution. Only do this if calculation time remains too long, after having done all of the above, because this option means much more programming work.

  • Note that it may also be your workbook formulas that cause the workbook to become (too) slow!!
    Often seen are too many, non-optimized VLOOKUPs to bring over data. If you really want to speed up VLOOKUPs, then sort your data and use VLOOKUPs with argument 'range lookup' set to TRUE, using a two-step-approach: first VLOOKUP on the key-column of the search table itself and check if this VLOOKUP outcome brings the key itself (so you know data is available in that search table), and if so then VLOOKUP once more to bring over data itself. For example: searching 50,000 records requires only 2 (two-step-approach) x 16 steps (as 216 > 50,000) compared to 25,000 on average, so thousand times faster.

  • Beware: if your file has become extremely slow due to having lots of formulas, then it may happen that VBA already continues before all calculations have finished, resulting in wrong outcomes. To make VBA wait until all calculations have finished, you can use the following code:
    Application.Calculation = xlCalculationAutomatic
    Application.Calculate
    Do Until (Application.CalculationState = xlDone)
       DoEvents
    Loop

String functions

  • left("hello",3)
    → "hel" = gives 3 left characters
  • right("hello",2)
    → "lo" = gives 2 right characters
  • mid("hello",3,2)
    → "ll" = gives 2 characters, starting with the 3rd
  • instr("hello","el")
    → 2 = gives the start position of the searched substring within the string. If the result is 0, then substring is not found.
  • "hello" & " and good bye"
    → "hello and good bye" = concatenate text
  • trim(" hello ")
    → "hello" = remove spaces around a word
  • Ucase("hello")
    → "HELLO" = convert to uppercase/capitals
  • Lcase("HELLO")
    → "hello" = convert to lowercase
  • Format(0.8954,"00.00%")
    → 89.45% = create specific text format

Error handling

You can use
GoTo
to 'jump' to another place within the code, skipping part of the code. This is often combined with
On Error
(and
Exit Sub
) to program specific error handling routines.


Autofilters

Activate autofilter of active sheet (filter on two columns)
ActiveSheet.Range("$B$3:$D$8").AutoFilter Field:=2, Criteria1:="2"
ActiveSheet.Range("$B$3:$D$8").AutoFilter Field:=3, Criteria1:="5"


Activate autofilter of a table
ActiveSheet.ListObjects("tableTest").Range.AutoFilter Field:=3, Criteria1:="3"

Deactivating autofilters is necessar if you want to copy-paste all data.


Deactivate autofilter of current worksheet. Note: this does not deactivate filters from tables on the sheet!
If Me.FilterMode Then
   Me.ShowAllData
End If

Deactivate autofilter of a specific table on current worksheet
If Me.ListObjects("tabTest").ShowAutoFilter Then
   Me.ListObjects("tabTest").AutoFilter.ShowAllData
End If

Remove autofilter (if existing) of a named sheet
If Sheets("test").AutoFilterMode Then Sheets("test").Cells.AutoFilter

Reading from files & writing to files

Read from another MS Excel file


  • Deactivate autofilters if you use .copy and .paste and you want to copy all data.
  • Clear old values from the copy-to-range first if you are using flexible ranges (otherwise old data will remain at the end if the new data set is shorter).

Dim ThisApp As String
Dim ThatApp As String
Dim fileName as string

ThisApp = ActiveWorkbook.Name
fileName="C:\test.xlsb"
Workbooks.Open fileName
ThatApp = ActiveWorkbook.Name
Workbooks(ThisApp).Sheets("Sheet1").Range("A1:C10").Value2 = Workbooks(ThatApp).Sheets("Sheet1").Range("A1:C10").Value2
Windows(ThatApp).Close


Write to new MS Excel file


Dim wb
Dim dest
Set wb = ActiveWorkbook
Set dest = Workbooks.Add(xlWBATWorksheet)
wb.Sheets("Sheet1").Range("A1:M1000").Copy
dest.ActiveSheet.Paste
Application.CutCopyMode = False
Application.DisplayAlerts = False 'do not show message that asks if you want to replace an existing file
dest.SaveAs "C:\test.xlsb", FileFormat:=50 '50=xlsb / 51=xlsx
dest.Close
Application.DisplayAlerts = True

Read from .csv file(s)


Dim fileName as string
Dim fileNumber
Dim textLine as string
Dim i as long

fileName="C:\test.csv"
fileNumber = FreeFile
Open fileName For Input As #fileNumber
i = 1
Do Until EOF(fileNumber)
   Line Input #fileNumber, textLine
   Sheets("CSV").Cells(i, 1) = textLine
   i = i + 1
Loop
Close #fileNumber

Sheets("CSV").Columns("A:A").Select
Selection.TextToColumns Destination:=Sheets("CSV").Range("A1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:=False, Space:=False, Other:=False, TrailingMinusNumbers:=True


Looping through multiple input files

Dim thisFile As String
thisFile = Dir("C:\*.xlsx")
Do While (thisFile <> "")
   MsgBox thisFile
   '...process the file...
   thisFile = Dir
Loop

Write to .csv file


Dim FileNumber
Dim ExportFilename as String
Dim textLine as string
Dim iRow as Long

FileNumber = FreeFile
ExportFilename = "c:\test.csv"
Open ExportFilename For Output As #FileNumber
For iRow = 1 To Range("A1").CurrentRegion.Rows.Count
   textLine = Cells(iRow, 1).Value2
   Print #FileNumber, textLine
Next
Close #FileNumber

A double click procedure with file selection window


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   If Target.Cells(1, 1).Address = Range("inputfile1").Address Then
      Dim FilesToRead
      On Error GoTo exitSub
      FilesToRead = Application.GetOpenFilename("(*.xlsb),*.xlsb", MultiSelect:=False)
      If FilesToRead = False Then Exit Sub
      Range("inputfile1").Value = FilesToRead
   End If
exitSub:
End Sub


Variables: constants & user defined

Constants

You can create variables as constants.


In a worksheet - to be used in that worksheet:
Const conPi = 3.14159

In a module - to be used in the whole file:
Public Const conPi = 3.14159


User defined variables

You can define your own variable types, for example if data belongs together:

Cities(1).name = "Haarlem"
Cities(1).longitude = 4.666
Cities(1).latitude = 52.353
Cities(2).name = "Alphen"
Cities(2).longitude = 4.662
Cities(2).latitude = 52.128

Miscellaneous

  • With
    WorksheetFunction.
    you can call standard functions.
    For example:
    XX = WorksheetFunction.RandBetween(1,9)
    → Whole number XX (integer) gets a random value between 1 and 9.

  • Use
    IsError(C.Value)
    or
    IsEmpty(C.Value)
    to check input beforehand / detect invalid input.

  • After a
    .Paste
    or
    .PasteSpecial
    add the code
    Application.CutCopyMode = False
    to empty the clipboard and prevent RAM usage to increase.

    Instead of copying you can transfer values directly (
    R1.Value = R2.Value
    ) to bypass the clipboard. However, no filters should ever be active/relevant, because those are ignored: all data is copied, not data of visible cells only! Even faster is (
    R1.Value2 = R2.Value2
    ) because it ignores date and currency formats and copies just the underlying numerical values.

    The same can be done with formulas, but take care: you must copy to the original formula as well to make the formulas on further rows below link to the correct input cells (so not 1 line below the original formula and further below). This direct method may fail (if rows in between are invisible). It is safer to use .Paste.

  • You can refer to the current active sheet with
    Me
    or
    ActiveSheet
    e.g.
    Me.range("Table").value=""


  • Workbook.RefreshAll
    refreshes all external links and pivot tables.

  • Use
    Application.International(xlDecimalSeparator)
    to get the actual decimal separator used by Excel (and ignore Application.DecimalSeparator)

  • Use
    & _
    to cut off extremely long VBA code lines so you can view them without having to scroll to the right
    If MsgBox("Primenumber" & Str(nPrime) & " = " Str(iNumber) & _
    "Show next prime number?", vbYesNo) = vbNo Then End


  • Repeatable simulation (handy for debugging)
    Rnd
    gives a random number uniformly distributed [0,1>. Thus
    Int((9 * Rnd) + 1)
    gives a random whole number between 1 en 9.
    Randomize
    initializes the random generator with a random seed

    Use the next two line construct - with a fixed value for FixedRandomSeed - to create a simulation that brings the same random numbers each time you restart the simulation: handy for debugging.
    Rnd -1
    Randomize (FixedRandomSeed)


  • You may want to protect your VBA code, optionally with a password (do not forget to tell someone else this password, just in case).




  • Program code prime number generator - Exercise result

    Result step 1


    Short version

    Private Sub cmdStep1_Click()
       Dim iNumber As Long

       iNumber = 0
       Do While True
          iNumber = iNumber + 1
          If MsgBox(iNumber, vbYesNo, "Continue?") = vbNo Then Exit Sub
       Loop
    End Sub


    Longer version, more 'by the book' with regard to the Do While condition

    Private Sub cmdStep1_Click()
       Dim iNumber As Long
       Dim doContinue as boolean

       iNumber = 0
       doContinue = true
       Do While doContinue = True
          iNumber = iNumber + 1
          If MsgBox(iNumber, vbYesNo, "Continue?") = vbNo Then doContinue=false
       Loop
    End Sub


    Result step 2


    Private Sub cmdStep2_Click()
       Dim iNumber As Long
       Dim isOdd As Boolean
       iNumber = 0

       Do While True
          iNumber = iNumber + 1

          isOdd = True
          If (iNumber Mod 2 = 0) Then isOdd = False

          If isOdd Then
             If MsgBox(iNumber, vbYesNo, "Continue?") = vbNo Then Exit Sub
          End If
       Loop
    End Sub


    Result step 3


    Private Sub cmdStep3_Click()
       Dim iNumber As Long
       Dim isOdd As Boolean
       Dim primeNumber(9999999) As Long
       Dim nPrime As Long

       iNumber = 0
       nPrime = 0

       Do While True
          iNumber = iNumber + 1

          isOdd = True
          If (iNumber Mod 2 = 0) Then isOdd = False

          If isOdd Then
             nPrime = nPrime + 1
             primeNumber(nPrime) = iNumber
             If MsgBox(iNumber, vbYesNo, "Continue?") = vbNo Then Exit Sub
          End If
       Loop
    End Sub


    Result step 4

    Including a more elaborated user message.

    Private Sub cmdStep4_Click()
       Dim iNumber As Long
       Dim isPrimeNumber As Boolean
       Dim primeNumber(9999999) As Long
       Dim nPrime As Long
       Dim iPrime As Long

       iNumber = 0
       nPrime = 0

       Do While True
          iNumber = iNumber + 1

          isPrimeNumber = True
          For iPrime = 2 To nPrime
             If iNumber Mod primeNumber(iPrime) = 0 Then
                isPrimeNumber = False
                Exit For
             End If
          Next

          If isPrimeNumber Then
             nPrime = nPrime + 1
             primeNumber(nPrime) = iNumber
             If MsgBox("Primenumber" & Str(nPrime) & " = " & Str(primeNumber(nPrime)) & vbCrLf & "Continue?", vbYesNo, "Continue?") = vbNo Then Exit Sub
          End If
       Loop
    End Sub