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
How to stop the marching ants
’ stops “marching ants”
Application.CutCopyMode = False
Scroll to the bottom of a range once the macro complete (and then up one row)
’ scrolls to where we need to be
ActiveWindow.ScrollRow = Selection.Row - 1
How do you count blank cells (where a formula returns nothing)?
WorksheetFunction.CountBlank
when specifying a range, how to you specify a non contiguous range?
, eg. cnSheet.Range(“a1:a2,c7,d9:e9”)
Create a macro to select a cell A9 using rgRow as a variable
Dim row as long
rgRow = 9
cnSheet.range(“A” & rgRow) = “Test”
for a sub, which is default, private or public?
public
What is Definition and how to you find.
Definition defines the variable. Right click mouse or Shift+F2
VBA to get back to last position
Ctrl + Shift + F2
What does debug compile do?
Checks code for project errors over more than one line
Add a new workbook wkNewReport
’ Declare a varable for a new workbook
Dim wkNewReport As Workbook
' Add a new workbook Set wkNewReport = Workbooks.Add
Add a new worksheet shTemplate to workbook wkNewReport
’ Create a new template after the default Sheet1
shTemplate.Copy after:=wkNewReport.Worksheets(1)
Delete the first worksheet in a workbook
’ Delete Sheet1 as it is not needed
Application.DisplayAlerts = False
wkNewReport.Worksheets(1).Delete
Application.DisplayAlerts = True
Call a function CreateWorkbook and pass values into that function which is CrateWorkbook(ByVal bBuildFormat as Boolean, ByRef shTemplate as Worksheet) As Workbook
CreateWorkbook(True, cnTemplateFormatted)
How do you set a workbook wkNewReport and add a workbook with a worksheet
setwkNewReport = workbooks.Add
shTemplate.copy after:=wkNewReport.Worksheets(1)
RA code to do something if intersect has a value and record key has a value. The something is set the defined cell to the row.
If Not Intersect(Target,Range(“D13:J9999)) is nothing and Range (“D” & Target.Row).Value <> Empty Then
Range(“B2”).Value = Target.Row
End if
Load Contact
Sub Cont_Load
With Sheet1
If .Range(“B2”).value = Empty Then Exit Sub
.Range(“B4”).value = True ‘ set contact load to true
ContRow = .Range(“B2”).Value ‘Returns the Contact
‘Row number
For ContCol = 4 to 10
.Range(.Cells(11,ContCol).Value).value =
Cells(ContRow,ContCol).value
Next ContCol
.Range(“B4).value = False ‘ set contact load to false
End With
sub for IFERROR to wrap it around all existing sheet formula where IFERROR has yet to be added
Sub vbaPracIfError()
’ wrap around IFERROR to all existing formula
Dim cell As Range
Dim formulaRange As Range
Set formulaRange = cells.specialCells(xlCellTypeFormulas)
Debug.Print formulaRange.Address
For Each cell In formulaRange
'check to see "IFERROR ALREADY ADDED
If InStr(cell.Formula, "IFERROR") = 0 Then
'if not then add "IFERROR"
cell.Formula = "=iferror(" & VBA.Mid(cell.Formula, 2) & ","""")"
End If
Next cell
End Sub
What is the difference between
Range(“B6”).interior.colorindex
Range(“B6”).interior.color?
Colorindex returns a very limited number of standard colors. Color is very broad.
How do you execute a command to the immediate window and how do you execute the same command in Excel?
- Immediate window, use a ? at start
2. no ? at start
Find the last column with data (starting with the last column) assigning to x
x.value = cells(5, Columns.Count).End(xlToLeft).Column
Find the last row with data (starting with last row).assigning to x
x.value = Range(“A” & Rows.Count).End(xlUp).Row
Find the last row with data (starting in the data).assigning to x
x.value = Range(“A4”).End(xlDown).row
Assign the address of the current region to x
x.value = Range(“A5”).CurrentRegion.Address
Assign the address of the last used cell in worksheet to x
x.Value = cells.specialCells(xlCellTypeLastCell).Address
Assign to x the number of used rows.
x.Value = ActiveSheet.UsedRange.Rows.Count
Change the color of Row 8 to colorindex 6
Range(“a8”).EntireRow.Interior.ColorIndex = 6
Select a color constant to change the color of an entire row
Range(“a8”).EntireRow.Interior.color = VBA.ColorConstants.vbMagenta
remove any color from a range beginning at A5
Range(“A5 : A” & Cells(Rows.Count, 1).End(xlUp).Row).EntireRow.Interior.Color = Excel.Constants.xlNone
How do we get around the fact that activeSheet does not have intellisense?
set a variable for wht worksheet
Dim Sh As Worksheet
Set Sh = ActiveSheet
when might we refer to Worksheets(1) and why not normally?
When you have a worksheet.
Normally avoid because worksheet (1) will move around if worksheet order is changed.
In vba, what is the difference between using “?” and “*”
? is a wildcard for a 1 character string
* is a wildcard for an any length character string
What function do you use to check if a file exists.
How do you use this function?
DIR function uses a path argument and returns the name of the file or folder. If path name is not found, DIR returns a zero length string “”.
Dim filename as string
filename = VBA.Dir(“C:\Users\Andrew\Downloads\S*”)
If filename = vba.constants.vbNullString then
msgbox “file not existing”
Else
msgbox “exists”
End if
Open a file, copy some data and close the file.
Sub get_data_From_File()
' looks for a file in the location of "this" file
Dim OpenBook As Workbook
Dim fileToopen As Variant
' opens file dialogue box
fileToopen = Application.GetOpenFilename(Title:="Browse for your file to open", filefilter:="Excel Files (*.xls*),*xls*")
If fileToopen <> False Then ' fileToOpen either has a name of it is False (ie. when cancelled)
' open a file
Set OpenBook = Application.Workbooks.Open(fileToopen)
' copy some data
OpenBook.Sheets(1).Range("A20").CurrentRegion.Copy
ThisWorkbook.Worksheets("SelectFile").Range("A10").PasteSpecial xlPasteValues
' close the file
OpenBook.Close False
End If
End Sub
Read a filename into a variable using Dir. What happens if filename does not exist
Dim fileName As String
fileName = VBA.FileSystem.Dir(“C:\Users\Andrew\Dropbox\vba\text file reader\filereader.xlsm”)
’ Check if the variable Filename contains a filename
If fileName = VBA.Constants.vbNullString Then
MsgBox "file not exist"
Else
MsgBox "file exists"
End Ifcheck the filepath exists
if not then create a filepath
Sub checkFilePath()
Dim path As String
Dim filename As String
Dim answer As VbMsgBoxResult
path = "C:\Users\Andrew\Dropbox\vba\text file reader\x2" filename = VBA.Dir(path, vbDirectory)
If filename = VBA.Constants.vbNullString Then
answer = MsgBox("do you wish to create a directory", vbYesNo)
Select Case answer
Case vbYes
VBA.FileSystem.MkDir (path)
Case vbNo
Exit Sub
End Select
Else
MsgBox "folder exists"
End If
End Sub
copy data into a new file and save as a csv
Sub Save_as_CSV()
Dim newBook As Workbook
Dim FileName As String
Application.DisplayAlerts = False
FileName = Application.ThisWorkbook.Path & "\TestTextCSV.csv" Set newBook = Workbooks.Add shCsv.Copy before:=newBook.Sheets(1)
With newBook
.Sheets(1).Rows("1:2").Delete
.SaveAs FileName:=FileName, FileFormat:=Excel.xlCSV
.Close
End With
Application.DisplayAlerts = False MsgBox "Your csv file was exported to the same director as this workbook" End Sub
’ Writing to text file in correct structure
‘ go through each row
‘ then each cell in row
‘ get structure correct first
Sub exportToFilex()
' Writing to text file in correct structure ' go through each row ' then each cell in row ' get structure correct first
Dim fileName As String
Dim ExpRange As Range
Dim ExpRow As Range
Dim ExpCell As Range
Dim myValue As Variant
Dim dLimitR As Variant
dLimitR = "&"
fileName = ThisWorkbook.Path & "\ProjectActivity.csv"
Open fileName For Output As #1
Set ExpRange = shCsv.Range("A6").CurrentRegion.Rows
For Each ExpRow In ExpRange
'Debug.Print ExpRange.Address
For Each ExpCell In ExpRow.Cells
myValue = myValue & ExpCell.Value & dLimitR
Next ExpCell
myValue = Left(myValue, Len(myValue) - 1)
Print #1, myValue
myValue = ""
Next ExpRow
Close #1
End Sub
‘Create a table named tbl in the active sheet
‘Create a table named tbl in the active sheet
Sub convertToTable()
Dim tbl As Range
Dim ws As Worksheet
Set tbl = Range(“A1”).CurrentRegion
Set ws = ActiveSheet
ws.ListObjects.Add(SourceType:=xlSrcRange, Source:=tbl).Name = “Data”
End Sub
add a column to a table tbl in worksheet ws
Sub addColumnTotable()
Dim ws As Worksheet
Dim tbl As ListObject
Set ws = ActiveSheet
Set tbl = ws.ListObjects(“Data”)
tbl.ListColumns.Add(4).Name = “xyz£”
End Sub
Filter the 7th column of a table for >=15 and <=30
Sub addfilter()
Dim ws As Worksheet
Dim tbl As ListObject
Set ws = ActiveSheet
Set tbl = ws.ListObjects(“Data”)
tbl.Range.AutoFilter field:=7, Criteria1:=”>=15”, Operator:=xlAnd, Criteria2:=”<=30”
End Sub
remove duplicates from column1
Sub removeDuplicates()
Dim rg As Range
Set rg = Range("A1").CurrentRegion
rg.removeDuplicates Columns:=1, Header:=xlYes
End Sub
’ delete a databody range and then add a row
’ delete a databody range and then add a row
Sub tablesXXY()
Dim table As ListObject
Set table = Sheet1.ListObjects(“data”)
If Not table.DataBodyRange Is Nothing Then
table.DataBodyRange.Delete
table.ListRows.Add
End If
End Sub
show/hide table headers for table named “Data”
Sub showHeaderRow()
Dim table As ListObject
Set table = ActiveSheet.ListObjects(“Data”)
table.ShowHeaders = Not table.ShowHeaders ‘DEALING HEADERS USING BOOLEAN
If table.ShowHeaders = True Then
table.ShowAutoFilterDropDown = Not table.ShowAutoFilterDropDown
End If
End Sub
show/ hide table totalrow
Sub SHOWTOTALROW()
Dim table As ListObject
Set table = Sheet1.ListObjects("Data")
table.ShowTotals = Not table.ShowTotals
End Sub
toggle autofilter on/off in a table
Sub TurnoffAutoFilter()
‘DOES NOT WORK PRIOR TO EXCEL 2013
Dim table As ListObject
Set table = Sheet1.ListObjects(“Data”)
table.ShowAutoFilterDropDown = Not table.ShowAutoFilterDropDown
table.ShowTableStyleFirstColumn = Not table.ShowTableStyleFirstColumn
End Sub
‘Create a table named tbl in the active sheet
‘Create a table named tbl in the active sheet
Sub convertToTable()
Dim tbl As Range
Dim ws As Worksheet
Set tbl = Range(“A1”).CurrentRegion
Set ws = ActiveSheet
ws.ListObjects.Add(SourceType:=xlSrcRange, Source:=tbl).Name = “Data”
End Sub
Sub addColumnTotable()
Sub addColumnTotable()
Dim ws As Worksheet
Dim tbl As ListObject
Set ws = ActiveSheet
Set tbl = ws.ListObjects(“Data”)
tbl.ListColumns.Add(4).Name = “xyz£”
End Sub
