VBA Code Library

(1)

http://www.vbusers.com/code/codetoc.asp http://www.vbusers.com/downloads/download.asp http://www.freevbcode.com/ShowCode.asp?ID=329 http://allenbrowne.com/funcindex.html http://www.ozgrid.com/VBA/ http://www.mrexcel.com/forum/excel-questions/540096-use-visual-basic-applications-code-create-excel-drop-down-list.html http://www.mrexcel.com/forum/excel-questions/643359-visual-basic-applications-code-sort-list-then-delete-simliar-rows-only-one-column.html http://www.mrexcel.com/forum/excel-questions/649127-visual-basic-applications-using-block-code-filtered-list.html http://msdn.microsoft.com/en-us/library/aa984063(v=vs.71).aspx http://www.mrexcel.com/forum/excel-questions/540096-use-visual-basic-applications-code-create-excel-drop-down-list.html http://www.mrexcel.com/forum/excel-questions/649127-visual-basic-applications-using-block-code-filtered-list.html http://www.ozgrid.com/VBA/ http://www.mrexcel.com/articles.shtml http://www.mrexcel.com/articles.shtml#Formulas vip http://allenbrowne.com/funcindex.html http://www.mrexcel.com/articles.shtml#VBA http://www.mrexcel.com/forum/excel-questions/649127-visual-basic-applications-using-block-code-filtered-list.html http://www.mrexcel.com/forum/excel-questions/614216-visual-basic-applications-code-extract-list-unique-values-database-field.html http://www.mrexcel.com/forum/excel-questions/641873-visual-basic-applications-reorder-columns-list-view.html http://www.mrexcel.com/forum/excel-questions/43009-visual-basic-applications-code-autofilter.html

(2)

http://www.vbusers.com/code/codetoc.asp?Search=&Type=0&MaxThread=395&MaxPost=1 http://www.mrexcel.com/forum/excel-questions/540096-use-visual-basic-applications-code-create-excel-drop-down-list.html http://www.mrexcel.com/forum/excel-questions/643359-visual-basic-applications-code-sort-list-then-delete-simliar-rows-only-one-column.html http://www.mrexcel.com/forum/excel-questions/649127-visual-basic-applications-using-block-code-filtered-list.html http://www.mrexcel.com/forum/excel-questions/540096-use-visual-basic-applications-code-create-excel-drop-down-list.html http://www.mrexcel.com/forum/excel-questions/649127-visual-basic-applications-using-block-code-filtered-list.html http://www.mrexcel.com/forum/excel-questions/649127-visual-basic-applications-using-block-code-filtered-list.html http://www.mrexcel.com/forum/excel-questions/614216-visual-basic-applications-code-extract-list-unique-values-database-field.html http://www.mrexcel.com/forum/excel-questions/641873-visual-basic-applications-reorder-columns-list-view.html http://www.mrexcel.com/forum/excel-questions/43009-visual-basic-applications-code-autofilter.html

(3)

Returning a distinct list of items from an Excel Range

Below is code to return a distinct list of items from an excel range.

Option Explicit

'Purpose : Returns a list of distinct items from a range.

'Inputs : rngReturnDistinctListFrom. The range to return the distinct list from ' rngItemsAlreadyReturned. The range containing the values already returned.

'Outputs : Returns the first distinct item which has not already been returned in the rngItemsAlreadyReturned range. 'Author : Andrew Baker

'Date : 25/03/2000 'Example usuage: '

'=FirstDistinctItem(A1:A1000,B$1:B1) 'Where:

'A1:A1000 is a list containing the items to return a distinct list of 'B$1:B1 is the list containing the distinct items already returned.

Function FirstDistinctItem(rngReturnDistinctListFrom As Excel.Range, rngItemsAlreadyReturned As Excel.Range, Optional bIgnoreBlanks As Boolean = True) As Variant Dim avItemsAlreadyReturned As Variant

Dim vCell As Variant

FirstDistinctItem = ""

avItemsAlreadyReturned = rngItemsAlreadyReturned.Value

For Each vCell In rngReturnDistinctListFrom

If (bIgnoreBlanks = False Or (bIgnoreBlanks And vCell <> "")) = True Then If (ArrayHasItem(avItemsAlreadyReturned, vCell) = False) Then FirstDistinctItem = vCell Exit Function End If End If Next End Function

'Purpose : Checks to see if a value is already in an array. 'Inputs : avInArray. The array to evaluate.

' vValueToCheck. The value to look for in the array. 'Outputs : True. The array contains vValueToCheck ' False. The array does not contain vValueToCheck. 'Author : Andrew Baker

'Date : 25/03/2000

(4)

Function ArrayHasItem(avInArray As Variant, vValueToCheck As Variant) As Boolean Dim vThisItem As Variant, lThisRow As Long

On Error GoTo ErrExit

If ArrayNumDimensions(avInArray) = 1 Then 'Faster than for each loop

For lThisRow = LBound(avInArray) To UBound(avInArray) vThisItem = avInArray(lThisRow)

'Check for arrays in arrays If IsArray(vThisItem) Then 'Search an array in an array

If ArrayHasItem(vThisItem, vValueToCheck) Then 'Found item

ArrayHasItem = True Exit For

End If

ElseIf vThisItem = vValueToCheck Then ArrayHasItem = True

Exit For End If Next Else

For Each vThisItem In avInArray 'Check for arrays in arrays If IsArray(vThisItem) Then 'Search an array in an array

If ArrayHasItem(vThisItem, vValueToCheck) Then 'Found item

ArrayHasItem = True Exit For

End If

ElseIf vThisItem = vValueToCheck Then ArrayHasItem = True Exit For End If Next End If ErrExit: On Error GoTo 0 End Function

'Purpose : Calculates the number of dimensions in an array 'Inputs : avInArray. The array to evaluate.

(5)

'Outputs : The number of dimensions the array has. 'Author : Andrew Baker

'Date : 25/03/2000 'Notes :

Function ArrayNumDimensions(avInArray As Variant) As Long Dim lNumDims As Long

If IsArray(avInArray) Then On Error GoTo ExitSub Do

