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 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 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 return the row of a search value?

<integer variable> = Application.WorksheetFunction.Match(<search value>, Range("<searched range index>"), <0 for exact match or 1 for approximate>)

Helpful tip: also use error handlers to catch 1004 errors (that arise from not finding a match for your search value)

'example
On Error GoTo ErrorHandler
foundRow = Application.WorksheetFunction.Match(“James Robert”, Range(“A1:A100”), 0)
 
ErrorHandler
 MsgBox “Name not found”
Resume Next

'example - find a name on a sheet, then delete the row containing name
On Error Resume Next
foundRow = Application.WorksheetFunction.Match("JamesRobert", Range("B2:B30"),0)
Rows(foundRow).Delete

 

 

What is the syntax to return part of an inputted date?

DatePart (interval, date1, firstdayofwk, firstdayofyr)
‘interval – can be one of the following listed in bullet points; mandatory
‘date1 – mandatory parameter
‘firstdayofweek – can be values 1-7 for Sunday-Saturday, or 0 for system’s version; optional parameter
‘firstdayofyear – really useless optional parameter, just ignore it

Interval values

  • d – day of the year.
  • m – month of the year
  • yyyy -Year
  • w -Weekday
  • ww -week
  • q -quarter
  • h -Hour
  • n -Minute
  • s –Second
