E VBA specific topics

E.1 Debugging your code

You debug you code to find errors and correct bugs in your program. VBA has a built-in debugger that you may use to step though you code and check if the values in memory are correct. You start and use the debugger using the debugger buttons in the VBA editor, e.g. set the cursor in the top of a sub and press the Step Into button ( F8, ⇧⌘I). You can now repeatedly press the button to step though the code.

In the Locals window you can see the values of the variables as you run you code.

Finally, if you want to run the program until a specific line or code then insert a break-point by clicking the margin of that line in the VBA editor. Next, run you sub and the debugger will stop at that line.

For more details you may have a look at Chapter 9 in Wøhlk (2010) or the videos Debug toolbar, Locals window and Breakpoints.

E.2 Error handling

See https://excelmacromastery.com/vba-error-handling/

E.3 Course procedures

The course have a set of course procedures that you may use ‘as is’ during the course and at the exam without any warranty. I will explicitly state if you are not allowed to use them. An overview is given in Table E.1. All procedures within a topic start with the same suffix so you easy can find them using auto complete in the VBA editor (Ctrl + Space). On a mac you may have to disable the default shortcut (Ctrl + Space) for switching input sources. You can go to the System Preferences -> Keyboard -> Shortcuts -> Input Sources and disable it. For instance all procedures related to arrays start with suffix Ary. Similar the procedures are stored in module ModAry. The modules are stored in the Excel files we use during the course and all course procedures can also be found as text files. If you want to use a course procedure in your own file copy/import the whole module containing the procedure to the Excel file.

Table E.1: Course procedures. See the modules named ModRng, ModAry, ModWst, ModCol, ModTm, and ModRand for further info.

Procedure	Type	Description
AryCopyColumn	sub	Create a 1D array by copying it from a column in a 2D array
AryCopyRow	sub	Create a 1D array by copying it from a row in a 2D array
AryDim	function	Array dimension
AryEmpty	function	Check if an array empty
AryFromCSV	sub	Read a csv file to and array
AryPaste	sub	Paste a 1D or 2D array to a sheet
AryPasteColumn	sub	Paste a column of a 2D array to a sheet
AryPasteRow	sub	Paste a row of a 2D array to a sheet
AryQuickSort	sub	Sort a 2-Dimensional array using a quicksort algorithm
AryRead	sub	Read a range into a 2D array
AryReadLong	sub	Read a range (long format) into an array (up to a 5D array is supported)
AryToSeq	sub	Set all array elements to a sequence
AryToStr	function	Convert an array to a string
AryToVal	sub	Set all array elements to a specific value
Col2Str	function	Convert a collection to a string
ColCopy	function	Copy a collection
RandGenBinomial	sub	Generate random numbers from a binomial distribution
RandGenDiscrete	sub	Generate random numbers from a custom discrete distribution
RandGenNormal	sub	Generate random numbers from a normal distribution
RandGenPoisson	sub	Generate random numbers from a poisson distribution
RandGenUniformCont	sub	Generate random numbers from a continuous uniform distribution
RandGenUniformDisc	sub	Generate random numbers from a discrete uniform distribution
RandInvBinomial	function	Generate a random number from a binomial distribution
RandInvDiscrete	function	Generate a random number from a custom discrete distribution
RandInvNormal	function	Generate a random number from a normal distribution
RandInvPoisson	function	Generate a random number from a Poisson distribution
RandInvUniformCont	function	Generate a random number from a continuous uniform distribution
RandInvUniformDisc	function	Generate a random number from a discrete uniform distribution
RngClear	sub	Clear a range
RngCurRegion	function	Return the current region of a range
RngFormat	sub	Format a range
RngFromCSV	function	Read a csv file and output it to cells
RngGetAddress	function	Return the address of a range.
RngGetColLetter	function	Convert column number to letter
RngGetCols	function	Columns in range
RngGetCurRegionAddress	function	Return the address of the current region of a range
RngGetCurRegionCols	function	Columns in current region
RngGetCurRegionFirstCol	function	First column in current region
RngGetCurRegionFirstRow	function	First row in current region
RngGetCurRegionLastCol	function	Last column in current region
RngGetCurRegionLastRow	function	Last row in current region
RngGetCurRegionLowerLeft	function	Return the lower left cell of the current region
RngGetCurRegionLowerRight	function	Return the lower right cell of the current region
RngGetCurRegionRange	function	Return the part of the current region starting with upper right cell in row and col number (counting within the current range) and lower right corner of the current range.
RngGetCurRegionRows	function	Rows in current region
RngGetCurRegionUpperLeft	function	Return the upper left cell of the current region
RngGetCurRegionUpperRight	function	Return the upper right cell of the current region
RngGetFirstCol	function	First column in range
RngGetFirstRow	function	First row in range
RngGetLastCol	function	Last column in range
RngGetLastRow	function	Last row in range
RngGetLowerLeft	function	Return the lower left cell of the range
RngGetLowerRight	function	Return the lower right cell of the range
RngGetRange	function	Return the part of the range starting with upper right cell in row and col
RngGetRows	function	Rows in range
RngGetUpperLeft	function	Return the upper left cell of the range
RngGetUpperRight	function	Return the upper right cell of the range
RngJoin	function	Join two ranges
RngPaste	function	Paste a range on a sheet.
RngRemoveInterior	sub	Remove fill colors in cell range
RngToCSV	sub	Write a range to a csv file
TmElapsed	function	Time since timer has be started.
TmRestoreAfterSpeedOptimize	sub	Restore properties for the Application object after have called ApplicationSpeedOptimize
TmSpeedOptimize	sub	Set some properties for the Application object to optimize excecution of vba
TmStart	function	Start timer (unit seconds)
WstClear	function	Clear a worksheet if it exists
WstCreate	function	Create a worksheet
WstDelete	function	Delete a worksheet if it exists
WstExists	function	Check if a worksheet exists
WstRename	function	Rename a worksheet if it exists and no sheet with the new name

E.3.1 Detailed descriptions of course procedures

#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> • `` -> `...1`
#> • `` -> `...2`
#> Warning in stri_sub(string, from = start, to = end): argument is not an atomic vector; coercing
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> • `` -> `...1`
#> • `` -> `...2`

E.3.1.1 RngRemoveInterior: Remove fill colors in cell range

Usage

Sub RngRemoveInterior(
   rng As Range
)

Argument	Description
rng	Range to clear.

E.3.1.2 RngFormat: Format a range

Usage

Sub RngFormat(
   rng As Range,
   Optional color As String = "none",
   Optional fit As Boolean = False,
   Optional vertical As Boolean = False,
   Optional merge As Boolean = False,
   Optional wrap As Boolean = False
)

Argument	Description
rng	Range to format.
color	A string equal “normal”, “yellow”, “orange” or “green” (otherwise leave background as is).
fit	Autofit width?
vertical	Make orientation vertical?
merge	Merge range?
wrap	Wrap text?

Only works if you use an english version of Excel since e.g. .Style=“Good” must be replaced with .Style = “God” in DK.

Examples

    Dim rng As Range
    Dim rngNew As Range
    Set rng = RngCurRegion(Range("D7"))         ' get current region
    MsgBox ("Copy to H14 (upper left corner).")
    Set rngNew = RngPaste(rng, Range("H14"))    ' rngNew is now the new range
    MsgBox ("Make yellow.")
    Call RngFormat(rngNew, "yellow")
    MsgBox ("Remove format.")
    Call RngClear(rngNew, blnCells:=False, blnFormat:=True)
    MsgBox ("Clear range.")
    Call RngClear(rngNew)

E.3.1.3 RngGetCurRegionRows: Rows in current region

Usage

Function RngGetCurRegionRows(
   rng As Range
) As Long

Argument	Description
rng	A range within the currentregion

Currentregion is the 2-dim range exapanded until empty cells

Examples

    Dim rng As Range
    ThisWorkbook.Worksheets("TM5").Activate
    Set rng = Range("D7")  ' assume we know that data contains cell D7
    MsgBox ("Address: " & RngGetCurRegionAddress(rng))
    MsgBox ("Rows = " & RngGetCurRegionRows(rng) & " cols = " & RngGetCurRegionCols(rng))
    MsgBox ("First row number = " & RngGetCurRegionFirstRow(rng) & ". Last row number = " & RngGetCurRegionLastRow(rng) & ".")
    MsgBox ("First col number = " & RngGetCurRegionFirstCol(rng) & ". Last col number = " & RngGetCurRegionLastCol(rng) & ".")
    MsgBox ("First col letter = " & RngGetCurRegionFirstCol(rng, True) & ". Last col letter = " & RngGetCurRegionLastCol(rng, True) & ".")

E.3.1.4 RngGetCurRegionCols: Columns in current region

Usage

Function RngGetCurRegionCols(
   rng As Range
) As Long

Argument	Description
rng	A range within the currentregion

Currentregion is the 2-dim range exapanded until empty cells