lNumDims = UBound(avInArray, ArrayNumDimensions + 1) ArrayNumDimensions = ArrayNumDimensions + 1 Loop End If ExitSub: On Error GoTo 0 End Function

(6)

Returning a distinct list of items from an Excel Range

'Outputs : Returns the first distinct item which has not already been returned in the rngItemsAlreadyReturned range.

(7)

How to use SHOWIF and SHOWUNLESS functions in Excel http://www.blog.methodsinexcel.co.uk/category/vba/

This post is by guest blogger Yoav Eze, his bio can be fond at the bottom of the post. Thanks Yoav!

It’s always nice to get content from other Excel developers, so if you have an interesting story, please feel free to share it here!!! Get in touch: rossmcleanatgoogledotcom That now makes a list of 2 guest bloggers on MIE in 5 years, Sam and Yoav, I’m hoping to grow that list!!!

Now, over to Yoav.

Have you ever in your spreadsheets wanted to show a value only under certain conditions?

We’ve created two functions show-functions (DOWNLAOD THE WORKBOOK HERE) that will do exactly this. The first accepts a value (which can be a simple value, a cell reference, or a calculation) and a condition as a string (like the MATCH function). It then evaluates the condition on the value. If the condition is true, the value is returned, otherwise an empty string is returned. You can also specify a different return value if the condition is not met by setting a third parameter. The second function does exactly the same, except that it will return the value if the condition is NOT met, and an empty string (or the third parameter) if the condition IS met.

Here’s an example of how you might use these functions. This is a formula used in one of our spreadsheets: =IF((SUMIFS(Payments!$D$2:$D$1000,Payments!$C$2:$C$1000,"="&B$1, OFFSET(Payments!$D$2:$D$1000,0,MATCH($A3,Payments!$E$1:$Z$1,0)),"x") SUMIFS(Payments!$D$2:$D$1000,Payments!$C$2:$C$1000,"="&$A3, OFFSET(Payments!$D$2:$D$1000,0,MATCH(B$1,Payments!$E$1:$Z$1,0)),"x"))<=0, "", SUMIFS(Payments!$D$2:$D$1000,Payments!$C$2:$C$1000,"="&B$1, OFFSET(Payments!$D$2:$D$1000,0,MATCH($A3,Payments!$E$1:$Z$1,0)),"x") -SUMIFS(Payments!$D$2:$D$1000,Payments!$C$2:$C$1000,"="&$A3, OFFSET(Payments!$D$2:$D$1000,0,MATCH(B$1,Payments!$E$1:$Z$1,0)),"x"))

Looking closely, it’s possible to see that we have the same formula appear twice; once it is checked to see if it’s less then or equal to zero, and again as the result. Now, the same formula using SHOWIF:

=SHOWIF(SUMIFS(Payments!$D$2:$D$1000,Payments!$C$2:$C$1000,"="&B$1, OFFSET(Payments!$D$2:$D$1000,0,MATCH($A3,Payments!$E$1:$Z$1,0)),"x") SUMIFS(Payments!$D$2:$D$1000,Payments!$C$2:$C$1000,"="&$A3,

OFFSET(Payments!$D$2:$D$1000,0,MATCH(B$1,Payments!$E$1:$Z$1,0)),"x"), "<=0") That’s MUCH shorter, and probably much easier to understand.

(8)

Here are the two functions: ? 1 ' This function will display the value if it matches the condition; 2 ' otherwise, it will display an empty cell, or 3 ' the third (optional) parameter 4 Function SHOWIF( value As Variant, condition As String, _ 5 Optional nonvalue As String = "") As Variant 6 If (Not IsError(val ue) And Applicatio n.Evaluate (CStr(valu e) & condition)) Then 7 SHOWIF = value

(9)

8 Else 9 SHOWIF = nonvalue 10 End If 11 End Function 12 ' This function will display the value if it does not matches the condition; 13 ' otherwise, it will display an empty cell, or' the third (optional) parameter 14 Function SHOWUN LESS(valu e As Variant, condition As String, _ 15 Optional nonvalue As String = "") As Variant

(10)

16 If (IsError(va lue) Or Applicatio n.Evaluate (CStr(valu e) & condition)) Then 17 SHOWUN LESS = value 18 Else 19 SHOWUN LESS = nonvalue 20 End If 21 End Function

To use them, just put in the formula =SHOWIF(value, condition) or =SHOWUNLESS(value, condition)

Grab the file SHOWFunctions.xla which is an Excel Add-in file containing both user-defined functions. It’s packaged as an Excel Add-in so you can easily add it to Excel without having to add the functions to each workbook.

To use it, save it as a file, then open Excel, and add it to the Add-in list. In Excel prior to 2007, you can do this using the Add-in command in the Tools menu (select ‘Browse’ from the dialog that opens and select the saved file to use it). In Excel 2007 and 2010, go to the Add-ins page in the Excel Options dialog, select ‘Excel Add-ins’ in the Manage box and press the Go button. Then use the browse button the same as in the previous versions. Summary

These functions should make your spreadsheet code much easier to follow and therefore easier to debug. We hope you get a lot of use out of them! Perhaps you could improve or extend our ideas? Please let us know… About the author

Yoav Ezer co-authors the technology and productivity blog Codswallop. He is also the CEO of a company that produces PDF to XLS conversion software. For more Excel tips from Yoav, join him on Facebook or Twitter

(11)

It’s always nice to get content from other Excel developers, so if you have an interesting story, please feel free to share it here!!! Get in touch: rossmcleanatgoogledotcom That now makes a list of 2 guest bloggers on MIE in 5 years, Sam and Yoav, I’m hoping to grow that list!!!

We’ve created two functions show-functions (DOWNLAOD THE WORKBOOK HERE) that will do exactly this. The first accepts a value (which can be a simple value, a cell reference, or a calculation) and a condition as a string (like the MATCH function). It then evaluates the condition on the value. If the condition is true, the value is returned, otherwise an empty string is returned. You can also specify a different return value if the condition is not met by setting a third parameter. The second function does exactly the same, except that it will return the value if the condition is NOT met, and an empty string (or the third parameter) if the condition IS met.

Here’s an example of how you might use these functions. This is a formula used in one of our spreadsheets:

(12)

(13)

(14)