‘examples
MsgBox (DatePart("yyyy", #3/22/2030#)) '2030
MsgBox (DatePart("d", #8/17/2060#)) '17
MsgBox (DatePart("q", "2057-01-15")) '1
MsgBox (DatePart("ww", "2031-12-27")) '52

 

 

What is the syntax to return the difference between two times?

DateDiff (interval, date1, date2, firstdayofwk, firstdayofyr)
‘interval – can be one of the following listed in bullet points; mandatory
‘date1, date2 are mandatory
‘firstdayofweek – can be values 1-7 for Sunday-Saturday, or 0 for system’s version; optional parameter
‘firstdayofyear – really useless optional parameter, just ignore it

Interval values

  • d – day of the year.
  • m – month of the year
  • yyyy -Year
  • w -Weekday
  • ww -week
  • q -quarter
  • h -Hour
  • n -Minute
  • s –Second
‘examples
MsgBox (DateDiff("yyyy", #2/23/2024#, #12/5/2034#)) '10 years
MsgBox (DateDiff("d", #2/1/2050#, #1/28/2050#)) '-4 days
MsgBox (DateDiff("h", "01-Jan-20 00:00:00", "01-Jan-20 23:59:00")) '23 hours
MsgBox (DateDiff("n", "01-Jan-20 12:00:00", "01-Jan-20 12:59:00")) '59 minutes
MsgBox (DateDiff("d", Date, txtDate.Value)) 'difference between a user inputted date and today

 

What is the syntax to return string a certain number of times?

String(number, character)
‘number – number of times value will be returned; mandatory parameter
‘character – character value that will be repeated; mandatory parameter
‘examples
Dim name As String
name = "Steve Musk!"
MsgBox (String(1, "S")) 'S
MsgBox (String(20, "!")) '!!!!!!!!!!!!!!!!!!!!
MsgBox (String(5, "M")) 'MMMMM

 

What is the syntax to replace a string with another?

Replace(String, find, replacewith, start, count, compare)
'string - string that will be searched; mandatory parameter
'find - string part that will be replaced; mandatory parameter
'replacewith - string part that will be inserted; mandatory parameter
'start – integer specifying the starting position of search; optional parameter where default is 1
'count - integer specifying number of times the replacement has to be performed; optional parameter
'compare - integer specifying the comparison method: 0 for binary, 1 for textual; optional parameter
'examples
Dim sentence As String
sentence = "The red fox"
MsgBox (Replace(sentence, "red", "blue", 1, 1)) 'The blue fox
MsgBox (Replace(sentence, "fox", "panda", 1)) 'The red panda
MsgBox (Replace(sentence, "fox", "panda", 9)) 'panda
MsgBox (Replace(sentence, "The", "My")) 'My red fox

 

What is the syntax to split a string from the middle?

Mid (String, start, length)
'String - string from which return value is extracted; mandatory parameter
'start - integer of starting position of extraction; mandatory parameter
'length - length of extraction; optional
'examples
Dim sentence As String
sentence = "blackgraywhite"
MsgBox (Mid(sentence, 6, 4)) 'gray
MsgBox (Mid(sentence, 1, 5)) 'black
MsgBox (Mid(sentence, 10)) 'white

 

What is the syntax to split a string from the right?

Right(String, Length)
‘string – string to be searched; mandatory parameter
‘length – integer specifying the number of characters to be returned; mandatory parameter
'examples
Dim name As String
name = "Elon Gates"
MsgBox (Right(name, 5)) 'Gates
MsgBox (Right(name, 8)) 'on Gates
MsgBox (Right(name, 10)) 'Elon Gates

 

What is the syntax to split a string from the left?

Left(String, Length)
‘string – string to be searched; mandatory parameter
‘length – integer specifying the number of characters to be returned; mandatory parameter
'examples
Dim name As String
name = "Bill Jobs"
MsgBox (Left(name, 4)) 'returns Bill
MsgBox (Left(name, 7)) 'returns Bill Jo
MsgBox (Left(name, 9)) 'returns Bill Jobs

 

 

What is the syntax to return the index of a string found within another string?

InStr (start integer, string1, string2, compare)
‘start integer – specifies starting position for search; optional parameter
‘string1 – string to be searched; mandatory parameter
‘string2 – indicate string value; mandatory parameter
‘compare - sets string comparison between 0 for binary comparison (default) or 1 for text comparison; optional
'example
Dim name As String
name = "Steve Gates"
MsgBox (InStr(1, name, "S")) 'returns 1
MsgBox (InStr(7, name, "hello")) 'returns 0
MsgBox (InStr(1, name, "Gates")) 'returns 7
MsgBox (InStr(name, "Steve")) 'returns 1

Be aware: spaces count as a character, a string’s index starts at 1, and a 0 yield means the string is not contained. The search occurs from left->right. For a right->left search, use InStrRev.

What is the syntax for error handling?

On Error GoTo <Error handler name>
‘normal code here
Exit Sub

<Error handler name>:
‘code here
Resume Next

This entire block of code above is meant to go into a single procedure.

‘example
On Error GoTo ErrorHandler 'Begins the error handling routine
z = 10 / 0 'Divide by ZERO Error Raises
Exit Sub 'exit so does not continue onto the errorhandler again

ErrorHandler: ' Error-handling routine
If Err.Number <> 0 Then '0 means no error
    MsgBox "Error #" & Err.Number & " : " & Chr(10) & Err.Description 'displays the error
End If
Resume Next 'goes to whatever code is after where the error line was

unnamed (3).jpg

What is the syntax to return a string made from substrings?

Join(list, delimiter)
‘list – array containing the subscripts to be joined; mandatory parameter
‘dlimiter – optional parameter with default being space
‘example
Dim grades() As Variant
grades = Array(87, 56, 99, 77)
MsgBox (Join(grades)) 'returns 87 56 99 77
MsgBox (Join(grades, " || ")) 'returns 87 || 56 || 99 || 77

 

 

What is the syntax to split an array based on a value?

Split(expression, delimiter, count, compare)
'expression is a string; only mandatory parameter
'count - how many substrings to be returned. -1 means all substrings are returned
'compare - 0 for binary, 1 for textual

'be aware that the Split function returns an array value, not string value.
‘example – splits the array based on the delimiter “#”
Dim myArray As Variant
Dim size As Integer

myArray = Split("Regular#Regular#Vegetarian#Regular#Vegan", "#")
size = UBound(myArray)

For i = 0 To size
    MsgBox ("Guest " & i & "'s meal option is: " & myArray (i))
Next i

unnamed (2).jpg

What is the syntax to return a filtered array?

Filter(inputStrings, value, include, compare)
'inputstrings - array to be searched; mandatory parameter
'value - search value; mandatory
'include - boolean whether/or not to return the substrings that include/exclude; optional parameter
'compare - binary or textual; optional parameter
‘example
Dim guestList, BlacheFamily As Variant
guestList = Array("Steven Blache", "Melissa Weather", "Kerry Stilt", "Michael Blache", "Karen Blache")
BlacheFamily = Filter(guestList, "Blache")

 
For Each person In BlacheFamily
   MsgBox (person)
Next

‘output is three msgboxes with “Steven Blache” “Michael Blache” and “Karen Blache”

What is the syntax to return the weekday name from date?

WeekDayName(weekday, abbreviate, firstdayofweek)
‘weekday can be values 1-7; mandatory parameter
‘abbreviate can be true or false; optional parameter with false default
‘firstdayofweek can be values 1-7 for Sunday to Saturday; optional parameter
‘examples
MsgBox (WeekdayName(3)) 'returns Tuesday
MsgBox (WeekdayName(1, True)) 'returns Sun
MsgBox (WeekdayName(1, False)) 'returns Sunday
MsgBox (WeekdayName(7, False, 6)) 'returns Thursday

 

What is the syntax to use a Go Sub Return statement?

Go Sub <procedure name>
     <your code here>
Exit Sub

<procedure name>:
     <your code here>
Return
‘example
usersName = InputBox(“Please enter your name.”) ‘1. user must type in his/her name
If usersName=”” Then GoSub NoName ‘2. if user doesn’t give a name, computer goes to sub procedure NoName
Exit Sub ‘5. Put exit sub so it will not run NoName unnecessarily

NoName: ‘3. This procedure is run only if user leaves inputbox blank
     MsgBox (“You did not enter any name”)
Return ‘4. returns to line procedure

What is the syntax to return the index of an active multipage?

Me.<multi-page name>.Value
#returns an integer value
'example for multipage named "mtp"
Private Sub mtp_Change() 'called whenever user goes to new multipage
   If Me.mtp.Value = 1 Then 'if user opens the "Show Guest List" page
        Call loadGuestLists
   End If
End Sub