What is the syntax to retrieve all open workbooks in Excel?

'wb.Name holds the name of the open workbook
Dim wb As Workbook
For Each wb In Application.Workbooks
 <code here with involving wb.Name>
Next wb
'example
Dim wb As Workbook
For Each wb In Application.Workbooks 'for each open workbook, pass the name to lookupEGA()
   MsgBox wb.Name
   lstWorkbooks.AddItem wb.Name
Next wb

 

What is the syntax to open a userform in the top-right screen corner?

 Me.StartUpPosition = 0
 Me.Top = Application.Top + 25
 Me.Left = Application.Left + Application.Width - Me.Width - 25
'example - place in UserForm_Activate procedure
Private Sub UserForm_Activate() 'when form opens
   Me.StartUpPosition = 0
   Me.Top = Application.Top + 25
   Me.Left = Application.Left + Application.Width - Me.Width - 25
End Sub

 

What is the syntax to return the index of the a listbox’s selected value?

#'i' is an integer variable that holds the index
 For i = 0 To <list box name>.ListCount - 1
     If <list box name>.Selected(i) = True Then Exit For
 Next i
#example
 For i = 0 To lstGuestList.ListCount - 1
     If lstGuestList.Selected(i) = True Then Exit For
 Next i

 

What is the syntax to call procedures from another module?

Call <module name>.<sub procedure's name>
#example - project has a userform, and a module called Commands
Private Sub btnRefresh_Click() 'located in a userform
 Call Commands.output
End Sub


Sub output() 'located in Commands module
     MsgBox "This code is in a separate module"
End Sub

 

What is the syntax to run a procedure based on a timer?

Sub <procedure name>()
   'code to be run
   Application.OnTime Now + TimeValue("00:00:00"), "<procedure name>"
End Sub
'example
Sub runsEveryTenSeconds()
    If formClock.Visible = False Then Exit Sub

    Call updateClock
    Application.OnTime Now + TimeValue("00:00:10"), "runsEveryTenSeconds"
End Sub

 

What is the syntax to replace a value in a whole workbook?

Dim Sht As Worksheet
For Each Sht In Worksheets
   Sht.Cells.Replace What:=<old value>, Replacement:=<new value>, LookAt:=xlPart, MatchCase:=False
Next
'example - replaces all instances of person's name
'Old first/last name are in A1, B2 (respectively) in Sheet "Names"
Dim Sht As Worksheet
For Each Sht In Worksheets
    Sht.Cells.Replace What:=Sheets("MasterNames").Cells(1, 1).Value, Replacement:=txtNameFirst.Value, LookAt:=xlPart, MatchCase:=False
    Sht.Cells.Replace What:=Sheets("MasterNames").Cells(1, 2).Value, Replacement:=txtNameLast.Value, LookAt:=xlPart, MatchCase:=False
Next

 

 

What is the syntax to automatically run code when multi-page page changes?

Private Sub <multipage name>_Change()
   If Me.<multipage name>.Value = <pg index under which code should run> Then
      <code to be run when page is activated>
   End If
End Sub
'example for multipage named "mtp"
Private Sub mtp_Change() 'called whenever active page in multipage changes
   If Me.mtp.Value = 1 Then 'if user opens the "Show Guest List" page
        Call loadGuestLists 'automatically runs this code
   End If
End Sub

What is the syntax to count how many times a substring occurs in a string?

<integer variable> = Len(<string>) - Len(Replace(<string>, "<substring>", "")) 
'example
sentence = "Hello, this is a sentence,"
countCommas = Len(sentence) - Len(Replace(sentence, ",", "")) 
msgbox(countCommas) 'output is 2

What is the syntax to exit a procedure?

Exit Sub
#example - exits procedure if something error description happens
Private Sub btnAddNew_Click()
errorStr = errorCheck 'checks for errors ie. missing gaps in textfields

If Not (errorStr = "") Then 'if something wrong with wat user entered
     Exit Sub
End If

End Sub

What is the syntax to read rows in to a list box?