To use it, save it as a file, then open Excel, and add it to the Add-in list. In Excel prior to 2007, you can do this using the Add-in command in the Tools menu (select ‘Browse’ from the dialog that opens and select the saved file to use it). In Excel 2007 and 2010, go to the Add-ins page in the Excel Options dialog, select ‘Excel Add-ins’ in the Manage box and press the Go button. Then use the browse button the same as in the previous versions.

These functions should make your spreadsheet code much easier to follow and therefore easier to debug. We hope you get a lot of use out of them! Perhaps you could improve or extend our ideas? Please let us know…

(15)

(16)

(17)

(18)

To use it, save it as a file, then open Excel, and add it to the Add-in list. In Excel prior to 2007, you can do this using the Add-in command in the Tools menu (select ‘Browse’ from the dialog that opens and select the saved file to use it). In Excel 2007 and 2010, go to the Add-ins page in the Excel Options dialog, select ‘Excel Add-ins’ in the Manage box and press the Go button. Then use the browse button the same as in the previous versions.

(19)

(20)

(21)

(22)

(23)

(24)

(25)

(26)

(27)

Returning the next blank/empty cell in a range

The code below will return the next blank cell given a reference range and a specified direction (eg. The next blank cell below this cell).

'Purpose : Returns the next blank in a given direction, at the specified offset. 'Inputs : rngReference The reference range.

' direction The direction to find the next blank cell in.

' [lOffset] The offset from the last used cell in the specified direction. 'Outputs : Returns the next blank in a given direction, at the specified offset. 'Author : Andrew Baker

'Date : 13/11/2000 10:14 'Notes :

'Revisions :

Function GetNextBlankCell(rngReference As Excel.range, direction As XlDirection, Optional lOffset As Long = 1) Dim result As Excel.range

On Error GoTo ErrFailed

Set result = rngReference.End(direction) Select Case direction

Case XlDirection.xlDown

Set result = result.Offset(lOffset, 0) Case XlDirection.xlToLeft

Set result = result.Offset(0, -lOffset) Case XlDirection.xlToRight

Set result = result.Offset(0, lOffset) Case XlDirection.xlUp

Set result = result.Offset(-lOffset, 0) End Select

Set GetNextBlankCell = result Exit Function

ErrFailed:

Debug.Print "Error in GetNextBlankCell. " & Err.Description Set GetNextBlankCell = Nothing

End Function

'Demonstration code Sub Test()

Dim rngNextCell As Excel.range

Set rngNextCell = GetNextBlankCell(range("A1"), xlToLeft) If (rngNextCell Is Nothing) = False Then

Debug.Print rngNextCell.address End If

(28)

Returning the next blank/empty cell in a range

The code below will return the next blank cell given a reference range and a specified direction (eg. The next blank cell below this cell).

(29)

Removing all the non numeric (alpha) characters from a string

The following function can be used to remove all the non numeric (or alpha characters) from a string.

'Purpose : Removes all non numeric characters from a string.

'Inputs : sNumberToClean The number to remove non numeric characters from. 'Outputs : Returns the input string with the non numeric characters removed.

'Author : Andrew Baker 'Date : 13/11/2000 10:14 'Notes :

'Revisions :

Public Function RemoveNonNumeric(sNumberToClean As String) As String Const NUMERIC_CHARS = "0123456789.,"

Dim lThisChar As Long Dim sResult As String

For lThisChar = 1 To Len(sNumberToClean)

If InStr(1, NUMERIC_CHARS, Mid$(sNumberToClean, lThisChar, 1)) > 0 Then 'Found a numeric character

sResult = sResult + Mid$(sNumberToClean, lThisChar, 1) End If

RemoveNonNumeric = sResult End Function

(30)

(31)

Ensuring a string always starts or ends with specific characters

Below are two simple routines that ensure a string always starts with or ends with some particular text.

'Purpose : Ensures the specified string always starts with a specific text. 'Inputs : sText The text to check the starting characters of.

' sStartWith The text to ensure is a the beginning of sText parameter. ' [bIgnoreCase] If true performs a case insensitive comparison, else does not. 'Outputs : The specified string, starting with the sStartWith parameter.

'Author : Andrew Baker 'Date : 25/03/2005 'Notes :

Public Function StringStartWith(ByRef sText, ByRef sStartWith As String, Optional bIgnoreCase As Boolean = True) As String Dim bPrepend As Boolean

If Len(sStartWith) Then If bIgnoreCase Then

If UCase$(Left$(sText, Len(sStartWith))) <> UCase$(sStartWith) Then 'String doesn't start with specified text

bPrepend = True End If

Else

If Left$(sText, Len(sStartWith)) <> sStartWith Then 'String doesn't start with specified text

End If

If bPrepend Then 'Add the text

StringStartWith = sStartWith & sText Else

'Return text unmodified StringStartWith = sText End If Else StringStartWith = sText End If End Function

'Purpose : Ensures the specified string always ends with a specific text. 'Inputs : sText The text to check the end characters of.

' sEndWith The text to ensure is a the end of sText parameter.

' [bIgnoreCase] If true performs a case insensitive comparison, else does not. 'Outputs : The specified string, ending with the sEndWith parameter.

(32)

Public Function StringEndWith(ByRef sText, ByRef sEndWith As String, Optional bIgnoreCase As Boolean = True) As String Dim bPrepend As Boolean

If Len(sEndWith) Then If bIgnoreCase Then

If UCase$(Right$(sText, Len(sEndWith))) <> UCase$(sEndWith) Then 'String doesn't start with specified text

Else

If Right$(sText, Len(sEndWith)) <> sEndWith Then 'String doesn't start with specified text

End If

If bPrepend Then 'Add the text

StringEndWith = sText & sEndWith Else

'Return text unmodified StringEndWith = sText End If Else StringEndWith = sText End If End Function 'Demo rountine Sub Test()

Dim sPath As String

sPath = "C:\Program Files\"

'First call will append the specified text sPath = StringEndWith(sPath, "My Path\") Debug.Print sPath

'Second call will do nothing

