What is the syntax to change font color?

'method 1
Cells(<row number>, <column number>).Font.ColorIndex = <color index integer>

'method 2
Range(“<cell reference>”).Font.ColorIndex = <color index integer>
‘examples
Cells(2,4).Font.ColorIndex = 5
Range(“D2”).Font.ColorIndex = 2

 

What is the syntax to use a With statement?

With <object expression>
 .<statements>
End Selection

Use these statements when you need to set many properties for the same object. Improves code readability

‘example without with statement
Selection.HorizontalAlignment = xlCenter
Selection.VerticalAlignment = xlLeft
Selection.Orientation = 0
Selection.WrapText = false
Selection.MergeCells=true

‘example using a with statement
With Selection
 .HorizontalAlignment = xlCenter
 .VerticalAlignment = xlLeft
 .Orientation = 0
 .WrapText = false
 .Mergecells=true
End With

What is the syntax to format borders?

Range(“<cell reference>”).Borders.LineStyle= <line style>
Range(“<cell reference>”).Borders.Weight = <thickness style>
Range(“<cell reference>”).Borders.Color= <rgb color>
  • Line style options: xlContinuous, xlDot, xlDashDotDot, xlDash, xlSlantDashDot, xlDouble
  • Thickness options: xlThin, xlMedium, xlThick
‘examples
Range(“B4”).Borders.LineStyle= xlDash
Range(“B4”).Borders.Color= rgbBlueViolet

 

 

What is the syntax to format a date?

FormatDateTime (date, format)
‘date – mandatory parameter
‘format – specifies which format to use; optional parameter

 

Format values

  • 0 = vbGeneralDate – Default.
  • 1 = vbLongDate
  • 2 = vbShortDate
  • 3 = vbLongTime
  • 4 = vbShortTime

 

‘examples
MsgBox (FormatDateTime("2064-08-15 20:25")) ‘returns 8/15/2064 8:25:00 PM
MsgBox (FormatDateTime(#5/21/2075#, 1)) ‘returns Tuesday, May 21, 2015
MsgBox (FormatDateTime(#5/21/2075#, 2)) ‘returns 5/21/2075
MsgBox (FormatDateTime("12:34:17", 3)) ‘returns 12:34:17 PM
MsgBox (FormatDateTime("2050-08-21 12:34:17", 4)) ‘returns 12:34

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 get the date after adding some time?

DateAdd (interval, number, date)
'all three are mandatory parameters
‘interval – can be one of the following listed in bullet points
‘number – can be any +/- integer
'date – can be variant or literal, represents starting date

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 (DateAdd("yyyy", 1, #1/1/2040#)) '1/1/2041
MsgBox (DateAdd("q", 1, #1/17/2040#)) '4/17/2040
MsgBox (DateAdd("d", -10, #1/17/2040#)) '1/7/2040
MsgBox (DateAdd("ww", 1, date1)) '1/6/1900
MsgBox (DateAdd("h", -1, "01-Jan-2040 12:00:00")) '1/1/2040 11:00:00 AM
MsgBox (DateAdd("n", 1, "01-Jan-2040 12:00:00")) '1/1/2040 12:01:00 PM
MsgBox (DateAdd("s", 1, "01-Jan-2040 12:00:00")) '1/1/2040 12:00:01 PM

 

 

 

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 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 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 select cells?

To select a single cell

Range("<Cell Value>").Select
(ex) Range("A1").Select
(ex) Range("C3").Select

 

To select a continuous range

Range("<Cell Value>").Select
(ex) Range("A1:B4").Select
(ex) Range("C3:F400").Select

 

To select multiple non-continuous ranges

Range("<Cell Value>, <Cell Value>").Select
(ex) Range("A1:E3, H2:H44").Select
(ex) Range("C3:D4, Y3:Y4").Select

 

To select down to the 1st empty cell

Range(Selection, Selection.End(xlDown)).Select

toselectdowntofirstemptycell