<listbox name>.AddItem Sheets("<sheet name>").Cells(<row>, <column num>).Value & ...etc
#example - reads a list of food entries from a sheet
Private Sub loadListbox()
     lstFoods.Clear
     lastEntryRow = Sheets("Foods").Cells(Sheets("Foods").Rows.Count, "A").End(xlUp).Row 'how many foods are there?

     For i = 2 To lastEntryRow 'adds each food entry row into the listbox
        lineDisplay = Sheets("Foods").Cells(i, 1).Value & Chr(9) & Sheets("Foods").Cells(i, 2).Value
        lstFoods.AddItem lineDisplay 
     Next
End Sub

 

 

What is the syntax to hide Excel and only show userform?

#insert code in ThisWorkbook (under Microsoft Excel Objects in IDE)
Private Sub Workbook_Activate()
     <userform name>.Show
End Sub

#insert code in userform's code
Private Sub UserForm_Initialize()
     Application.Visible = False
End Sub
 
Private Sub UserForm_Terminate()
     Application.Visible = True
End Sub
#example

#in ThisWorkbook object
Private Sub Workbook_Activate()
 frmHome.Show
End Sub

#in userform, frmHome
Private Sub UserForm_Initialize()
 Application.Visible = False
End Sub
 
Private Sub UserForm_Terminate()
 Application.Visible = True
End Sub

 

What is the syntax to close a workbook?

ActiveWorkbook.Close SaveChanges:=<true/false>
#true if want to save changes before closing
#false if disregard changes before closing
#example
Private Sub btnExit_Click()
   ActiveWorkbook.Close SaveChanges:=True 
End Sub

 

 

What is the syntax to clear a filter but leave drop-down icons?

Sheets("<sheet name>").ShowAllData
'examples
Sheets("RevenueSummary").ShowAllData
ActiveSheet.ShowAllData

For another version of clear filter that does remove the filter drop-down icons, click here.

What is the syntax to clear an autofilter?

Sheets("<sheet name>").Range("<chart cell>").AutoFilter
'where chart cell is the right-most, top-most cell containing filtered data
'clears the filter dropdown icons as well
'example
Sheets("RevenueSummary").Range("A1").AutoFilter

For another version of clear filter that doesn’t remove the filter drop-down icons, click here.

 

What is the syntax to find and replace a value?

Sht.Cells.Replace What:=<old value>, Replacement:=<new value>, LookAt:=xlPart, MatchCase:=False
'example - replaces all instances of person's name
'Old first/last name are in A1, B2 (respectively) in Sheet "Names"
Dim Sht As Worksheet
For Each Sht In Worksheets
    Sht.Cells.Replace What:=Sheets("MasterNames").Cells(1, 1).Value, Replacement:=txtNameFirst.Value, LookAt:=xlPart, MatchCase:=False
    Sht.Cells.Replace What:=Sheets("MasterNames").Cells(1, 2).Value, Replacement:=txtNameLast.Value, LookAt:=xlPart, MatchCase:=False
Next

 

 

What is the syntax to read a sheet into a listbox?

lastEntryRow = Sheets("<sheet name>").Cells(Sheets("<sheet name>").Rows.Count, "<column letter>").End(xlUp).Row 
For i = 2 To lastEntryRow 'adds each row to listbox
       lineDisplay = Sheets("<sheet name>").Cells(i, <column you want to show in listbox>).Value & ...etc 
       <listbox name>.AddItem lineDisplay
Next
#example - reads a list of food entries from a sheet
Private Sub loadListbox()
     lstFoods.Clear
     lastEntryRow = Sheets("Foods").Cells(Sheets("Foods").Rows.Count, "A").End(xlUp).Row 'how many foods are there?

     For i = 2 To lastEntryRow 'adds each food entry row into the listbox
        lineDisplay = Sheets("Foods").Cells(i, 1).Value & Chr(9) & Sheets("Foods").Cells(i, 2).Value
        lstFoods.AddItem lineDisplay 
     Next
End Sub

Tips for debugging when working with workbooks

When working with workbooks, you may get Run-Time Error:9. That is, the following message box may pop up during execution:

Capture.PNG

Tips for debugging this error:

  • Is the workbook you are referencing currently closed?
  • Did you misspell the name of the workbook?
  • Are you trying to reference a new workbook that has not been explicitly saved for the first time?
  • Are you running two instances of Excel?
  • Did you pass an index that is greater than the number of workbooks open?

What is the syntax to write to another workbook?

