Paul Kelly VBA Handbook Flashcards
Cycle through a range of data row by row having already set up the range of data
Dim DataCurRow As Range
For Each DataCurRow In rgData.Rows
get a filename/address when the workbook name is already set up as sWorkbook
’ Get the filename
Dim sFilename As String
sFilename = ThisWorkbook.Path & “" & sWorkbook
Check if the file exists in directory
If Dir(sFilename) = “” Then
MsgBox “Could not find the workbook: [” & sFilename _
& “]. Please check this file is in the current folder.”
Exit Sub
End If
Check to see if 2 items have the same text
If StrComp(sUserTeam, sTeam1, vbTextCompare) = 0, then….
Create a dictionary for 2 football teams with a number field. Print one teams score. Test if the other team exists in the dictionary.
Sub DictExample()
Dim dict As New Scripting.Dictionary
'populate the dictionary
dict. Add "Germany", 6
dict. Add "Uruguay", 12
' print the value of france, ie 8
Debug.Print dict("France")
' checks if an entry for Germany
If dict.Exists("Germany") Then
' set the value of Germany to 89
dict("Germany") = 89
Debug.Print dict("Germany")
End If
End Sub
Create a function to format a date with date stated in the function
Function CustomDate() As String
CustomDate = Format(Date, "dddd dd mmmm yyyy")
End Function
Create a function to format a date with the date passed from a sub referencing the custom function
Function CustomDate(DateToFormat As Date) As String
CustomDate = Format(DateToFormat, "dddd dd mmmm yyyy")
End Function
’ call the function
Sub CreateNewSheet()
Worksheets.Add
Range("a1").Value = "Created on " & CustomDate(#1/1/2019#) End Sub
Distinguish between a parameter and an argument
A parameter is the variable in the sub/function declaration.
An argument is the value that you pass to the parameter.
Add a new record to dictionary “dict” with object oTeamCalc and of class module clsTeamCalc and a key of Team1
’ oTeamCalcs is a New object design of clsTeamCalcs
Set oTeamCalcs = New clsTeamCalcs
’ adding a new item with a key being the value of variable sTeam2
dict.Add sTeam2, oTeamCalcs
Test whether Team1 exists in dictionary “dict”
If Not dict.Exists(sTeam2) Then
What is code to cycle through dictionary “dict”
’ go through each team in the dictionary
Dim k as variant
For Each k in dict.Keys
How do we add goals_for to a dictionary “dict” to variable sTeam1, where goals_for is defined in a class module and goal1 is the variable for the current match
dict(sTeam1).goals_for = dict(sTeam1).goals_for + goal1
When in CreateReports where the flow of subs is controlled how do we control a dictionary “dict”?
' Read Data into dictionary
' create a scripting dictionary
Dim dict As Scripting.Dictionary
' get the value from ReadFromdata sub
Set dict = ReadFromdata()’ Create new dictionary
’ Create new dictionary
Dim dict As New Scripting.Dictionary
’ get the user settings
Dim sYear As String, sWorkbook As String
sYear = cnReport.Range(CELL_USER_YEAR)
sWorkbook = cnReport.Range(CELL_USER_WORKBOOK)
’ Open Workbook, dim wkBook as Workbook and refer to sWorkbook
’ Open Workbook
Dim wkBook As Workbook
Set wkBook = Workbooks.Open(ThisWorkbook.Path & “" & sWorkbook, ReadOnly:=True)
’ Get the data range, referring to a constant
’ Get the data range
Dim rgData As Range
Set rgData = shData.Range(DATA_RANGE_START).CurrentRegion
Set rgData = rgData.Offset(1, 0).Resize(rgData.Rows.Count - 1)
’ go through each row
’ go through each row
Dim rgCurRow As Long
For Each rgCurRow In rgData.Rows
Next rgCurRow
What is a class module?
A way of storing a record for each team
’ go through each row,
read the data into variables
check if teams exist, if not add
’ go through each row
Dim rgCurrRow As Range
For Each rgCurrRow In rgData.Rows
‘read the row data into variables
sTeam1 = rgCurrRow.Cells(1, COL_DATA_TEAM1)
goal1 = rgCurrRow.Cells(1, COL_DATA_SCORE1)
sTeam2 = rgCurrRow.Cells(1, COL_DATA_TEAM2)
goal2 = rgCurrRow.Cells(1, COL_DATA_SCORE2)
' Check if team 1 exists and if NOT then create a new oTeamCalcs
If Not dict.Exists(sTeam1) Then
Set oTeamCalcs = New clsTeamCalcs
' sTeam1 is the key, oTeamCalcs is the new container and
' clsTeamCalcs the type of container
dict.Add sTeam1, oTeamCalcs
End If
' Check if team 2 exists and if NOT then create a new oTeamCalcs
If Not dict.Exists(sTeam2) Then
Set oTeamCalcs = New clsTeamCalcs
' sTeam2 is the key, oTeamCalcs is the new container and
' clsTeamCalcs the type of container
dict.Add sTeam1, oTeamCalcs
End If
Next rgCurrRow
Dim sht as worksheet and loop though all worksheets in the current workbook and write “Hello World” in cell A1
Public Sub LoopForEach()
' Writes "Hello World" into cell A1 for each worksheet
Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets
sht.Range("A1") = "Hello World"
Next sht
End Sub
’ Passing a range string/range Address into the sub “PrintName” from sub DoStuff
Sub DoStuff()
' Passing a range string/rangeAddress into the sub "PrintName" as it is called
PrintName cnReport.Range("A1")
PrintName cnReport.Range("b1")
PrintName cnReport.Range("c1")
End Sub
Sub PrintName(rg As Range)
' here we are receiving an argument of cnReport.Range("A1") into the
' parameter rg.
Debug.Print rg
End Sub’ SUB TO PASS FIRST NAME AND SECOND NAME ARGUMENTS INTO ANOTHER SUB. FIRST SUB NAME “PrintNames”, 2nd SUB “name”
' this sub is passing the values into the second sub Public Sub PrintNames() NameFormat "John", "Smith" NameFormat "mary", "jones" End Sub
Public Sub NameFormat(firstname As String, secondname As String)
Dim name As String
name = secondname + ", " + firstname
Debug.Print name
End Sub
What is the default way of passing arguments into subs and functions?
ByRef
What is difference between ByRef and ByVal? Which one should we always use?
ByRef changes the value of the sub/function. ByVal defaults back to the value stated in the calling function.
ByVal.
Pass 20 from sub one to variable “val” in sub 2, using ByVal and Optional. Print val
Public Sub usingOptional()
printStudentNo 20
End Sub
‘ variable val is receiving a value of 20 passed from usingOptional
Sub printStudentNo(Optional ByVal val As Long)
Debug.Print val
End Sub
For Optional Arguments, what should you do
It is good practice to state a value eg (Optional ByVal val as Long = 20).
If you supply a parameter in the calling code it uses the parameter, otherwise it uses the default.
What is important about “:=” in parameters
It enables you to specify an optional parameter without using commas which is much easier where there are multiple parameters
Loop through data to find “Laptop Model A” in column1 and output ot shReport A1 down
Sub UseForCopyOR()
' Get the source data range
Dim rg As Range
Set rg = shData.Range("A1").CurrentRegion
Dim i As Long, row As Long, rgDest As Range
row = 1
' Read through the data one row at a time
For i = 1 To rg.Rows.Count
' Check if the current row has an item of type "Laptop Model A"
OR it is the header i.e. i=1
If (rg.Cells(i, 1).Value2 = "Laptop Model A" _
Or i = 1 Then
' Get the destination range
Set rgDest = shReport.Range("A" & row).Resize(1, rg.Columns.Count)
' Copy the data using the assignment operator(equals sign)
rgDest.Value2 = rg.Rows(i).Value2
row = row + 1
End If
Next i
shReport.Activate
End Sub
What is the standard code for AutoFilter to filter for “Laptop Model A” and past to shReport.Range(“A1”)
’ Clear any existing Autofilter
rg.AutoFilter
' Apply the filter
rg.AutoFilter 1, "Laptop Model A"
' Copy the data
rg.SpecialCells(xlCellTypeVisible).Copy
shReport.Range("A1").PasteSpecial xlPasteValues
' Clear our Autofilter
rg.AutoFilter
set up an advanced filter with rg the range to filter already given with output titles “Stockist..” and “Year”. All in sheet shSales
' specify the output headers
' With shSales
' .Range("j6") = "Stockist/Customer"
' .Range("k6") = "Year"
'
' End With
' Set up advanced filter criteria
Dim CriteriaRange As Range
Set CriteriaRange = shSales.Range("l3:m4")
' set up advanced filter destination
Dim CopyRange As Range
Set CopyRange = shSales.Range("j6:s6")
' run advanced filter
rg.AdvancedFilter xlFilterCopy, CriteriaRange, CopyRange
Copy a cell’s formula to the next row down
rgSrc.Copy
rgSrc.Offset(1, 0).PasteSpecial xlPasteFormulas