Examples

    Dim rng As Range
    ThisWorkbook.Worksheets("TM5").Activate
    Set rng = Range("D7")  ' assume we know that data contains cell D7
    MsgBox ("Address: " & RngGetCurRegionAddress(rng))
    MsgBox ("Rows = " & RngGetCurRegionRows(rng) & " cols = " & RngGetCurRegionCols(rng))
    MsgBox ("First row number = " & RngGetCurRegionFirstRow(rng) & ". Last row number = " & RngGetCurRegionLastRow(rng) & ".")
    MsgBox ("First col number = " & RngGetCurRegionFirstCol(rng) & ". Last col number = " & RngGetCurRegionLastCol(rng) & ".")
    MsgBox ("First col letter = " & RngGetCurRegionFirstCol(rng, True) & ". Last col letter = " & RngGetCurRegionLastCol(rng, True) & ".")

E.3.1.5 RngGetColLetter: Convert column number to letter

Usage

Function RngGetColLetter(
   lngCol As Long
) As String

Argument	Description
lngCol	Column number.

E.3.1.6 RngGetCurRegionFirstRow: First row in current region

Usage

Function RngGetCurRegionFirstRow(
   rng As Range
) As Long

Argument	Description
rng	A range within the currentregion

Current region is the 2-dim range expanded until empty cells

Examples

    Dim rng As Range
    ThisWorkbook.Worksheets("TM5").Activate
    Set rng = Range("D7")  ' assume we know that data contains cell D7
    MsgBox ("Address: " & RngGetCurRegionAddress(rng))
    MsgBox ("Rows = " & RngGetCurRegionRows(rng) & " cols = " & RngGetCurRegionCols(rng))
    MsgBox ("First row number = " & RngGetCurRegionFirstRow(rng) & ". Last row number = " & RngGetCurRegionLastRow(rng) & ".")
    MsgBox ("First col number = " & RngGetCurRegionFirstCol(rng) & ". Last col number = " & RngGetCurRegionLastCol(rng) & ".")
    MsgBox ("First col letter = " & RngGetCurRegionFirstCol(rng, True) & ". Last col letter = " & RngGetCurRegionLastCol(rng, True) & ".")

E.3.1.7 RngGetCurRegionFirstCol: First column in current region

Usage

Function RngGetCurRegionFirstCol(
   rng As Range,
   Optional asLetter As Boolean = False
) As Variant

Argument	Description
rng	A range within the currentregion

Currentregion is the 2-dim range expanded until empty cells

Examples

    Dim rng As Range
    ThisWorkbook.Worksheets("TM5").Activate
    Set rng = Range("D7")  ' assume we know that data contains cell D7
    MsgBox ("Address: " & RngGetCurRegionAddress(rng))
    MsgBox ("Rows = " & RngGetCurRegionRows(rng) & " cols = " & RngGetCurRegionCols(rng))
    MsgBox ("First row number = " & RngGetCurRegionFirstRow(rng) & ". Last row number = " & RngGetCurRegionLastRow(rng) & ".")
    MsgBox ("First col number = " & RngGetCurRegionFirstCol(rng) & ". Last col number = " & RngGetCurRegionLastCol(rng) & ".")
    MsgBox ("First col letter = " & RngGetCurRegionFirstCol(rng, True) & ". Last col letter = " & RngGetCurRegionLastCol(rng, True) & ".")

E.3.1.8 RngGetCurRegionLastRow: Last row in current region

Usage

Function RngGetCurRegionLastRow(
   rng As Range
) As Long

Argument	Description
rng	A range within the currentregion

Currentregion is the 2-dim range expanded until empty cells

Examples

    Dim rng As Range
    ThisWorkbook.Worksheets("TM5").Activate
    Set rng = Range("D7")  ' assume we know that data contains cell D7
    MsgBox ("Address: " & RngGetCurRegionAddress(rng))
    MsgBox ("Rows = " & RngGetCurRegionRows(rng) & " cols = " & RngGetCurRegionCols(rng))
    MsgBox ("First row number = " & RngGetCurRegionFirstRow(rng) & ". Last row number = " & RngGetCurRegionLastRow(rng) & ".")
    MsgBox ("First col number = " & RngGetCurRegionFirstCol(rng) & ". Last col number = " & RngGetCurRegionLastCol(rng) & ".")
    MsgBox ("First col letter = " & RngGetCurRegionFirstCol(rng, True) & ". Last col letter = " & RngGetCurRegionLastCol(rng, True) & ".")

E.3.1.9 RngGetCurRegionLastCol: Last column in current region

Usage

Function RngGetCurRegionLastCol(
   rng As Range,
   Optional asLetter As Boolean = False
) As Variant

Argument	Description
rng	A range within the currentregion

Currentregion is the 2-dim range expanded until empty cells

Examples

    Dim rng As Range
    ThisWorkbook.Worksheets("TM5").Activate
    Set rng = Range("D7")  ' assume we know that data contains cell D7
    MsgBox ("Address: " & RngGetCurRegionAddress(rng))
    MsgBox ("Rows = " & RngGetCurRegionRows(rng) & " cols = " & RngGetCurRegionCols(rng))
    MsgBox ("First row number = " & RngGetCurRegionFirstRow(rng) & ". Last row number = " & RngGetCurRegionLastRow(rng) & ".")
    MsgBox ("First col number = " & RngGetCurRegionFirstCol(rng) & ". Last col number = " & RngGetCurRegionLastCol(rng) & ".")
    MsgBox ("First col letter = " & RngGetCurRegionFirstCol(rng, True) & ". Last col letter = " & RngGetCurRegionLastCol(rng, True) & ".")

E.3.1.10 RngGetCurRegionRange: Return the part of the current region starting with upper right cell in row and col number (counting within the current range) and lower right corner of the current range.

Usage

Function RngGetCurRegionRange(
   rng As Range,
   Optional row As Integer = 1,
   Optional col As Integer = 1
) As Range

Argument	Description
rng	A range within the currentregion
row	First row number in the current range.
col	First column number in the current range.

Examples

    RngGetCurRegionRange(rng, 2, 3) ' return the range of the current region starting in row number 2 and column number 3.

E.3.1.11 RngGetCurRegionUpperLeft: Return the upper left cell of the current region

Usage

Function RngGetCurRegionUpperLeft(
   rng As Range,
   Optional asString As Boolean = False
) As Variant

Argument	Description
rng	A range within the currentregion
asString	Return as R1C1 string; otherwise return a Range to the cell.

Examples

  Dim rng As Range
  Set rng = ThisWorkbook.Worksheets("TM5").Range("C5")
  MsgBox ("Address: " & RngGetCurRegionAddress(rng))
  MsgBox ("Number of rows: " & RngGetCurRegionRows(rng))
  MsgBox ("Number of cols: " & RngGetCurRegionCols(rng))
  MsgBox ("First column number: " & RngGetCurRegionFirstCol(rng))
  MsgBox ("First column letter: " & RngGetCurRegionFirstCol(rng, asLetter:=True))
  MsgBox ("Lower left cell: " & RngGetCurRegionLowerLeft(rng, asString:=True))
  MsgBox ("Upper left cell: " & RngGetCurRegionUpperLeft(rng, asString:=True))
  MsgBox ("Lower right cell: " & RngGetCurRegionLowerRight(rng, asString:=True))
  MsgBox ("Upper right cell: " & RngGetCurRegionUpperRight(rng, asString:=True))

E.3.1.12 RngGetCurRegionLowerLeft: Return the lower left cell of the current region

Usage

Function RngGetCurRegionLowerLeft(
   rng As Range,
   Optional asString As Boolean = False
) As Variant

Argument	Description
rng	A range within the currentregion
asString	Return as R1C1 string; otherwise return a Range to the cell.

Examples

  Dim rng As Range
  Set rng = ThisWorkbook.Worksheets("TM5").Range("C5")
  MsgBox ("Address: " & RngGetCurRegionAddress(rng))
  MsgBox ("Number of rows: " & RngGetCurRegionRows(rng))
  MsgBox ("Number of cols: " & RngGetCurRegionCols(rng))
  MsgBox ("First column number: " & RngGetCurRegionFirstCol(rng))
  MsgBox ("First column letter: " & RngGetCurRegionFirstCol(rng, asLetter:=True))
  MsgBox ("Lower left cell: " & RngGetCurRegionLowerLeft(rng, asString:=True))
  MsgBox ("Upper left cell: " & RngGetCurRegionUpperLeft(rng, asString:=True))
  MsgBox ("Lower right cell: " & RngGetCurRegionLowerRight(rng, asString:=True))
  MsgBox ("Upper right cell: " & RngGetCurRegionUpperRight(rng, asString:=True))

E.3.1.13 RngGetCurRegionUpperRight: Return the upper right cell of the current region

Usage

Function RngGetCurRegionUpperRight(
   rng As Range,
   Optional asString As Boolean = False
) As Variant

Argument	Description
rng	A range within the currentregion
asString	Return as R1C1 string; otherwise return a Range to the cell.