'to retrieve data
Workbooks("<workbook file name>").Worksheets("<sheet name>").Range("<your range>").Value
Workbooks("<workbook file name>").Worksheets("<sheet name>").Cells(<row number>, <column number>).Value

'to set data
Workbooks("<workbook file name>").Workbooks("Worksheets("<sheet name>").Range("<your range>") = <your value/data>
Workbooks("<workbook file name>").Worksheets("<sheet name>").Cells(<row number>, <column number>).Value = <your value/data>
'examples
Workbooks("example.xlsm").Worksheets("Example").Range("A1:A4") = "hello"
Workbooks("book3.xlsx").Worksheets("Example").Range("D1") = "world"
Workbooks("book3.xlsx").Worksheets("Example").Cells(2, 4).Value = "goodbye"

What is the syntax to loop through worksheets?

'Method 1 - looping using a For Each loop
Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets
    <your code here>
Next sht

'Method 2 - looping using a For loop
For i=1 To ThisWorkbook.Worksheets.Count
     <your code here, referencing Worksheets(i)>
Next
'example using both methods
Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets
     sht.Range("B2") = "Hello"
Next sht

For i=1 To ThisWorkbook.Worksheets.Count
     Worksheets(i).Range("B2") = "Hello"
Next

 

What is the syntax to declare a worksheet object?

Dim <object name> As Worksheet
Set <object name> = Worksheets("<sheet name>")
'example of simplification with the worksheet object

'Original code
Worksheets("exampleSheet").Range("A1") = 6
Worksheets("exampleSheet").Range("B2:B9").Font.Italic = True
Worksheets("exampleSheet").Range("B2:B9").Interior.Color = rgbRed

'Simplified code
Dim sht As Worksheet
Set sht = Worksheets("exampleSheet")

sht.Range("A1") = 6
sht.Range("B2:B9").Font.Italic = True
sht.Range("B2:B9").Interior.Color = rgbRed

 

 

Tips for debugging when working with worksheets

When working with worksheets, you may get Run-Time Error:9. That is, the following message box may pop up during execution:

Capture.PNG

Tips for debugging this error:

  • Is the name of the Worksheets spelled correctly?
  • Has the name of the sheet changed and not been updated in your code?
  • Has the worksheet been deleted?
  • Has the index you used to reference the sheet too large?
  • If working with multiple workbooks, did you reference the wrong workbook?

What is the syntax to use/set data in another sheet?

'to retrieve data
Worksheets("<sheet name>").Range("<your range>").Value
Worksheets("<sheet name>").Cells(<row number>, <column number>).Value

'to set data
Worksheets("<sheet name>").Range("<your range>") = <your value/data>
Worksheets("<sheet name>").Cells(<row number>, <column number>).Value = <your value/data>
'examples
Worksheets("Example").Range("A1:A4") = "hello"
Worksheets("Example").Range("D1") = "world"
Worksheets("Example").Cells(2, 4).Value = "goodbye"

Capture.PNG

What is the syntax to search a range?

Range("<your range here>").Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
'What - data to search for; only mandatory parameter
'value (ex) any VBA data type like 123, 12.3, "name"

'After - a single cell to start searching from
'value (ex) Range("A1"), Range("B5")

'LookIn - to search in formulas, values, or comments
'value (ex) xlValues, xlFormulas, xlComments

'LookAt - look at part of cell or entire cell?
'value (ex) xlWhole, xlPart

'SearchOrder - search by rows or columns
'value (ex) xlByRows, xlByColumns

'SearchDirection - search next cell or previous ones
'value (ex) xlNext, xlPrevious

'the following parameter's values are either true or false
'MatchCase - is search case sensitive?
'MatchByte - only relevant if you have installed double-byte language support
'SearchFormat - search by formatting (which is set using Application.FindFormat)

It is critical to note that the Range.Find method does not return a value, rather, it returns a Range object. If nothing is found, the Range object will be Nothing.

'examples

'returns Emily Carn
Range("B3:B20").Find(What:="Emily").Value

'selects cell B8
Range("B3:B20").Find(What:="Emily", MatchCase:=False).Select

'returns 19
Range("B3:B20").Find(What:="Emily", After:=Range("B17")).Row

capture

What is the syntax to run code every time a cell value changes?

'place code in a sheet under Microsoft Excel Objects
Private Sub Worksheet_Change(ByVal Target As Range)
     If target.Row = Range("<range of cell>").Row And target.Column = Range("<range of cells>").Column Then
        <code to be run here>
     End If
End Sub
'example
Private Sub Worksheet_Change(ByVal Target As Range)
     If target.Row = Range("A1").Row And target.Column = Range("A1").Column Then
        MsgBox("The value of A1 has changed")
     End If
End Sub

 

 

What is the syntax to load multiple images at once?

Dim cntrl as Control
For Each cntrl In <userformname>.Controls 'goes through all controls in form
   If TypeOf cntrl Is Image Then 'if control is image
      cntrl.Picture = LoadPicture(<picture's filepath here>)
   End If
Next 'moves onto next control
'example - updates the tiles (which are images) in a connect-3 game
'there are 90 image controls, all with naming convention: r<row>c<column>
'(ex) r01c01, r01c02, r01c03...r09c10
'there are 7 random colours a tile can be, the random number is stored in a sheet

Private Sub updateDisplay() 'call this procedure whenver display wanted to update
Dim cntrl As Control
Dim rr, cc As Integer 'for rows and columns
Dim strPath(7) As String

strPath(0) = "C:\Users\Documents\connect3\images\Yellow.jpg"
strPath(1) = "C:\Users\Documents\connect3\images\Green.jpg"
strPath(2) = "C:\Users\Documents\connect3\images\Pink.jpg"
strPath(3) = "C:\Users\Documents\connect3\images\White.jpg"
strPath(4) = "C:\Users\Documents\connect3\images\Red.jpg"
strPath(5) = "C:\Users\Documents\connect3\images\Cyan.jpg"
strPath(6) = "C:\Users\Documents\connect3\images\Orange.jpg"

For Each cntrl In frmConnect.Controls
   If TypeOf cntrl Is Image Then
      'cntrl.Name is in form r01c01, r01c02...r09c10
      rr = Int(Mid(cntrl.Name, 2, 2)) 'returns 3 if r03c04
      cc = Int(Mid(cntrl.Name, 5, 2)) 'returns 4 if r03c04
      cntrl.Picture = LoadPicture(strPath(Cells(rr, cc).Value))
      rr = rr + 1
      cc = cc + 1
   End If
Next
End Sub

retro

 

What is the syntax for a function to return a value?

Function <function name> (<parameter name> As <data type>)
     'parameters are optional
     <function name> = <your code/calculations here>
End Function
'examples
Function TotalPay (hrs As Integer)
     TotalPay = hrs*12
End Function

Public Function returnOne() As Integer
     returnOne = 1
End Function

 

What is the syntax to generate a random number?

<variable holding number> = (<max> - <min> + 1) * Rnd + <min>
'where max is the highest value the random number can be
'where min is the lowest value the random number can be
'cast to Int if a non-decimal random is desired
'examples

Dim myNumber As Integer
myNumber = Int ((20 - 10 + 1) * Rnd + 10)
'examples of values generated: 12, 10, 20, 23

Dim myNumber As Double
myNumber = (20 - 10 + 1) * Rnd + 10
'examples of values generated: 12.2, 10.2, 10, 20, 19.3, 12.2

What is the syntax to load an image?

<image control name>.Picture = LoadPicture(<file path in string form>)
'examples
profileImg.Picture = LoadPicture("C:\Users\john\Documents\johnsface.jpg")

strPath = "C:\Users\bob\Pictures\Landscapes\picoftrees.jpg"
image1.Picture = LoadPicture(strPath)

 

Helpful Tips

  • To add an Image Control, directly drag an Image from the Toolbox menu onto your UserForm.
  • To change the name of an Image Control, go to the Properties window in the UserForm Editor and change the name under field “(Name)”.
Toolbox Window, Image Control highlighted
Properties Window, Name field pops up first
  • If you do not see your Toolbox or Properties window, go to the top under View and toggle your visibility settings
  • To find the string filepath for your image, you may find this article helpful: How do I find the filepath for a folder?

What is XML Validation?

XML Validation is the process of making sure that XML documents/schemas comply by the rules of the language. This process is similar to compiling of a VBA program. Any good XML editor will display error messages whenever XML language rules are not followed.

Although programs like Notepad can be used to create XML documents, these editors do not have validation features available.