sPath = StringEndWith(sPath, "My Path\") Debug.Print sPath

(33)

Ensuring a string always starts or ends with specific characters

Below are two simple routines that ensure a string always starts with or ends with some particular text.

(34)

(35)

Joining or Concatenation two paths together

Below is a simple routine which joins two paths together to form a fully qualified path (eg "C:\Program Files" and "My Application" becomes "C:\Program Files\My Application\"). A demonstration routine can be found at the bottom of this post.

'Purpose : Appends or concatenates two paths together. 'Inputs : sPath1 The base directory or path.

' sPath2 The directory (or file) to append to the base directory.

' [bAppendTrailingSlash] True to append a trailing backslash (if sPath2 is a file name then use false). 'Outputs : Returns the paths combined, with trailing slash.

Public Function PathConcatenate(sPath1 As String, sPath2 As String, Optional bAppendTrailingSlash As Boolean = True) As String Dim sPathSepChar As String

'Determine if we are using forward or back slashes If InStr(sPath1 & sPath2, "/") > 0 Then

sPathSepChar = "/" Else sPathSepChar = "\" End If If Len(sPath1) = 0 Then PathConcatenate = sPath2 ElseIf Len(sPath2) = 0 Then PathConcatenate = sPath1 Else

If Right$(sPath1, 1) <> sPathSepChar Then sPath1 = sPath1 & sPathSepChar End If

If Left$(sPath2, 1) = sPathSepChar Then sPath2 = Mid(sPath2, 2)

End If

'Return the path

PathConcatenate = sPath1 & sPath2 End If

If bAppendTrailingSlash Then 'Append trailing slash

If Right$(PathConcatenate, 1) <> sPathSepChar Then PathConcatenate = PathConcatenate & sPathSepChar End If

(36)

End If End Function

'Demonstration routine Sub Test()

Dim sPath1 As String, sPath2 As String sPath1 = "C:\"

sPath2 = "MyFolder\SubDir1"

Debug.Print "Appending folders: " & PathConcatenate(sPath1, sPath2)

sPath1 = "http://www.vbusers.com" sPath2 = "code/codetoc.asp"

Debug.Print "Appending web paths: " & PathConcatenate(sPath1, sPath2) End Sub

(37)

Below is a simple routine which joins two paths together to form a fully qualified path (eg "C:\Program Files" and "My Application" becomes "C:\Program Files\My Application\"). A demonstration routine can be found at the bottom of this post.

' [bAppendTrailingSlash] True to append a trailing backslash (if sPath2 is a file name then use false).

(38)

(39)

(40)

Determining the amount of time the system has been idle

The function below can be used to return the amount of time (ms) the system (or computer) has been idle (ie, has not received any mouse or keyboard input). This can be useful for closing database connections or other such clean up tasks. Option Explicit

Private Type PLASTINPUTINFO cbSize As Long

dwTime As Long End Type

Private Declare Function GetLastInputInfo Lib "user32.dll" (ByRef plii As PLASTINPUTINFO) As Long Private Declare Function GetTickCount Lib "kernel32" () As Long

'Purpose : Returns the number of milliseconds the system (or computer) has been idle for. 'Inputs : N/A

'Outputs : Returns the number of milliseconds the system has been idle for. 'Date : 25/03/2005

'Author : Andrew Baker(www.vbusers.com) 'Notes :

Public Function SystemIdleTime() As Long Dim tLastInput As PLASTINPUTINFO tLastInput.cbSize = Len(tLastInput)

Call GetLastInputInfo(tLastInput)

SystemIdleTime = GetTickCount - tLastInput.dwTime End Function

'Test (for VBA) Sub Test()

Application.OnTime DateAdd("s", 5, Now), "CheckIdle" End Sub

Sub CheckIdle()

Debug.Print "The system has been idle for " & SystemIdleTime & " ms" End Sub

(41)

Determining the amount of time the system has been idle

(42)

(43)

Removing duplicate values from a delimited string

Below is a function and demonstration source code that shows how to remove duplicate values from a delimited string. 'Purpose : Removes duplicate values from a delimited string.

'Inputs : sDelimString The delimited string to remove the duplicate values from. ' sDelimiter The string delimiter.

' [eCompare] The method of comparison.

'Outputs : Returns the input string "sDelimString" with any duplicate values removed. 'Author : Andrew Baker

'Date : 13/Jul/2001 'Notes :

Function RemoveDuplicates(sDelimString As String, sDelimiter As String, Optional eCompare As VbCompareMethod = vbTextCompare) As String Dim asValues() As String

Dim lThisValue As Long Dim sExistingValues As String Dim bEndDelim As Boolean

If Len(sDelimString) Then

'Check if there is an end delimiter

bEndDelim = Right$(sDelimString, Len(sDelimiter)) = sDelimiter 'Split the string into an array

asValues = Split(sDelimString, sDelimiter) 'Loop over the items in the string

For lThisValue = 0 To UBound(asValues) 'Check if the item is already in the string

If InStr(1, RemoveDuplicates, asValues(lThisValue) & sDelimiter, eCompare) = 0 Then 'The value is unique, add it to the results

RemoveDuplicates = RemoveDuplicates & asValues(lThisValue) & sDelimiter End If

RemoveDuplicates = Left(RemoveDuplicates, Len(RemoveDuplicates) - Len(sDelimiter)) End If

End If End Function

Dim sTest As String

sTest = "andrew,baker,is,ace,andrew,IS,"

sTest = RemoveDuplicates(sTest, ",", vbTextCompare) Debug.Print sTest

(44)

(45)

Removing duplicate values from a delimited string

Below is a function and demonstration source code that shows how to remove duplicate values from a delimited string.

(46)

Adding a command bar button that calls a parameterised function

The OnAction property of an Excel CommandbarButton actually allows you to specify a lot more than just the macro to call. Below is a function which creates a CommandbarButton that can call a parametised function in a specified workbook.

Option Explicit

'Purpose : Adds and returns a command bar button (that can call a parameterised macro) to an existing commandbar. 'Inputs : oParentCommandBar The command bar to add the button to.

' oMacroWrk The workbook containing the macro to call from the button click. ' sMacroName The name of the macro to call.

' sToolTip The tooltip of the button. ' lFaceID The faceID of the button icon.

' [args] A list of the arguments to pass to the sMacroName function 'Outputs : N/A

'Example :

' 'Setup command bar

' Set oCmdBar = CommandBars.Add ' 'Add show this file history

' Call CommandBarAdd(oCmdBar, ThisWorkbook, "TestClick", "Show A File", 2520, "The name of the file", "Another Parameter") ' 'Header of macro called looks like this

' Sub TestClick(Optional sParam1 As String, Optional sParam2 As String) ' ...

' ...

Function CommandBarAdd(oParentCommandBar As CommandBar, oMacroWrk As Excel.Workbook, sMacroName As String, sToolTip As String, lFaceID As Long, ParamArray args() As Variant) As CommandBarButton Dim oBtn As CommandBarButton

Dim sOnAction As String Dim vArg As Variant Dim sSeperator As String

Set oBtn = oParentCommandBar.Controls.Add oBtn.FaceId = lFaceID

oBtn.TooltipText = sToolTip

sOnAction = "'" & oMacroWrk.Name & "'!'" & sMacroName If IsArray(args) Then

For Each vArg In args

sOnAction = sOnAction & sSeperator & """" & CStr(vArg) & """" sSeperator = ","

Next End If

(47)

oBtn.OnAction = sOnAction End Function

'Demo code Sub Test()

Dim oCmdBar As CommandBar 'Setup command bar

Set oCmdBar = CommandBars.Add 'Add show this file history

CommandBarAdd oCmdBar, ThisWorkbook, "TestClick", "Show A File", 2520, "Value1", "Value2" 'Add a name here

'oCmdBar.Name = "Test Bar 1" oCmdBar.Visible = True End Sub

'Routine called by click

Sub TestClick(Optional sParam1 As String, Optional sParam2 As String) MsgBox "Param1: " & sParam2 & ". Param2: " & sParam1

(48)

Adding a command bar button that calls a parameterised function

'Purpose : Adds and returns a command bar button (that can call a parameterised macro) to an existing commandbar.

' Call CommandBarAdd(oCmdBar, ThisWorkbook, "TestClick", "Show A File", 2520, "The name of the file", "Another Parameter")

(49)

(50)

(51)

Transposing a 2d array

The code below can be used to transpose (or invert) a 2d variant array. Eg a 2d array with 2 columns and 6 rows, would become a 2d array with 6 columns and 2 rows: 'Purpose : Transposes a 2D array

'Inputs : avValues The array to transpose.

'Outputs : Returns the array transposed, or empty if an error occurs 'Author : Andrew Baker

'Date : 25/03/2000 'Notes :

Function Array2DTranspose(avValues As Variant) As Variant Dim lThisCol As Long, lThisRow As Long

Dim lUb2 As Long, lLb2 As Long Dim lUb1 As Long, lLb1 As Long Dim avTransposed As Variant

If IsArray(avValues) Then On Error GoTo ErrFailed lUb2 = UBound(avValues, 2) lLb2 = LBound(avValues, 2) lUb1 = UBound(avValues, 1) lLb1 = LBound(avValues, 1)

ReDim avTransposed(lLb2 To lUb2, lLb1 To lUb1) For lThisCol = lLb1 To lUb1

For lThisRow = lLb2 To lUb2

avTransposed(lThisRow, lThisCol) = avValues(lThisCol, lThisRow) Next Next End If Array2DTranspose = avTransposed Exit Function ErrFailed: Debug.Print err.description Debug.Assert False Array2DTranspose = Empty Exit Function Resume End Function

(52)

(53)

Performing a custom Trim() on an input string

The RTrim, LTrim and Trim functions are useful from removing blank spaces. However, sometimes you want to remove different trailing/leading characters (eg, Remove all the vbNewLine from the start and end of a string). The function below allows you to specify a custom Trim:

'Purpose : Trims trailing a leading characters from a string buffer 'Inputs : sValue The string to trim

' sRemove The character/s to remove from the start/end of the sValue ' [bRtrim] If True removes any matching right hand side chars

' [bLtrim] If True removes any matching left hand side chars ' [eCompare] The method of comparison

'Outputs : Returns the string trimed from the specified characters 'Author : Andrew Baker

'Date : 17/01/2001 12:37

'Notes : Trim2("aaaaaBBBBCCCaaaa", "a") returns "BBBBCCC" 'Example :

'Assumptions :

Function Trim2(ByVal sValue As String, sRemove As String, Optional bRtrim As Boolean = True, Optional bLTrim As Boolean = True, Optional eCompare As VbCompareMethod = vbBinaryCompare) As String Dim lPos As Long, lLastChar As Long, lLenRemove As Long

lLenRemove = Len(sRemove)

If lLenRemove > 0 And Len(sValue) > 0 Then If bLTrim Then

'Remove the left hand chars lPos = 1 - lLenRemove lLastChar = 1 - lLenRemove

'Loop finding the chars to replace Do

lPos = InStr(lPos + lLenRemove, sValue, sRemove, eCompare) If lPos = lLastChar + lLenRemove Then

lLastChar = lPos Else

'Found all the matching characters Exit Do

End If Loop

If lLastChar Then

sValue = Mid$(sValue, lLastChar + lLenRemove) End If

(54)

If bRtrim = True And Len(sValue) > 0 Then 'Remove the right hand chars

lPos = Len(sValue) + 1 lLastChar = lPos

'Loop finding the chars to replace Do

lPos = InStrRev(sValue, sRemove, lPos - 1, eCompare) If lPos = lLastChar - lLenRemove Then

lLastChar = lPos Else

'Found all the matching characters Exit Do

End If Loop

If lLastChar Then

sValue = Left$(sValue, lLastChar - 1) End If End If End If Trim2 = sValue Exit Function ErrFailed:

Debug.Print "Error in Trim2: " & Err.Description Debug.Assert False

(55)

Performing a custom Trim() on an input string

The RTrim, LTrim and Trim functions are useful from removing blank spaces. However, sometimes you want to remove different trailing/leading characters (eg, Remove all the vbNewLine from the start and end of a string). The function below allows you to specify a custom Trim:

(56)

(57)

(58)

Using the Like comparison with Escaped characters

Occasionally, you may need to perform a like comparison on a reserved character (eg "[" or "%"). To do this you must specify and "Escape" character. This means that everytime you use this character, the following character will not be converted as a regular expression. See below for an example. --This bizarre example is searching for the first 100 people who name starts with "Andrew %Bake"

select top 100 * from UserLogins

(59)

Using the Like comparison with Escaped characters

(60)

(61)

Reading the contents of an Excel Range

The following code can be used to quickly extact the values of an Excel Range into an array.

'Purpose : Reads the values of a range into an array (much quicker than looping through a range) 'Inputs : rngInput The range to extract the values from.

' avValues See outputs. 'Outputs : Returns the True on success.

' avValues An 2d array containing the values in the range. 'Author : Andrew Baker

'Date : 31/12/2000 13:51 'Notes :

'Revisions :

'Example : Call RangeToArray(Worksheets(1).Range("A1:K1000"), avValues) Function RangeToArray(rngInput As Object, avValues As Variant) As Boolean On Error GoTo ErrFailed

avValues = Empty avValues = rngInput.Value RangeToArray = True Exit Function ErrFailed: 'Failed

Debug.Print "Error in RangeToArray: " & Err.Description Debug.Assert False

RangeToArray = False On Error GoTo 0 End Function

(62)

Safely checking the bounds of an array

Using the Ubound and Lbound functions on an uninitialised typed array causes a "Subscript out of range" error. Use the following simple functions to safely check the bounds of any variant datatype. 'Purpose : Replacement function to UBound. Returns Upper bound of an array.

'Inputs : avValues The array to determine the upper bound of ' lDimension The dimension of the array

' [lDefault] The value to return if an error occurs 'Outputs : The upper bound specific dimension of avValues 'Author : Andrew Baker

'Date : 23/07/2000 19:07

'Notes : This function uses UBound with an error handler and allows you to specify a default return value 'Revisions :

Function UUBound(avValues As Variant, Optional lDimension As Long = 1, Optional lDefault As Long = -1) As Long

UUBound = UBound(avValues, lDimension) Exit Function

ErrFailed:

'Error Occurred, Return the Default and clear error UUBound = lDefault

On Error GoTo 0 End Function

'Purpose : Replacement function for LBound (has extra functionality and error handling) 'Inputs : avValues The array to find the LBound.

' lDimension Which dimension's lower bound to return ' [lDefault] The value to return if an error occurs

'Outputs : The lower bound of the array, or lDefault if an error occurs 'Author : Andrew Baker

'Date : 25/03/2000

'Notes : LBound raises and error if you try and check the the lower bound ' of a dimension that doesn't exist.

Function LLBound(avValues, Optional lDimension As Long = 1, Optional lDefault As Long = -1) As Long On Error GoTo ErrFailed

LLBound = LBound(avValues, lDimension) Exit Function

ErrFailed:

LLBound = lDefault On Error GoTo 0 End Function

(63)

Using the Ubound and Lbound functions on an uninitialised typed array causes a "Subscript out of range" error. Use the following simple functions to safely check the bounds of any variant datatype.

'Notes : This function uses UBound with an error handler and allows you to specify a default return value

(64)

Counting the number of instances of a string within another string

The code below returns the number of instances of a string within another string.

'Purpose : Counts the number of instances of a specified string within another string. 'Inputs : sText The string to search in.

' sSearchFor The string to search for.

' [bIgnoreCase] If True does a case insensitive comparison.

' [sIgnoreText] If specified will ignore items between subsequent instances of ' "sSearchFor" which match this text (see example 2)

'Outputs : Returns the number of instances of the string. 'Author : Andrew Baker

'Date : 03/09/2000 13:58

'Example : eg. Find the instances of the character "A" within a string ' 1.

' Debug.Print StringCountInstances("ABCAA","A") ' Returns 3.

' Now find how many lines of data are contained within a string, ignoring any blanks lines. ' 2.

' Debug.Print StringCountInstances("ANDREW" & vbnewline & "BAKER" & vbnewline & vbnewline & "IS" & vbnewline & "GREAT!" & vbnewline,vbnewline,,"") ' Returns 3 (NOT 4).

'Revisions :

Function StringCountInstances(sText As String, sSearchFor As String, Optional bIgnoreCase As Boolean = True, Optional sIgnoreText As String) As Long Dim asItems() As String, lThisItem As Long

If bIgnoreCase Then

asItems = Split(UCase$(sText), UCase$(sSearchFor)) StringCountInstances = UBound(asItems)

Else

asItems = Split(sText, sSearchFor) StringCountInstances = UBound(asItems) End If

If Len(sIgnoreText) Then

'Deduct any items which contain the specified "sIgnoreText" For lThisItem = 0 To UBound(asItems) - 1

If asItems(lThisItem) = sIgnoreText Then 'Deduct this item

StringCountInstances = StringCountInstances - 1 End If

Next End If

(65)

Exit Function ErrFailed:

'Error occurred

Debug.Print "Error in StringCountInstances " & Err.Description Debug.Assert False

StringCountInstances = -1 End Function

(66)

Counting the number of instances of a string within another string

' Debug.Print StringCountInstances("ANDREW" & vbnewline & "BAKER" & vbnewline & vbnewline & "IS" & vbnewline & "GREAT!" & vbnewline,vbnewline,,"")

(67)

Automate Excel via VB

http://www.freevbcode.com/ShowCode.asp?ID=329 Dim ApExcel As Object 'To open Excel

Set ApExcel = CreateObject("Excel.application") 'Creates an object ApExcel.Visible = True ' So you can see Excel

ApExcel.Workbooks.Add 'Adds a new book. ApExcel.cells(1, 1).Formula = "HELLO" 'Add Text to a Cell

'You can use the line above, changing coordenates to go to any 'cell and you can also add Formulas

ApExcel.Range("A1:Z1").BORDERS.Color = RGB(0, 0, 0) 'Use it to 'change the borders.

ApExcel.Columns("A:AY").EntireColumn.AutoFit 'To adjust the 'column's width.

ApExcel.Range("A:Z").Select 'To establish a selection

(68)

(69)

A simple function for removing null values

To prevent "Type Mismatch" errors when returning a null value from a database to a form control, use the following function to remove any null values. 'Purpose : Removes Null Values

'Inputs : vValue The value to test for a null value ' [vDefault] The value to return if null is found 'Outputs : Returns an uninitialised variant (empty) if the value ' of vValue is null, else returns the value of vValue. 'Author : Andrew Baker

'Date : 25/03/2000 'Notes :

Public Function KillNull(vValue As Variant, Optional vDefault As Variant) As Variant If IsNull(vValue) = False Then

KillNull = vValue Else

KillNull = vDefault End If

(70)

(71)

Copying data between Excel Ranges

Range("A1:A10").Select Selection.Copy

Range("B1:B10").Select ActiveSheet.Paste

This can get fairly labour intensive if you are pasting data between Excel Worbooks or other sheets. The following code demonstrates a fast method of copying and pasting data between various different ranges: Option Explicit

'Purpose : Copies data from one excel range to another

'Inputs : rngSourceData The Excel Range containing the source data ' rngOutput The Excel Range to copy the data to

'Outputs : Returns True on Success 'Author : Andrew Baker

'Date : 13/11/2002 10:14 'Notes :

'Revisions :

Function ExcelRangeCopy(rngSourceData As Excel.Range, rngOutput As Excel.Range) As Boolean On Error GoTo ErrFailed

rngOutput.Value = rngSourceData.Value ExcelRangeCopy = True Exit Function ErrFailed: Debug.Print Err.Description ExcelRangeCopy = False End Function

'Demonstration routine, showing how to copy data between Excel ranges Sub Test()

Dim oWk1 As Workbook, oWk2 As Workbook, lThisRow As Long

If Application.SheetsInNewWorkbook = 1 Then

'Set the default number of sheets in a new workbook to 2 Application.SheetsInNewWorkbook = 2

End If

Many developers get stuck on the basics when it comes to VBA. The most common question I get sent is in regards to copying a pasting data between Excel Ranges. If you use trimmed down recorded macro to do this for you, you will most likely be using "Selected" ranges.

(72)

'Create two new workbooks Set oWk1 = Excel.Workbooks.Add Set oWk2 = Excel.Workbooks.Add

'Put some dummy data the first workbook For lThisRow = 1 To 10

oWk1.Sheets(1).Cells(lThisRow, 1).Value = "Cell " & lThisRow Next

'Copy the data into the next column

If ExcelRangeCopy(oWk1.Sheets(1).Range("A1:A10"), oWk1.Sheets(1).Range("B1:B9")) Then oWk1.Activate

oWk1.Sheets(1).Select

MsgBox "Copied range between columns!" Else

MsgBox "Failed to copy range between columns!" End If

'Copy the data into the next sheet

If ExcelRangeCopy(oWk1.Sheets(1).Range("A1:A10"), oWk1.Sheets(2).Range("A1:A10")) Then oWk1.Activate

MsgBox "Copied range between sheets!" Else

MsgBox "Failed to copy range between sheets!" End If

'Copy the data into the next worbook

If ExcelRangeCopy(oWk1.Sheets(1).Range("A1:A10"), oWk2.Sheets(1).Range("A1:A10")) Then oWk2.Activate

MsgBox "Copied range between workbooks!" Else

MsgBox "Failed to copy range between workbooks!" End If

(73)

This can get fairly labour intensive if you are pasting data between Excel Worbooks or other sheets. The following code demonstrates a fast method of copying and pasting data between various different ranges: Many developers get stuck on the basics when it comes to VBA. The most common question I get sent is in regards to copying a pasting

data between Excel Ranges. If you use trimmed down recorded macro to do this for you, you will most likely be using "Selected" ranges. eg:

(74)

(75)

(76)

Returning distinct values from an Excel Range

Often you may want to filter an excel range which contains duplicate values, to show only distinct values. The following function performs this (note, there is an example of how to using the function in the header). 'Purpose : Returns distinct values from a range

'Inputs : RangeValues The range containing the values to return the distinct items in. ' RangeDistinct The range which will to be populated with distinct values.

' Cell The Cell to return the distinct value into. 'Outputs : Returns a distinct value from a range of values 'Author : Andrew Baker

'Date : 04/Sep/2002 'Notes : Example:

' [A] [B] [Results] ' NAMES

'[4] ANDREW =DISTINCTVALUE(A$4:A$8,B$4:B$8,B4) ANDREW '[5] PETER =DISTINCTVALUE(A$4:A$8,B$4:B$8,B5) PETER '[6] ANDREW =DISTINCTVALUE(A$4:A$8,B$4:B$8,B6) JOHN '[7] JOHN =DISTINCTVALUE(A$4:A$8,B$4:B$8,B7) MARK '[8] PETER =DISTINCTVALUE(A$4:A$8,B$4:B$8,B8)

'[8] MARK =DISTINCTVALUE(A$4:A$8,B$4:B$8,B9) 'Revisions :

Function DISTINCTVALUE(RangeValues As Excel.Range, RangeDistinct As Excel.Range, Cell As Excel.Range) Dim oColDistinct As Collection, lCellIndex As Long

Dim oThisCell As Excel.Range Dim lIndexCount As Long

Application.Volatile True

lCellIndex = Cell.Row - RangeDistinct.Row Set oColDistinct = New Collection

DISTINCTVALUE = ""

On Error Resume Next

For Each oThisCell In RangeValues

oColDistinct.Add "", CStr(oThisCell.Value) If Err.Number Then

'Duplicate value Err.Clear

ElseIf lIndexCount = lCellIndex Then 'Found the index we want

DISTINCTVALUE = oThisCell.Value Exit For

(77)

lIndexCount = lIndexCount + 1 End If

(78)

Returning distinct values from an Excel Range

Often you may want to filter an excel range which contains duplicate values, to show only distinct values. The following function performs this (note, there is an example of how to using the function in the header).

(79)

(80)

(81)

Comparing the columns of two tables on different databases

The following example demonstrates how to compare the columns in two tables residing in different databases. Note, this was written in Sybase 11.5 which doesn't support dynamic SQL. I will rewrite this once I get round to upgrading: =--Before running This, change database1 and database2 to the names of the

--two databases you want to compare and the variable @table_name to the name of the table. declare @table_name varchar(255)

declare @table_id1 int declare @table_id2 int set nocount on

select @table_name = "MyTable" --Get object ID's of tables

select @table_id1 = id

from database1.dbo.sysobjects where name = @table_name select @table_id2 = id

from database2.dbo.sysobjects where name = @table_name

#NAME?

select "database1" as database_name, name as column_name, type as column_type, length as column_length into #tmp_names1

from database1.dbo.syscolumns where id = @table_id1

order by column_name

select "database2" as database_name, name as column_name, type as column_type, length as column_length into #tmp_names2 from database2.dbo.syscolumns where id = @table_id2 order by column_name #NAME? select * from #tmp_names1 where not exists (

select 1

from #tmp_names2

where #tmp_names1.column_name = #tmp_names2.column_name and #tmp_names1.column_type = #tmp_names2.column_type

(82)

and #tmp_names1.column_length = #tmp_names2.column_length )

union select *

from #tmp_names2 where not exists (

select 1

from #tmp_names1

where #tmp_names1.column_name = #tmp_names2.column_name and #tmp_names1.column_type = #tmp_names2.column_type and #tmp_names1.column_length = #tmp_names2.column_length )

order by column_name #NAME?

drop table #tmp_names1 drop table #tmp_names2 set nocount off

(83)

Comparing the columns of two tables on different databases

The following example demonstrates how to compare the columns in two tables residing in different databases. Note, this was written in Sybase 11.5 which doesn't support dynamic SQL. I will rewrite this once I get round to upgrading:

select "database1" as database_name, name as column_name, type as column_type, length as column_length

(84)

(85)

(86)

Combining the contents of two Excel Workbooks

The following code demonstrates how to combine the sheets from two workbooks.

Option Explicit

'Purpose : Appends the contents of a workbooks into an existing workbook. 'Inputs : sPathToDestinationWorkbook The path to the destination workbook.

' sPathToSourceWorkbook The path to the workbook containing the source sheets. 'Output : Returns "SUCCESS" if succeeded, else returns the error description/

'Author : Andrew Baker 'Date : 03/05/2001 13:50 'Notes :

'Revisions :

Function WorkbooksCombine(sPathToDestinationWorkbook As String, sPathToSourceWorkbook As String) As String Const csUniqueSheetName As String = "Â²Â³"

Dim wkDestination As Excel.Workbook, wkbSource As Workbook Dim shtToCopy As Object

On Error GoTo ErrFailed 'Open the workbooks

Set wkDestination = Application.Workbooks.Open(sPathToDestinationWorkbook) Set wkbSource = Application.Workbooks.Open(sPathToSourceWorkbook, , True)

'Copy sheets across

For Each shtToCopy In wkbSource.Sheets shtToCopy.Copy wkDestination.Sheets(1) Next

'Save and close output workbook wkDestination.Save

wkDestination.Close False Set wkDestination = Nothing 'Close the source workbook wkbSource.Close False Set wkbSource = Nothing

WorkbooksCombine = "SUCCESS" Exit Function

ErrFailed:

(87)

On Error GoTo 0 End Function

Dim sResult As String

'Place two workbooks in your c:\ directory with the names book1.xls and book2.xls sResult = WorkbooksCombine("C:\destination.xls", "C:\source.xls")

If sResult = "SUCCESS" Then

MsgBox "Book2.xls now contains the worksheets for Book1.xls... ", vbInformation Else

MsgBox "Export failed..." & vbNewLine & sResult, vbExclamation End If

(88)

Combining the contents of two Excel Workbooks

(89)

Returning the reference count on an object

Since VB is a COM based language all objects created in it will be a COM object. However, objects are relatively new to most VB programmers and as a result they have a habit of doing silly things with objects, like making circular references and not explicitly breaking them before unloading. This means VB my GPF during unloading or leak memory. The following routine lets you clearly identify how many references your object has to it and as such is a starting point on that long road to unravelling the mess you have created for yourself! Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (dest As Any, src As Any, ByVal nbytes As Long)

'Purpose : Returns the refence count for any VB created object (COM objects) 'Inputs : oObject The object to return the reference count on. 'Outputs : Returns the refence count or -1 on error

'Author : Andrew Baker 'Date : 19/Sep/2001 15:50

'Notes : Generally useful when debugging unloaded of class objects. ' Forms will always start with at least 6 references as they are loaded ' into the global name space and the Me keyword

'Revisions : 'Assumptions :

Function ObjectReferenceCount(oObject As IUnknown) As Long On Error GoTo ErrFailed

If (oObject Is Nothing) = False Then

'Reference count is offset 4 bytes from object pointer 'and is 4 bytes wide (a long)

CopyMemory ObjectReferenceCount, ByVal (ObjPtr(oObject)) + 4, 4 ObjectReferenceCount = ObjectReferenceCount - 2 Else ObjectReferenceCount = 0 End If If Err.LastDllError Then 'Error calling DLL ObjectReferenceCount = -1 Err.Clear End If Exit Function ErrFailed:

Debug.Print "Error in ObjectReferenceCount: " & Err.Description ObjectReferenceCount = -1

End Function

'Demonstration routine. Add a blank class called Class1 to a project and run the following 'code.

(90)

Sub Test()

Dim oObject1 As Class1 Dim oObject2 As Class1

'Make a single reference to class Set oObject1 = New Class1

Debug.Print ObjectReferenceCount(oObject1) 'Make another reference to class

Set oObject2 = oObject1

Debug.Print ObjectReferenceCount(oObject1) 'Release a reference to class

Set oObject2 = Nothing

Debug.Print ObjectReferenceCount(oObject1) 'Release last reference to class

Set oObject1 = Nothing

Debug.Print ObjectReferenceCount(oObject1) End Sub

(91)

Since VB is a COM based language all objects created in it will be a COM object. However, objects are relatively new to most VB programmers and as a result they have a habit of doing silly things with objects, like making circular references and not explicitly breaking them before unloading. This means VB my GPF during unloading or leak memory. The following routine lets you clearly identify how many references your object has to it and as such is a starting point on that long road to unravelling the mess you have created for yourself! Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (dest As Any, src As Any, ByVal nbytes As Long)

(92)

(93)

(94)

(95)

(96)

(97)

(98)

(99)