Examples

  Dim rng As Range
  Set rng = ThisWorkbook.Worksheets("TM5").Range("C5")
  MsgBox ("Address: " & RngGetCurRegionAddress(rng))
  MsgBox ("Number of rows: " & RngGetCurRegionRows(rng))
  MsgBox ("Number of cols: " & RngGetCurRegionCols(rng))
  MsgBox ("First column number: " & RngGetCurRegionFirstCol(rng))
  MsgBox ("First column letter: " & RngGetCurRegionFirstCol(rng, asLetter:=True))
  MsgBox ("Lower left cell: " & RngGetCurRegionLowerLeft(rng, asString:=True))
  MsgBox ("Upper left cell: " & RngGetCurRegionUpperLeft(rng, asString:=True))
  MsgBox ("Lower right cell: " & RngGetCurRegionLowerRight(rng, asString:=True))
  MsgBox ("Upper right cell: " & RngGetCurRegionUpperRight(rng, asString:=True))

E.3.1.14 RngGetCurRegionLowerRight: Return the lower right cell of the current region

Usage

Function RngGetCurRegionLowerRight(
   rng As Range,
   Optional asString As Boolean = False
) As Variant

Argument	Description
rng	A range within the currentregion
asString	Return as R1C1 string; otherwise return a Range to the cell.

Examples

  Dim rng As Range
  Set rng = ThisWorkbook.Worksheets("TM5").Range("C5")
  MsgBox ("Address: " & RngGetCurRegionAddress(rng))
  MsgBox ("Number of rows: " & RngGetCurRegionRows(rng))
  MsgBox ("Number of cols: " & RngGetCurRegionCols(rng))
  MsgBox ("First column number: " & RngGetCurRegionFirstCol(rng))
  MsgBox ("First column letter: " & RngGetCurRegionFirstCol(rng, asLetter:=True))
  MsgBox ("Lower left cell: " & RngGetCurRegionLowerLeft(rng, asString:=True))
  MsgBox ("Upper left cell: " & RngGetCurRegionUpperLeft(rng, asString:=True))
  MsgBox ("Lower right cell: " & RngGetCurRegionLowerRight(rng, asString:=True))
  MsgBox ("Upper right cell: " & RngGetCurRegionUpperRight(rng, asString:=True))

E.3.1.15 RngJoin: Join two ranges

Usage

Function RngJoin(
   rng1 As Range,
   rng2 As Range
) As Range

Argument	Description
character(

Examples

   Dim rng As Range
   ThisWorkbook.Worksheets("TM5").Activate
   Set rng = RngJoin(Range("A35:B38"), Range("A49:D56"))
   MsgBox (RngGetAddress(rng))  ' note rng is two seperate blocks of cells

E.3.1.16 RngPaste: Paste a range on a sheet.

Usage

Function RngPaste(
   rng As Range,
   rngUL As Range,
   Optional withFormat As Boolean = False
) As Range

Argument	Description
rng	The range to paste
rngUL	The upper left cell to paste to.
withFormat	If true also copy cell format too.

Examples

    Dim rng As Range
    Dim rngNew As Range
    Set rng = RngCurRegion(Range("D7"))         ' get current region
    MsgBox ("Copy to H14 (upper left corner).")
    Set rngNew = RngPaste(rng, Range("H14"))    ' rngNew is now the new range
    MsgBox ("Make yellow.")
    Call RngFormat(rngNew, "yellow")
    MsgBox ("Remove format.")
    Call RngClear(rngNew, blnCells:=False, blnFormat:=True)
    MsgBox ("Clear range.")
    Call RngClear(rngNew)

E.3.1.17 RngClear: Clear a range

Usage

Sub RngClear(
   rng As Range,
   Optional blnCells As Boolean = True,
   Optional blnContents As Boolean = False,
   Optional blnFormat As Boolean = False
)

Argument	Description
rng	Range to clear.
blnCells	Delete cell contents, formats, comments, etc. (default).
blnContents	Delete cell contents.
blnFormat	Delete cell format.

Examples

    Dim rng As Range
    Dim rngNew As Range
    Set rng = RngCurRegion(Range("D7"))         ' get current region
    MsgBox ("Copy to H14 (upper left corner).")
    Set rngNew = RngPaste(rng, Range("H14"))    ' rngNew is now the new range
    MsgBox ("Make yellow.")
    Call RngFormat(rngNew, "yellow")
    MsgBox ("Remove format.")
    Call RngClear(rngNew, blnCells:=False, blnFormat:=True)
    MsgBox ("Clear range.")
    Call RngClear(rngNew)

E.3.1.18 RngCurRegion: Return the current region of a range

Usage

Function RngCurRegion(
   rng As Range
) As Range

Argument	Description
rng	The range to get the current region from.

Examples

    Dim rng As Range
    ThisWorkbook.Worksheets("TM5").Activate
    Set rng = RngCurRegion(Range("D7"))  ' rng now is the current region
    MsgBox ("Address: " & RngGetAddress(rng))
    MsgBox ("Rows = " & RngGetRows(rng) & " cols = " & RngGetCols(rng))
    MsgBox ("First row number = " & RngGetFirstRow(rng) & ". Last row number = " & RngGetLastRow(rng) & ".")
    MsgBox ("First col number = " & RngGetFirstCol(rng) & ". Last col number = " & RngGetLastCol(rng) & ".")
    MsgBox ("First col letter = " & RngGetFirstCol(rng, True) & ". Last col letter = " & RngGetLastCol(rng, True) & ".")

E.3.1.19 RngGetCurRegionAddress: Return the address of the current region of a range

Usage

Function RngGetCurRegionAddress(
   rng As Range
) As String

Argument	Description
rng	The range to get the current region from.

Examples

    Dim rng As Range
    ThisWorkbook.Worksheets("TM5").Activate
    Set rng = Range("D7")  ' assume we know that data contains cell D7
    MsgBox ("Address: " & RngGetCurRegionAddress(rng))
    MsgBox ("Rows = " & RngGetCurRegionRows(rng) & " cols = " & RngGetCurRegionCols(rng))
    MsgBox ("First row number = " & RngGetCurRegionFirstRow(rng) & ". Last row number = " & RngGetCurRegionLastRow(rng) & ".")
    MsgBox ("First col number = " & RngGetCurRegionFirstCol(rng) & ". Last col number = " & RngGetCurRegionLastCol(rng) & ".")
    MsgBox ("First col letter = " & RngGetCurRegionFirstCol(rng, True) & ". Last col letter = " & RngGetCurRegionLastCol(rng, True) & ".")

E.3.1.20 RngGetAddress: Return the address of a range.

Usage

Function RngGetAddress(
   rng As Range
) As String

Argument	Description
rng	The range to get the current region from

Examples

    Dim rng As Range
    ThisWorkbook.Worksheets("TM5").Activate
    Set rng = RngCurRegion(Range("D7"))  ' rng now is the current region
    MsgBox ("Address: " & RngGetAddress(rng))
    MsgBox ("Rows = " & RngGetRows(rng) & " cols = " & RngGetCols(rng))
    MsgBox ("First row number = " & RngGetFirstRow(rng) & ". Last row number = " & RngGetLastRow(rng) & ".")
    MsgBox ("First col number = " & RngGetFirstCol(rng) & ". Last col number = " & RngGetLastCol(rng) & ".")
    MsgBox ("First col letter = " & RngGetFirstCol(rng, True) & ". Last col letter = " & RngGetLastCol(rng, True) & ".")

E.3.1.21 RngGetRows: Rows in range

Usage

Function RngGetRows(
   rng As Range
) As Long

Argument	Description
rng	A range.

Examples

  Dim rng As Range
  Set rng = ThisWorkbook.Worksheets("TM5").Range("C4:E19")
  MsgBox ("Number of rows: " & RngGetRows(rng))
  MsgBox ("Number of cols: " & RngGetCols(rng))
  MsgBox ("First column number: " & RngGetFirstCol(rng))
  MsgBox ("First column letter: " & RngGetFirstCol(rng, asLetter:=True))
  MsgBox ("Lower right cell: " & RngGetLowerRight(rng, asString:=True))
  MsgBox ("Upper right cell: " & RngGetUpperRight(rng, asString:=True))

E.3.1.22 RngGetCols: Columns in range

Usage

Function RngGetCols(
   rng As Range
) As Long

Argument	Description
rng	A range.

Examples

  Dim rng As Range
  Set rng = ThisWorkbook.Worksheets("TM5").Range("C4:E19")
  MsgBox ("Number of rows: " & RngGetRows(rng))
  MsgBox ("Number of cols: " & RngGetCols(rng))
  MsgBox ("First column number: " & RngGetFirstCol(rng))
  MsgBox ("First column letter: " & RngGetFirstCol(rng, asLetter:=True))
  MsgBox ("Lower right cell: " & RngGetLowerRight(rng, asString:=True))
  MsgBox ("Upper right cell: " & RngGetUpperRight(rng, asString:=True))

E.3.1.23 RngGetFirstRow: First row in range

Usage

Function RngGetFirstRow(
   rng As Range
) As Long

Argument	Description
rng	A range.

Examples

    Dim rng As Range
    ThisWorkbook.Worksheets("TM5").Activate
    Set rng = RngCurRegion(Range("D7"))  ' rng now is the current region
    MsgBox ("Address: " & RngGetAddress(rng))
    MsgBox ("Rows = " & RngGetRows(rng) & " cols = " & RngGetCols(rng))
    MsgBox ("First row number = " & RngGetFirstRow(rng) & ". Last row number = " & RngGetLastRow(rng) & ".")
    MsgBox ("First col number = " & RngGetFirstCol(rng) & ". Last col number = " & RngGetLastCol(rng) & ".")
    MsgBox ("First col letter = " & RngGetFirstCol(rng, True) & ". Last col letter = " & RngGetLastCol(rng, True) & ".")

E.3.1.24 RngGetFirstCol: First column in range

Usage

Function RngGetFirstCol(
   rng As Range,
   Optional asLetter As Boolean = False
) As Variant

Argument	Description
rng	A range.

Examples

  Dim rng As Range
  Set rng = ThisWorkbook.Worksheets("TM5").Range("C4:E19")
  MsgBox ("Number of rows: " & RngGetRows(rng))
  MsgBox ("Number of cols: " & RngGetCols(rng))
  MsgBox ("First column number: " & RngGetFirstCol(rng))
  MsgBox ("First column letter: " & RngGetFirstCol(rng, asLetter:=True))
  MsgBox ("Lower right cell: " & RngGetLowerRight(rng, asString:=True))
  MsgBox ("Upper right cell: " & RngGetUpperRight(rng, asString:=True))

E.3.1.25 RngGetLastRow: Last row in range

Usage

Function RngGetLastRow(
   rng As Range
) As Long

Argument	Description
rng	A range.

Examples

    Dim rng As Range
    ThisWorkbook.Worksheets("TM5").Activate
    Set rng = RngCurRegion(Range("D7"))  ' rng now is the current region
    MsgBox ("Address: " & RngGetAddress(rng))
    MsgBox ("Rows = " & RngGetRows(rng) & " cols = " & RngGetCols(rng))
    MsgBox ("First row number = " & RngGetFirstRow(rng) & ". Last row number = " & RngGetLastRow(rng) & ".")
    MsgBox ("First col number = " & RngGetFirstCol(rng) & ". Last col number = " & RngGetLastCol(rng) & ".")
    MsgBox ("First col letter = " & RngGetFirstCol(rng, True) & ". Last col letter = " & RngGetLastCol(rng, True) & ".")

E.3.1.26 RngGetLastCol: Last column in range

Usage

Function RngGetLastCol(
   rng As Range,
   Optional asLetter As Boolean = False
) As Variant

Argument	Description
rng	A range.

Examples

    Dim rng As Range
    ThisWorkbook.Worksheets("TM5").Activate
    Set rng = RngCurRegion(Range("D7"))  ' rng now is the current region
    MsgBox ("Address: " & RngGetAddress(rng))
    MsgBox ("Rows = " & RngGetRows(rng) & " cols = " & RngGetCols(rng))
    MsgBox ("First row number = " & RngGetFirstRow(rng) & ". Last row number = " & RngGetLastRow(rng) & ".")
    MsgBox ("First col number = " & RngGetFirstCol(rng) & ". Last col number = " & RngGetLastCol(rng) & ".")
    MsgBox ("First col letter = " & RngGetFirstCol(rng, True) & ". Last col letter = " & RngGetLastCol(rng, True) & ".")

E.3.1.27 RngGetRange: Return the part of the range starting with upper right cell in row and col

Usage

Function RngGetRange(
   rng As Range,
   Optional row As Integer = 1,
   Optional col As Integer = 1
) As Range

Argument	Description
rng	A range.
row	Row number in the current range.
col	Column number in the current range.

Examples

    RngGetRange(rng, 2, 3) ' return the range of the range starting in row number 2 and column number 3.

E.3.1.28 RngGetUpperLeft: Return the upper left cell of the range

Usage

Function RngGetUpperLeft(
   rng As Range,
   Optional asString As Boolean = False
) As Variant

Argument	Description
rng	A range.

Examples

  MsgBox ("Address: " & RngGetAddress(rng) & vbLf & _
          "Rows: " & RngGetRows(rng) & " " & "Cols: " & RngGetCols(rng) & vbLf & _
          "UL: " & RngGetUpperLeft(rng, asString:=True) & " " & _
          "UR: " & RngGetUpperRight(rng, asString:=True) & vbLf & _
          "LL: " & RngGetLowerLeft(rng, asString:=True) & " " & _
          "LR: " & RngGetLowerRight(rng, asString:=True))

E.3.1.29 RngGetLowerLeft: Return the lower left cell of the range

Usage

Function RngGetLowerLeft(
   rng As Range,
   Optional asString As Boolean = False
) As Variant

Argument	Description
rng	A range.
asString	Return as address string; otherwise return a Range to the cell.

Examples

  MsgBox ("Address: " & RngGetAddress(rng) & vbLf & _
          "Rows: " & RngGetRows(rng) & " " & "Cols: " & RngGetCols(rng) & vbLf & _
          "UL: " & RngGetUpperLeft(rng, asString:=True) & " " & _
          "UR: " & RngGetUpperRight(rng, asString:=True) & vbLf & _
          "LL: " & RngGetLowerLeft(rng, asString:=True) & " " & _
          "LR: " & RngGetLowerRight(rng, asString:=True))

E.3.1.30 RngGetUpperRight: Return the upper right cell of the range

Usage

Function RngGetUpperRight(
   rng As Range,
   Optional asString As Boolean = False
) As Variant

Argument	Description
rng	A range.
asString	Return as R1C1 string; otherwise return a Range to the cell.

Examples

  Dim rng As Range
  Set rng = ThisWorkbook.Worksheets("TM5").Range("C4:E19")
  MsgBox ("Number of rows: " & RngGetRows(rng))
  MsgBox ("Number of cols: " & RngGetCols(rng))
  MsgBox ("First column number: " & RngGetFirstCol(rng))
  MsgBox ("First column letter: " & RngGetFirstCol(rng, asLetter:=True))
  MsgBox ("Lower right cell: " & RngGetLowerRight(rng, asString:=True))
  MsgBox ("Upper right cell: " & RngGetUpperRight(rng, asString:=True))

E.3.1.31 RngGetLowerRight: Return the lower right cell of the range

Usage

Function RngGetLowerRight(
   rng As Range,
   Optional asString As Boolean = False
) As Variant

Argument	Description
rng	A range.
asString	Return as R1C1 string; otherwise return a Range to the cell.

Examples

  Dim rng As Range
  Set rng = ThisWorkbook.Worksheets("TM5").Range("C4:E19")
  MsgBox ("Number of rows: " & RngGetRows(rng))
  MsgBox ("Number of cols: " & RngGetCols(rng))
  MsgBox ("First column number: " & RngGetFirstCol(rng))
  MsgBox ("First column letter: " & RngGetFirstCol(rng, asLetter:=True))
  MsgBox ("Lower right cell: " & RngGetLowerRight(rng, asString:=True))
  MsgBox ("Upper right cell: " & RngGetUpperRight(rng, asString:=True))

E.3.1.32 RngToCSV: Write a range to a csv file

Usage

Sub RngToCSV(
   strFileName As String,
   rng As Range,
   Optional strDelim As String = ";",
   Optional blnAddCharacter As Boolean = False,
   Optional blnAbsPath As Boolean = False
)

Argument	Description
strFileName	File name.
rng	Range given the upper left cell of where to place the data.
strDelim	Delimiter, e.g. “,” in a comma delimited file.
blnAddCharacter	Add quotes around values.
blnAbsPath	If true strName specify the absolute path otherwise create the folder as a subfolder to the current file placement.

Examples

    Dim rng As Range
    ThisWorkbook.Worksheets("TM5").Activate
    Call TM5_ClearTestTM5    ' clear test cells so have empty cells
    '' Write to csv file
    Set rng = Range("C4:E19")
    Call RngToCSV("test.csv", rng, ";")     ' semicolon (;) separated file
    '' Read test.csv file to check
    Range("G3") = "Content of test.csv:"
    Set rng = RngFromCSV("test.csv", Range("G4"), ";")

E.3.1.33 RngFromCSV: Read a csv file and output it to cells

Usage

Function RngFromCSV(
   strFileName As String,
   rngUL As Range,
   Optional strDelim As String = ";",
   Optional strExcludeCharacter As String = "",
   Optional blnAbsPath As Boolean = False
) As Range

Argument	Description
strFileName	File name.
rngUL	The upper left cell of where to place the data.
strDelim	Delimiter, e.g. “,” in a comma delimited file.
strExcludeCharacter	Sometimes csv files have quotes around strings (“value”). If strExcludeCharacter = “““” then removes the quotes.
blnAbsPath	If true strName specify the absolute path otherwise create the folder as a subfolder to the current file placement.

Examples

    Dim rng As Range
    ThisWorkbook.Worksheets("TM5").Activate
    Call TM5_ClearTestTM5    ' clear test cells so have empty cells
    '' Read data1.csv file (NOTE you must know the separator in the csv file beforehand!)
    Range("G3") = "Content of data1.csv:"
    Set rng = RngFromCSV("data1.csv", Range("G4"), ";") ' paste file in range with upper left cell G4
    MsgBox (RngGetAddress(rng))
    '' Read data2.csv file (NOTE you must know the separator in the csv file beforehand!)
    Range("G8") = "Content of data2.csv:"
    Set rng = RngFromCSV("data2.csv", Range("G9"), ",") ' paste file in range with upper left cell G9
    MsgBox (RngGetAddress(rng))
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> • `` -> `...1`
#> • `` -> `...2`

E.3.1.34 AryDim: Array dimension

Usage

Function AryDim(
   ary As Variant
) As Integer

Argument	Description
ary	The array.

E.3.1.35 AryToStr: Convert an array to a string

Usage

Function AryToStr(
   ary As Variant,
   Optional strSep As String = ", "
) As String

Argument	Description
ary	A 1D or 2D array.
strSep	Seperator used to seperate the values.

Examples

   Dim intAry(4) As Integer      ' define array of integers with index 0-4
   ' Set values
   intAry(0) = 9
   intAry(1) = 12
   intAry(2) = 222
   intAry(3) = 4
   intAry(4) = 100
   ' Information about the array
   MsgBox ("Lowest index: " & LBound(intAry))
   MsgBox ("Largest index: " & UBound(intAry))
   MsgBox ("Number of elements   : " & UBound(intAry) - LBound(intAry) + 1)
   MsgBox ("Array as a string: " & AryToStr(intAry))

E.3.1.36 AryPaste: Paste a 1D or 2D array to a sheet

Usage

Sub AryPaste(
   ary As Variant,
   rngUL As Range,
   Optional blnRowwise As Boolean = True
)

Argument	Description
ary	The array.
rng	The upper left cell of the printed cells.
blnRowwise	Paste the 1D array as a column or row (default).

Examples

    Dim ary() As Integer
    Dim strAry() As String
    ThisWorkbook.Worksheets("TM5").Activate
    Call TM5_ClearTestTM5
    '' Set to value single value
    ReDim ary(2)
    Call AryToVal(ary, 5)
    Range("G4") = "A fixed value:"
    Call AryPaste(ary, Range("G5")) ' the upper left cell is G5
    '' Paste vertical
    Range("K4") = "Paste vertical:"
    Call AryPaste(ary, Range("K5"), False)
    '' Set to sequence
    Call AryToSeq(ary, 1, 6)
    Range("G10") = "A sequence:"
    Call AryPaste(ary, Range("G11"))
    '' Read strings from a range
    Call AryRead(strAry, Range("C31:D33"))  ' read a 2D array
    Range("G31") = "Names in the " & AryDim(strAry) & "D array:"
    Call AryPaste(strAry, Range("G32"))

E.3.1.37 AryPasteRow: Paste a row of a 2D array to a sheet

Usage

Sub AryPasteRow(
   ary As Variant,
   intRowIdx As Integer,
   rngUL As Range,
   Optional blnRowwise As Boolean = True
)

Argument	Description
ary	The 2D array.
intRowIdx	The index of the row to paste.
rngUL	The upper left cell of the paste area.
blnRowwise	Paste the row horizontal (default).

Examples

   Dim ary(2 To 3, 2 To 5) As Integer
   Call AryToVal(ary, 5)  ' set all entries to 5
   ' Paste rows
   Call AryPasteRow(ary, 2, Range("B2")) ' paste row with index 2
   Call AryPasteRow(ary, 0, Range("B4")) ' nothing happens since index is not valid
   Call AryPasteRow(ary, 3, Range("B4"), blnRowwise:=False) ' print horizontial
   ' Paste columns
   Call AryPasteColumn(ary, 2, Range("A9")) ' paste column with index 2
   Call AryPasteColumn(ary, 1, Range("B12")) '  nothing happens since index is not valid
   Call AryPasteColumn(ary, 5, Range("B12"), blnColwise:=False) ' print vertical

E.3.1.38 AryPasteColumn: Paste a column of a 2D array to a sheet

Usage

Sub AryPasteColumn(
   ary As Variant,
   intColumnIdx As Integer,
   rngUL As Range,
   Optional blnColwise As Boolean = True
)

Argument	Description
ary	The 2D array.
intColumnIdx	The index of the row to paste.
rngUL	The upper left cell of the paste area.
blnColwise	Paste the column vertical (default).

Examples

   Dim ary(2 To 3, 2 To 5) As Integer
   Call AryToVal(ary, 5)  ' set all entries to 5
   ' Paste rows
   Call AryPasteRow(ary, 2, Range("B2")) ' paste row with index 2
   Call AryPasteRow(ary, 0, Range("B4")) ' nothing happens since index is not valid
   Call AryPasteRow(ary, 3, Range("B4"), blnRowwise:=False) ' print horizontial
   ' Paste columns
   Call AryPasteColumn(ary, 2, Range("A9")) ' paste column with index 2
   Call AryPasteColumn(ary, 1, Range("B12")) '  nothing happens since index is not valid
   Call AryPasteColumn(ary, 5, Range("B12"), blnColwise:=False) ' print vertical

E.3.1.39 AryCopyRow: Create a 1D array by copying it from a row in a 2D array

Usage

Sub AryCopyRow(
   ary As Variant,
   intRowIdx As Integer,
   aryRes As Variant
)

Argument	Description
ary	The 2D array.
intRowIdx	The index of the row.
aryRes	The 1D array to return (dynamic, ByRef). The array will use the same start and end index as the 2D array!

Examples

   Dim ary(2 To 3, 2 To 5) As Integer
   Dim aryRes() As Integer
   ' Copy row
   Call AryToVal(ary, 5)  ' set all entries to 5
   Call AryCopyRow(ary, 2, aryRes)  ' copy row with index 2
   Call AryCopyRow(ary, 1, aryRes)  ' nothing happens since index is not valid
   ' Copy column
   Call AryCopyColumn(ary, 3, aryRes)  ' copy column with index 2
   Call AryCopyColumn(ary, 0, aryRes)  ' nothing happens since index is not valid

E.3.1.40 AryCopyColumn: Create a 1D array by copying it from a column in a 2D array

Usage

Sub AryCopyColumn(
   ary As Variant,
   intColumnIdx As Integer,
   aryRes As Variant
)

Argument	Description
ary	The 2D array.
intColumnIdx	The index of the column.
aryRes	The 1D array to return (dynamic, ByRef). The array will use the same start and end index as the 2D array!

Examples

   Dim ary(2 To 3, 2 To 5) As Integer
   Dim aryRes() As Integer
   ' Copy row
   Call AryToVal(ary, 5)  ' set all entries to 5
   Call AryCopyRow(ary, 2, aryRes)  ' copy row with index 2
   Call AryCopyRow(ary, 1, aryRes)  ' nothing happens since index is not valid
   ' Copy column
   Call AryCopyColumn(ary, 3, aryRes)  ' copy column with index 2
   Call AryCopyColumn(ary, 0, aryRes)  ' nothing happens since index is not valid

E.3.1.41 AryRead: Read a range into a 2D array

Usage

Sub AryRead(
   ByRef ary As Variant,
   ByRef rng As Range,
   Optional intStartIdx1 As Integer = 1,
   Optional intStartIdx2 As Integer = 1,
   Optional blnReduceDim As Boolean = True
)

Argument	Description
ary	Dynamic array.
rng	Range to be copied.
intStartIdx1	Starting index for first dimension (default 1).
intStartIdx2	Starting index for second dimension (default 1).
blnReduceDim	If true then reduce a range with 1 row or column to a 1D array. (post?) The array contains the range values

Examples

    Dim ary() As Integer
    ThisWorkbook.Worksheets("TM5").Activate
    Call TM5_ClearTestTM5
    '' Read values from a range with only 1 column
    Call AryRead(ary, Range("C5:C9"))
    Range("G4") = AryDim(ary) & "D array:"
    Call AryPaste(ary, Range("G5"), False)
    '' Read values from a range with only 1 row
    Call AryRead(ary, Range("C5:E5"))
    Range("I4") = AryDim(ary) & "D array:"
    Call AryPaste(ary, Range("I5"))
    '' Read values from a range with only 1 column/row but use 2D array
    Call AryRead(ary, Range("C11:C14"), blnReduceDim:=False)
    Range("G10") = AryDim(ary) & "D array:"
    Call AryPaste(ary, Range("G11"), False)
    '' Use other start and end index
    Call AryRead(ary, Range("C17:E19"), intStartIdx1:=2, intStartIdx2:=5)
    Range("G16") = AryDim(ary) & "D array with start index " & LBound(ary, 1) & " and " & LBound(ary, 2) & ":"
    Call AryPaste(ary, Range("G17"))

E.3.1.42 AryReadLong: Read a range (long format) into an array (up to a 5D array is supported)

Usage

Sub AryReadLong(
   ByRef ary As Variant,
   ByRef rng As Range,
   Optional vntDefalult As Variant = 0
)

Argument	Description
ary	Dynamic array.
rng	Range to be copied in long format, i.e. index in all columns except the last which contains the values. (post?) The array contains the range values

Examples

    Dim ary() As Integer
    ThisWorkbook.Worksheets("TM5").Activate
    Call TM5_ClearTestTM5
    '' Read 1D array
    Call AryReadLong(ary, Range("A36:B38"), 3)   ' default value = 3
    Range("G35") = "Values in the " & AryDim(ary) & "D array:"
    Call AryPaste(ary, Range("G36"))
    '' Read 2D array
    Call AryReadLong(ary, Range("A41:C47"), 4)   ' default value = 4
    Range("G40") = "Values in the " & AryDim(ary) & "D array:"
    Call AryPaste(ary, Range("G41"))
    '' Read 3D array (cannot be pasted to the sheet, have a look at it using the debugger)
    Call AryReadLong(ary, Range("A50:D56"), 5)   ' default value = 5

E.3.1.43 AryEmpty: Check if an array empty

Usage

Function AryEmpty(
   ary As Variant
) As Boolean

Argument	Description
ary	The variable to check.

E.3.1.44 AryToVal: Set all array elements to a specific value

Usage

Sub AryToVal(
   ByRef ary As Variant,
   value As Variant
)

Argument	Description
ary	A 1D to 5D array.
value	The value.

Examples

    Dim ary() As Integer
    Dim strAry() As String
    ThisWorkbook.Worksheets("TM5").Activate
    Call TM5_ClearTestTM5
    '' Set to value single value
    ReDim ary(2)
    Call AryToVal(ary, 5)
    Range("G4") = "A fixed value:"
    Call AryPaste(ary, Range("G5")) ' the upper left cell is G5

E.3.1.45 AryToSeq: Set all array elements to a sequence

Usage

Sub AryToSeq(
   ByRef ary As Variant,
   lngFrom As Long,
   lngTo As Long,
   Optional lngIdx As Long = 1
)

Argument	Description
ary	A dynamic array (use redim to resize it)
lngFrom	From value.
lngTo	To value.
lngIdx	Start index in the array.

Examples

    Dim ary() As Integer
    Dim strAry() As String
    ThisWorkbook.Worksheets("TM5").Activate
    Call TM5_ClearTestTM5
    '' Set to sequence
    Call AryToSeq(ary, 1, 6)
    Range("G10") = "A sequence:"
    Call AryPaste(ary, Range("G11"))

E.3.1.46 AryQuickSort: Sort a 2-Dimensional array using a quicksort algorithm

Usage

Sub AryQuickSort(
   ByRef ary As Variant,
   Optional lngColumn As Long = 0,
   Optional lngStartIdx As Long = -1,
   Optional lngEndIdx As Long = -1
)

Argument	Description
ary	Array to sort and return.
lngColumn	Column to sort
lngStartIdx	Start index to sort from
lngEndIdx	End index to sort from

https://stackoverflow.com/questions/4873182/sorting-a-multidimensionnal-array-in-vba Posted by Jim Rech 10/20/98 Excel.Programming. Modifications by Nigel Heffernan: Escape failed comparison with empty variant and defensive coding: check inputs

Examples

    Dim ary() As Integer
    ThisWorkbook.Worksheets("TM5").Activate
    Call TM5_ClearTestTM5
    '' Read from a range and sort
    Call AryRead(ary, Range("C5:E19"))
    Call AryQuickSort(ary, 1)
    Range("G3") = "Sort w.r.t. 1. column:"
    Call RngPaste(Range("C4:E4"), Range("G4"))
    Call AryPaste(ary, Range("G5"))

E.3.1.47 AryFromCSV: Read a csv file to and array

Usage

Sub AryFromCSV(
   ary As Variant,
   strFileName As String,
   Optional strDelim As String = ";",
   Optional strExcludeCharacter As String = "",
   Optional blnAbsPath As Boolean = False,
   Optional lngReadFrom As Long = 1
)

Argument	Description
strFileName	File name.
strDelim	Delimiter, e.g. “,” in a comma delimited file.
strExcludeCharacter	Sometimes csv files have quotes around strings (“value”). If strExcludeCharacter = “““” then removes the quotes.
blnAbsPath	If true strName specify the absolute path otherwise create the folder as a subfolder to the current file placement.
lngReadFrom	The line to read from.

Inspired by http://stackoverflow.com/questions/9564908/open-csv-file-via-vba-performance Will not work if the cell values contain the delimeter. You MUST use blnAbsPath = True if the files are stored at a network folder (e.g. OneDrive) and specify the full path.

Examples

    Dim rng As Range
    Dim ary() As Integer
    ThisWorkbook.Worksheets("TM5").Activate
    Call TM5_ClearTestTM5
    '' Read csv file (NOTE you must know the separator in the csv file beforehand!)
    Range("G3") = "Array values:"
    Call AryFromCSV(ary, "data2.csv", ",")   ' know that it contains integers (otherwise use variant)
    Call AryPaste(ary, Range("G4"))
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> • `` -> `...1`
#> • `` -> `...2`

E.3.1.48 WstExists: Check if a worksheet exists

Usage

Function WstExists(
   strName As String
) As Boolean

Argument	Description
strName	Name of worksheet.

Examples

    If WstExists("Test") Then MsgBox ("Found it!")

E.3.1.49 WstDelete: Delete a worksheet if it exists

Usage

Function WstDelete(
   strName As String
) As Boolean

Argument	Description
strName	Name of worksheet.

Examples

    If WstDelete("Test1") Then MsgBox ("Deleted Test1")

E.3.1.50 WstCreate: Create a worksheet

Usage

Function WstCreate(
   strName As String,
   Optional blnForce As Boolean = False
) As Boolean

Argument	Description
strName	Name of worksheet.
blnForce	Force deletion of worksheet if exists.

Examples

    If WstCreate("Test", blnForce:=True) Then MsgBox ("Created Test")  ' create Test sheet

E.3.1.51 WstRename: Rename a worksheet if it exists and no sheet with the new name

Usage

Function WstRename(
   strName As String,
   strNewName As String
) As Boolean

Argument	Description
strName	Name of worksheet.
strNewName	New name of worksheet.

Examples

    If WstRename("Test", "Test1") Then MsgBox ("Renamed the Test to Test1")  ' only work if no Test1 sheet

E.3.1.52 WstClear: Clear a worksheet if it exists

Usage

Function WstClear(
   strName As String,
   Optional blnCells As Boolean = True,
   Optional blnContents As Boolean = False,
   Optional blnFormat As Boolean = False,
   Optional blnObjects As Boolean = False
) As Boolean

Argument	Description
strName	Name of worksheet.
blnCells	Delete cell contents, formats, comments, etc. (default).
blnContents	Delete only cell contents.
blnFormat	Delete only cell format.
blnObjects	Delete cell buttons and charts too.

Examples

    If WstClear("Test8") Then MsgBox ("Cleared Test8") ' no clearing since if no sheet with that name
#> New names:
#> New names:
#> • `` -> `...1`
#> • `` -> `...2`

E.3.1.53 Col2Str: Convert a collection to a string

Usage

Function Col2Str(
   col As Collection,
   Optional strSep As String = ", "
) As String

Argument	Description
col	A collection.
strSep	Seperator used to seperate the values.

E.3.1.54 ColCopy: Copy a collection

Usage

Function ColCopy(
   colFrom As Collection
) As Collection

Argument	Description
colFrom	The collection to copy.

#> Warning in stri_sub(string, from = start, to = end): argument is not an atomic vector; coercing
#> New names:
#> New names:
#> • `` -> `...1`
#> • `` -> `...2`
#> Warning in stri_sub(string, from = start, to = end): argument is not an atomic vector; coercing
#> New names:
#> • `` -> `...1`
#> • `` -> `...2`
#> Warning in stri_sub(string, from = start, to = end): argument is not an atomic vector; coercing
#> New names:
#> • `` -> `...1`
#> • `` -> `...2`

E.3.1.55 TmStart: Start timer (unit seconds)

Usage

Function TmStart() As Double

Argument	Description
character(

Examples

    dblTimer = TmStart()
    Application.Wait (Now() + TimeValue("0:00:02"))   ' wait for approx 2 sec
    MsgBox (TmElapsed(dblTimer, "sec"))

E.3.1.56 TmElapsed: Time since timer has be started.

Usage

Function TmElapsed(
   dblTimer As Double,
   Optional strUnit As String = "sec"
)

Argument	Description
dblTimer	Timer when started the timer using timer = StartTimer().
strUnit	Return unit must be either ms, sec, min or hour otherwise return -1

Examples

    dblTimer = TmStart()
    Application.Wait (Now() + TimeValue("0:00:02"))   ' wait for approx 2 sec
    MsgBox (TmElapsed(dblTimer, "sec"))

E.3.1.57 TmSpeedOptimize: Set some properties for the Application object to optimize excecution of vba

Usage

Sub TmSpeedOptimize()

Argument	Description
character(

Examples

    Dim dbltimer As Double
    Dim i As Integer
    Dim dblT1 As Double, dblT2 As Double
    ' Measure cpu time
    dbltimer = TmStart()
    Application.Wait (Now() + TimeValue("0:00:02"))   ' wait for approx 2 sec
    MsgBox ("Time used: " & TmElapsed(dbltimer, "sec") & " sec")
    ' Impact of disabling application updates
    Call TmSpeedOptimize
    dbltimer = TmStart()
    For i = 1 To 10000
      Cells(200, 500) = 56
    Next
    dblT1 = TmElapsed(dbltimer, "sec")
    Call TmRestoreAfterSpeedOptimize
    dbltimer = TmStart()
    For i = 1 To 10000
      Cells(200, 500) = 56
    Next
    dblT2 = TmElapsed(dbltimer, "sec")
    MsgBox ("Time without updates: " & dblT1 & vbCr & "Time with updates: " & dblT2)
    Cells(200, 500).Clear

E.3.1.58 TmRestoreAfterSpeedOptimize: Restore properties for the Application object after have called ApplicationSpeedOptimize

Usage

Sub TmRestoreAfterSpeedOptimize()

Argument	Description
character(

Examples

    Dim dbltimer As Double
    Dim i As Integer
    Dim dblT1 As Double, dblT2 As Double
    ' Measure cpu time
    dbltimer = TmStart()
    Application.Wait (Now() + TimeValue("0:00:02"))   ' wait for approx 2 sec
    MsgBox ("Time used: " & TmElapsed(dbltimer, "sec") & " sec")
    ' Impact of disabling application updates
    Call TmSpeedOptimize
    dbltimer = TmStart()
    For i = 1 To 10000
      Cells(200, 500) = 56
    Next
    dblT1 = TmElapsed(dbltimer, "sec")
    Call TmRestoreAfterSpeedOptimize
    dbltimer = TmStart()
    For i = 1 To 10000
      Cells(200, 500) = 56
    Next
    dblT2 = TmElapsed(dbltimer, "sec")
    MsgBox ("Time without updates: " & dblT1 & vbCr & "Time with updates: " & dblT2)
    Cells(200, 500).Clear
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> New names:
#> • `` -> `...1`
#> • `` -> `...2`

E.3.1.59 RandGenNormal: Generate random numbers from a normal distribution

Usage

Sub RandGenNormal(
   intSize As Integer,
   dblMean As Double,
   dblSD As Double,
   ary() As Double
)

Argument	Description
intSize	Random numbers generated
dblMean	Mean.
dblSD	Standard deviation.
ary	Array to store the values in.

Examples

    Dim ary() As Double
    Dim aryDens() As Double
    Dim intSize As Integer

    ThisWorkbook.Worksheets("TM6").Activate
    Randomize    ' initialize random-number generator
    intSize = 20 ' generate 20 numbers for each distribution
    ' Normal
    Call RandGenNormal(intSize, 100, 20, ary)
    Range("A1") = "Normal"
    Call AryPaste(ary, Range("A2"), False)
    ' Cont. uniform [10,500[
    Call RandGenUniformCont(intSize, 10, 500, ary)
    Range("B1") = "Uniform"
    Call AryPaste(ary, Range("B2"), False)
    ' Discrete uniform 10,...,500
    Call RandGenUniformDisc(intSize, 10, 500, ary)
    Range("B1") = "Uniform"
    Call AryPaste(ary, Range("B2"), False)
    ' Binomial 100 trials, pr = 0.2
    Call RandGenBinomial(intSize, 100, 0.2, ary)
    Range("C1") = "Binomial"
    Call AryPaste(ary, Range("C2"), False)
    ' Poisson lambda = 5
    Call RandGenPoisson(intSize, 5, ary)
    Range("D1") = "Poisson"
    Call AryPaste(ary, Range("D2"), False)
    ' Custom discrete
    ReDim aryDens(1 To 4, 1 To 2)
    aryDens(1, 1) = 3
    aryDens(2, 1) = 4
    aryDens(3, 1) = 5
    aryDens(4, 1) = 6
    aryDens(1, 2) = 0.1
    aryDens(2, 2) = 0.3
    aryDens(3, 2) = 0.5
    aryDens(4, 2) = 0.1
    Call RandGenDiscrete(intSize, aryDens, ary)
    Range("E1") = "Custom"
    Call AryPaste(ary, Range("E2"), False)
    Range("G3") = "Custom distribution:"
    Range("G4") = "Value"
    Range("H4") = "Prob."
    Call AryPaste(aryDens, Range("G5"))

E.3.1.60 RandInvNormal: Generate a random number from a normal distribution

Usage

Function RandInvNormal(
   dblMean As Double,
   dblSD As Double
) As Double

Argument	Description
dblMean	Mean.
dblSD	Standard deviation.

Examples

    MsgBox ("Normal: " & RandInvNormal(100, 20))

E.3.1.61 RandGenUniformCont: Generate random numbers from a continuous uniform distribution

Usage

Sub RandGenUniformCont(
   intSize As Integer,
   dblMin As Double,
   dblMax As Double,
   ary() As Double
)

Argument	Description
intSize	Random numbers generated
dblMin	Minimum number.
dblMax	Maximum number (not included).
ary	Array to store the values in.

Examples

    Dim ary() As Double
    Dim aryDens() As Double
    Dim intSize As Integer

    ThisWorkbook.Worksheets("TM6").Activate
    Randomize    ' initialize random-number generator
    intSize = 20 ' generate 20 numbers for each distribution
    ' Normal
    Call RandGenNormal(intSize, 100, 20, ary)
    Range("A1") = "Normal"
    Call AryPaste(ary, Range("A2"), False)
    ' Cont. uniform [10,500[
    Call RandGenUniformCont(intSize, 10, 500, ary)
    Range("B1") = "Uniform"
    Call AryPaste(ary, Range("B2"), False)
    ' Discrete uniform 10,...,500
    Call RandGenUniformDisc(intSize, 10, 500, ary)
    Range("B1") = "Uniform"
    Call AryPaste(ary, Range("B2"), False)
    ' Binomial 100 trials, pr = 0.2
    Call RandGenBinomial(intSize, 100, 0.2, ary)
    Range("C1") = "Binomial"
    Call AryPaste(ary, Range("C2"), False)
    ' Poisson lambda = 5
    Call RandGenPoisson(intSize, 5, ary)
    Range("D1") = "Poisson"
    Call AryPaste(ary, Range("D2"), False)
    ' Custom discrete
    ReDim aryDens(1 To 4, 1 To 2)
    aryDens(1, 1) = 3
    aryDens(2, 1) = 4
    aryDens(3, 1) = 5
    aryDens(4, 1) = 6
    aryDens(1, 2) = 0.1
    aryDens(2, 2) = 0.3
    aryDens(3, 2) = 0.5
    aryDens(4, 2) = 0.1
    Call RandGenDiscrete(intSize, aryDens, ary)
    Range("E1") = "Custom"
    Call AryPaste(ary, Range("E2"), False)
    Range("G3") = "Custom distribution:"
    Range("G4") = "Value"
    Range("H4") = "Prob."
    Call AryPaste(aryDens, Range("G5"))

E.3.1.62 RandInvUniformCont: Generate a random number from a continuous uniform distribution

Usage

Function RandInvUniformCont(
   dblMin As Double,
   dblMax As Double
) As Double

Argument	Description
dblMin	Minimum number.
dblMax	Maximum number (not included).

Examples

    ' Cont. uniform [10,500[
    MsgBox ("Uniform (continuous): " & RandInvUniformCont(10, 500))

E.3.1.63 RandGenUniformDisc: Generate random numbers from a discrete uniform distribution

Usage

Sub RandGenUniformDisc(
   intSize As Integer,
   vntMin As Variant,
   vntMax As Variant,
   ary As Variant
)

Argument	Description
intSize	Random numbers generated
vntMin	Minimum number.
vntMax	Maximum number.
ary	Array to store the values in.

Examples

    Dim ary() As Double
    Dim aryDens() As Double
    Dim intSize As Integer

    ThisWorkbook.Worksheets("TM6").Activate
    Randomize    ' initialize random-number generator
    intSize = 20 ' generate 20 numbers for each distribution
    ' Normal
    Call RandGenNormal(intSize, 100, 20, ary)
    Range("A1") = "Normal"
    Call AryPaste(ary, Range("A2"), False)
    ' Cont. uniform [10,500[
    Call RandGenUniformCont(intSize, 10, 500, ary)
    Range("B1") = "Uniform"
    Call AryPaste(ary, Range("B2"), False)
    ' Discrete uniform 10,...,500
    Call RandGenUniformDisc(intSize, 10, 500, ary)
    Range("B1") = "Uniform"
    Call AryPaste(ary, Range("B2"), False)
    ' Binomial 100 trials, pr = 0.2
    Call RandGenBinomial(intSize, 100, 0.2, ary)
    Range("C1") = "Binomial"
    Call AryPaste(ary, Range("C2"), False)
    ' Poisson lambda = 5
    Call RandGenPoisson(intSize, 5, ary)
    Range("D1") = "Poisson"
    Call AryPaste(ary, Range("D2"), False)
    ' Custom discrete
    ReDim aryDens(1 To 4, 1 To 2)
    aryDens(1, 1) = 3
    aryDens(2, 1) = 4
    aryDens(3, 1) = 5
    aryDens(4, 1) = 6
    aryDens(1, 2) = 0.1
    aryDens(2, 2) = 0.3
    aryDens(3, 2) = 0.5
    aryDens(4, 2) = 0.1
    Call RandGenDiscrete(intSize, aryDens, ary)
    Range("E1") = "Custom"
    Call AryPaste(ary, Range("E2"), False)
    Range("G3") = "Custom distribution:"
    Range("G4") = "Value"
    Range("H4") = "Prob."
    Call AryPaste(aryDens, Range("G5"))

E.3.1.64 RandInvUniformDisc: Generate a random number from a discrete uniform distribution

Usage

Function RandInvUniformDisc(
   vntMin As Variant,
   vntMax As Variant
) As Variant

Argument	Description
vntMin	Minimum number.
vntMax	Maximum number.

Examples

    ' Discrete uniform 10,...,500
    MsgBox ("Uniform (discrete): " & RandInvUniformDisc(10, 500))

E.3.1.65 RandGenBinomial: Generate random numbers from a binomial distribution

Usage

Sub RandGenBinomial(
   intSize As Integer,
   intTrials As Integer,
   dblPr As Double,
   ary() As Double
)

Argument	Description
intSize	Random numbers generated
intTrials	Number of trials.
dblPr	Probability of success.
ary	Array to store the values in.

Examples

    Dim ary() As Double
    Dim aryDens() As Double
    Dim intSize As Integer

    ThisWorkbook.Worksheets("TM6").Activate
    Randomize    ' initialize random-number generator
    intSize = 20 ' generate 20 numbers for each distribution
    ' Normal
    Call RandGenNormal(intSize, 100, 20, ary)
    Range("A1") = "Normal"
    Call AryPaste(ary, Range("A2"), False)
    ' Cont. uniform [10,500[
    Call RandGenUniformCont(intSize, 10, 500, ary)
    Range("B1") = "Uniform"
    Call AryPaste(ary, Range("B2"), False)
    ' Discrete uniform 10,...,500
    Call RandGenUniformDisc(intSize, 10, 500, ary)
    Range("B1") = "Uniform"
    Call AryPaste(ary, Range("B2"), False)
    ' Binomial 100 trials, pr = 0.2
    Call RandGenBinomial(intSize, 100, 0.2, ary)
    Range("C1") = "Binomial"
    Call AryPaste(ary, Range("C2"), False)
    ' Poisson lambda = 5
    Call RandGenPoisson(intSize, 5, ary)
    Range("D1") = "Poisson"
    Call AryPaste(ary, Range("D2"), False)
    ' Custom discrete
    ReDim aryDens(1 To 4, 1 To 2)
    aryDens(1, 1) = 3
    aryDens(2, 1) = 4
    aryDens(3, 1) = 5
    aryDens(4, 1) = 6
    aryDens(1, 2) = 0.1
    aryDens(2, 2) = 0.3
    aryDens(3, 2) = 0.5
    aryDens(4, 2) = 0.1
    Call RandGenDiscrete(intSize, aryDens, ary)
    Range("E1") = "Custom"
    Call AryPaste(ary, Range("E2"), False)
    Range("G3") = "Custom distribution:"
    Range("G4") = "Value"
    Range("H4") = "Prob."
    Call AryPaste(aryDens, Range("G5"))

E.3.1.66 RandInvBinomial: Generate a random number from a binomial distribution

Usage

Function RandInvBinomial(
   intTrials As Integer,
   dblPr As Double
) As Double

Argument	Description
intTrials	Number of trials.
dblPr	Probability of success.

Examples

    ' Binomial 100 trials, pr = 0.2
    MsgBox ("Binomial: " & RandInvBinomial(100, 0.2))

E.3.1.67 RandGenPoisson: Generate random numbers from a poisson distribution

Usage

Sub RandGenPoisson(
   intSize As Integer,
   dblLambda As Double,
   ary() As Double
)

Argument	Description
intSize	Random numbers generated
dblLambda	Mean.
ary	Array to store the values in.

Algorithm suggested by D. Knuth.

Examples

    Dim ary() As Double
    Dim aryDens() As Double
    Dim intSize As Integer

    ThisWorkbook.Worksheets("TM6").Activate
    Randomize    ' initialize random-number generator
    intSize = 20 ' generate 20 numbers for each distribution
    ' Normal
    Call RandGenNormal(intSize, 100, 20, ary)
    Range("A1") = "Normal"
    Call AryPaste(ary, Range("A2"), False)
    ' Cont. uniform [10,500[
    Call RandGenUniformCont(intSize, 10, 500, ary)
    Range("B1") = "Uniform"
    Call AryPaste(ary, Range("B2"), False)
    ' Discrete uniform 10,...,500
    Call RandGenUniformDisc(intSize, 10, 500, ary)
    Range("B1") = "Uniform"
    Call AryPaste(ary, Range("B2"), False)
    ' Binomial 100 trials, pr = 0.2
    Call RandGenBinomial(intSize, 100, 0.2, ary)
    Range("C1") = "Binomial"
    Call AryPaste(ary, Range("C2"), False)
    ' Poisson lambda = 5
    Call RandGenPoisson(intSize, 5, ary)
    Range("D1") = "Poisson"
    Call AryPaste(ary, Range("D2"), False)
    ' Custom discrete
    ReDim aryDens(1 To 4, 1 To 2)
    aryDens(1, 1) = 3
    aryDens(2, 1) = 4
    aryDens(3, 1) = 5
    aryDens(4, 1) = 6
    aryDens(1, 2) = 0.1
    aryDens(2, 2) = 0.3
    aryDens(3, 2) = 0.5
    aryDens(4, 2) = 0.1
    Call RandGenDiscrete(intSize, aryDens, ary)
    Range("E1") = "Custom"
    Call AryPaste(ary, Range("E2"), False)
    Range("G3") = "Custom distribution:"
    Range("G4") = "Value"
    Range("H4") = "Prob."
    Call AryPaste(aryDens, Range("G5"))

E.3.1.68 RandInvPoisson: Generate a random number from a Poisson distribution

Usage

Function RandInvPoisson(
   dblLambda As Double
) As Long

Argument	Description
dblLambda	Mean.

Algorithm suggested by D. Knuth.

Examples

    ' Poisson lambda = 5
    MsgBox ("Poisson: " & RandInvPoisson(5))

E.3.1.69 RandGenDiscrete: Generate random numbers from a custom discrete distribution

Usage

Sub RandGenDiscrete(
   intSize As Integer,
   dblDens As Variant,
   ary As Variant
)

Argument	Description
intSize	Random numbers generated
dblDens	The probability density. First column contains the outcome and the second the probability.
ary	Array to store the values in.

Assume that the second column in aryDens sums to one.

Examples

    Dim ary() As Double
    Dim aryDens() As Double
    Dim intSize As Integer

    ThisWorkbook.Worksheets("TM6").Activate
    Randomize    ' initialize random-number generator
    intSize = 20 ' generate 20 numbers for each distribution
    ' Normal
    Call RandGenNormal(intSize, 100, 20, ary)
    Range("A1") = "Normal"
    Call AryPaste(ary, Range("A2"), False)
    ' Cont. uniform [10,500[
    Call RandGenUniformCont(intSize, 10, 500, ary)
    Range("B1") = "Uniform"
    Call AryPaste(ary, Range("B2"), False)
    ' Discrete uniform 10,...,500
    Call RandGenUniformDisc(intSize, 10, 500, ary)
    Range("B1") = "Uniform"
    Call AryPaste(ary, Range("B2"), False)
    ' Binomial 100 trials, pr = 0.2
    Call RandGenBinomial(intSize, 100, 0.2, ary)
    Range("C1") = "Binomial"
    Call AryPaste(ary, Range("C2"), False)
    ' Poisson lambda = 5
    Call RandGenPoisson(intSize, 5, ary)
    Range("D1") = "Poisson"
    Call AryPaste(ary, Range("D2"), False)
    ' Custom discrete
    ReDim aryDens(1 To 4, 1 To 2)
    aryDens(1, 1) = 3
    aryDens(2, 1) = 4
    aryDens(3, 1) = 5
    aryDens(4, 1) = 6
    aryDens(1, 2) = 0.1
    aryDens(2, 2) = 0.3
    aryDens(3, 2) = 0.5
    aryDens(4, 2) = 0.1
    Call RandGenDiscrete(intSize, aryDens, ary)
    Range("E1") = "Custom"
    Call AryPaste(ary, Range("E2"), False)
    Range("G3") = "Custom distribution:"
    Range("G4") = "Value"
    Range("H4") = "Prob."
    Call AryPaste(aryDens, Range("G5"))

E.3.1.70 RandInvDiscrete: Generate a random number from a custom discrete distribution

Usage

Function RandInvDiscrete(
   aryDens As Variant
) As Variant

Argument	Description
aryDens	The probability density. First column contains the outcome and the second the probability.

Assume that the second column in aryDens sums to one.

Examples

    Dim aryDens() As Double
    ' Custom discrete
    ReDim aryDens(1 To 4, 1 To 2)
    aryDens(1, 1) = 3
    aryDens(2, 1) = 4
    aryDens(3, 1) = 5
    aryDens(4, 1) = 6
    aryDens(1, 2) = 0.1
    aryDens(2, 2) = 0.3
    aryDens(3, 2) = 0.5
    aryDens(4, 2) = 0.1
    MsgBox ("Custom (discrete): " & RandInvDiscrete(aryDens))

References

Wøhlk, S. 2010. VBA Programming in Business Economics. DJØF Publishing.