<

Passing lists and complex data through a parameter

Published on
23,091 Points
12,491 Views
6 Endorsements
Last Modified:
Awarded
Passing lists and complex data to VB 6 and VBA functions

1. Introduction

While editing AlainBryden's article (http:/A_1480.html) on using the SPLIT() function, I realized that there was a related topic that needed to be covered.  For Alain's article, he needed to pass some delimiters into the parsing function.  The parsing function iterates through the characters in the string and parses data accordingly.

I also edited a similar article by AngelIII (http:/A_1536.html) where he passes a (delimited) list of values to a stored procedure.  Although AngelIII is working in the SQL Server environment, the problem is very similar to this one.

Problem definition
We might need to pass some non-trivial, certainly more than single character delimiters, data into a routine (Sub or Function).  As our data needs grow more complex, this will be similar to passing an object, such as a recordset or control, as a parameter.  However, I want this article to concentrate on passing multiple pieces of data through a single parameter, rather than passing a single complex parameter.

I'm going to add other criteria to consider.  In the AlainBryden article, his solutions were supplied to the parsing routine as string literals.  As a result, I'm going to start with that premise.  After showing a method that meets criterion, I'm going to drop the 'single line invocation' criterion and expand to methods more suitable for larger and more complex list passing.

Applicable development environments
This material applies to VB6 and VBA.  The examples are written to this version of the VB language, although it is likely that most of these examples will compile in a VB.Net environment.  It is important to remember that you are creating an interface between the calling code and the called routine.  There are expectations that must be met by both parties.

Note: Many of the methods can be used in VB5 and VBScript, but you might have to write some more code, supplementing the additional functions in VB6, and use Variant data types.

In the VB.Net environment, our options are much greater and the intrinsic .Net framework provides many more functions and data types.  This article is not written for the VB.Net developer's options.  However, I will mention some of the applicable .Net name space alternatives when possible.

But what about Regular Expressions?
The two articles leading up to this were about parsing.  However, this article is about efficiently passing lists of items through a routine's parameter -- NOT parsing.  This why I'm not going to recommend or cover RegEx as a solution in this article:
If we are going to pass a list to a routine, we want it to be as simple as possible.  Simple is easy.  Easy is efficient.  RegEx is for complicated problems.  After all, why create another problem for ourselves?
While RegEx parsing is powerful, it is also very slow compared with the native parsing operations.

Since I've mentioned RegExp, there is an excellent article (http:/A_1336.html) on the subject for VB developers.

2. Simple solution using a delimited list

The simplest method for passing a list of strings is to pass a delimited string.  Since the string parsing article showed us multiple single-character delimiter parsing, it was a simple task to iterate through each character in the passed string.  However, we want the ability to pass multiple multi-character strings that aren't necessarily the same length.  Thus, we can expand on the work of that article.

One of the parsing article's reference links passes two parameters: the delimited string and the delimiter character.  (show example code)

You can combine the two parameters, as I demonstrate below, by concatenating the two parameters.  In this example, the delimiter character is the first character.

Now we have a simple method for passing multiple multi-character delimiters.
'=======================================================
'ReplaceAndSplit2 is based on the alainbryden routine
'Uses the native REPLACE() function to replace all delimiters with a common
'delimiter, and then splits the modified string on that delimiter.
'=======================================================
Function ReplaceAndSplit2(ByRef parmText As String, _
                          ByRef parmDelimiters As String) As String()
  Dim DelimCount As Long, DelimLoop As Long
  Dim strDelims() As String
  Dim strTemp As String, Delim1 As String
  Dim ThisDelim As String
  strTemp = parmText
  strDelims = Split(Mid(parmDelimiters, 2), Mid(parmDelimiters, 1, 1))
  Delim1 = parmDelimiters(0)
  DelimCount = UBound(strDelims)
  For DelimLoop = 1 To DelimCount
    ThisDelim = strDelims(DelimLoop)
    If InStr(strTemp, ThisDelim) <> 0 Then _
      strTemp = Replace(strTemp, ThisDelim, Delim1)
  Next DelimLoop
  ReplaceAndSplit2 = Split(strTemp, Delim1)
End Function

Open in new window

Usage: If we wanted to parse a web page's HTML to get the link data, we might invoke this routine with:
Dim strHTML() As String
Dim strLinks() As String
Dim lngLoop As Long, lngLink As Long
strHTML = ReplaceAndSplit2(strPageContents, "\<a \</a>")
ReDim strLinks(1 To UBound(strHTML)\2)
For lngLoop = 1 To UBound(strHTML) Step 2
  lngLink = lngLink + 1
  strLinks(lngLink) = strHTML(lngLoop)
Next

Open in new window

Pretty slick, eh?  By passing a delimited string of delimiters (sub-strings) we are now able to parse a string with a list of multi-character delimiters.  With this one change, we now allow our custom parsing function to behave much more like repeated applications of the Split() function.  But, there's more&

3. Pass an Array

The instrinsic VB Split() function has other parameters.  You can limit the number of found delimiters it will act upon and you can override the case-sensitive comparison of its delimiter string.  It's going to be difficult to pass the required data through a delimited string as we've done above.  We will need a more complex sophisticated data structure.

It is possible to pass an array of some data type as a parameter.  The language also has an easy-to-use function that will create an array on-the-fly.  Since the Array() function accepts many different data types, it produces a Variant array.
http://www.cpearson.com/excel/VBAArrays.htm

Here is an example of an array of multi-character delimiters that we can pass into the parsing function.
Array("<a ", "</a>", "img src=", "href=", _
      "<form ", "</form>")

Open in new window


Incremental array building
Populating an array can be tricky.  Firstly, you need to have enough empty slots to contain the new entries.  If you are lucky enough to know how many entries you will add, then this process is fairly simple and efficient: just ReDim the array.  However, you may need to add additional items.  You do this by a ReDim of the array using the Preserve option to increase its size and then copy more items to the array.

Note: The ReDim Preserve operation is a poor performer due to the underlying memory management and data copying operations.  It is better to allocate a larger array than you might need and then trim the size of the array with a final ReDim Preserve operation.

It is also possible to add new items using the intrinsic VB Split() and Join() functions.
Dim Delims() As Variant

Delims = Array("<a ", "</a>")
Delims = Split(Join(Delims,"^") & "^" & "img src=" & "^" & "href=","^")
Delims = Split(Join(Delims,"^") & "^" & "<form " & "^" & "</form>", "^")

'To see the contents of Delims, run the following:
Dim D As Long
For D = 0 To UBound(Delims)
  Debug.Print D, Delims(D)
Next

Open in new window


Note: The iterative Split() and Join() operations is a poor performer for the same reasons as iterative ReDim Preserve operations.

More Complex array data
With the Array() function it is possible to pass multi-dimensional arrays and arrays containing mixed data types.  In this code snippet, the first item in each row is a numeric data type, the second item a string data type, and the third a date data type.
Dim Ex() As Variant

Ex = Array(Array(1,"Mark",#2/3/2009#), Array(2,"Fred",#3/4/2009#))

'To see the contents of Ex, run the following:
Dim D As Long
For D = 0 To UBound(Ex)
  Debug.Print D, Ex(D)(0) , Ex(D)(1) , Ex(D)(2)
Next

Open in new window



Let's enhance our custom parsing function.  Since the Replace() function also has both count and compare parameters, we can use these in our existing function.

Note: It is possible to accomplish our parsing with iterative Split/Join operations, but this function pair isn't as efficient as iterative Replace operations.
'=======================================================
'ReplaceAndSplit3 is based on the alainbryden routine
'Uses the native REPLACE() function to replace all delimiters with a common
'delimiter, and then splits the modified string on that delimiter.
'=======================================================
Function ReplaceAndSplit3(ByRef parmText As String, _
                          parmDelimiters() As Variant) As String()
  '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  'Expected format of parmDelimiters array:
  '  Each row contains three element array (indexes = 0,1,2)
  '    = delimstring, count, stringcomparetype
  'Note: default count = -1 and the default stringcomparetype = 0
  '      See the Replace function documentation in the appendix for
  '      more information.
  '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  Dim DelimCount As Long, DelimLoop As Long
  Dim strTemp As String
  Dim Delim1() As Variant
  Dim ThisDelim() As Variant
  strTemp = parmText
  Delim1 = parmDelimiters(0)
  DelimCount = UBound(parmDelimiters)
  For DelimLoop = 1 To DelimCount
    ThisDelim = parmDelimiters(DelimLoop)
    If InStr(strTemp, ThisDelim(0)) <> 0 Then _
      strTemp = Replace(strTemp, ThisDelim(0), Delim1(0), 1, ThisDelim(1), ThisDelim(2))
  Next DelimLoop
  ReplaceAndSplit3 = Split(strTemp, Delim1(0), Delim1(1), Delim1(2))
End Function

Open in new window

Usage: If we wanted to parse a web page into the header and the first 5 name anchors, we might invoke this routine with:
Dim strHTML() As String
strHTML = ReplaceAndSplit3(strPageContents, _
          Array(Array("<head>", -1, 0), Array("</head>", -1, 0), _
                Array("<a name=", 5, 0)))

Open in new window


We are now going to drop the criterion that we might construct the list on the statement that we pass the parameter.  All of the following methods are better suited for situations where you build the list of parameters through iteration or from some external source.

4. Pass a Collection or Dictionary object

Collection and Dictionary objects combine a linked list's speed when adding a new item and the strength of associative item access.  An associative item access means that you can name the entries -- associate each item with a (string value) key.  In addition to the key value access, items can also be accessed with a numeric (index) value, like array items.

Some Background on Collection and Dictionary objects
Collections are an intrinsic VB data type.  As such, you do not have to add any references to your project.  You have two choices when defining these objects in your code -- early binding and late binding.  With early binding, the object variables exhibit Intellisense, where the properties and methods appear in a dropdown list when you type a period following the variable name.
Dim colEarlyBind As New Collection

Open in new window


Dim colLateBind As Collection

Set colLateBind = New Collection

Open in new window


Adding items to a collection object.  For collections, the item data is the first parameter and the optional key value is the second parameter.
Dim colEarlyBind As New Collection

colEarlyBind.Add "<a "
colEarlyBind.Add "</a>"
colEarlyBind.Add "img src="
colEarlyBind.Add "href="
colEarlyBind.Add "<form "
colEarlyBind.Add "</form>"

Open in new window


Dictionary objects are part of the Microsoft Scripting Runtime.  If you want the developer advantages of early binding, you will need to add a reference to the Microsoft Scripting Runtime library to your project.
Dim dicEarlyBind As New Scripting.Dictionary

Open in new window


Dim dicLateBind As Object

Set dicLateBind = CreateObject("Scripting.Dictionary")

Open in new window


Adding items to a dictionary object.  For dictionary objects, the key is the first parameter and the item data is the second parameter.
Dim dicEarlyBind As New Scripting.Dictionary

dicEarlyBind.Add 1,"<a "
dicEarlyBind.Add 2,"</a>"
dicEarlyBind.Add 3,"img src="
dicEarlyBind.Add 4,"href="
dicEarlyBind.Add 5,"<form "
dicEarlyBind.Add 6,"</form>"

Open in new window


Col/Dic example function 1
This example is actually a step backwards.  This example implements an equivalent functionality as ReplaceAndSplit2().
'=======================================================
'ReplaceAndSplit4 is based on the alainbryden routine
'Uses the native REPLACE() function to replace all delimiters with a common
'delimiter, and then splits the modified string on that delimiter.
'=======================================================
Function ReplaceAndSplit4(ByRef parmText As String, _
                          parmDelimiters As Variant) As String()
  '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  'parmDelimiters is expected to be a collection or dictionary.
  'Note: Since the default item is iterated by the For Each statement
  '      below, the dictionary delimiters should be assigned to the
  '      key values since those are the default items.
  '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  Dim strTemp As String
  Dim Delim1 As Variant
  Dim ThisDelim As Variant
  strTemp = parmText
  For Each ThisDelim In parmDelimiters
    If IsEmpty(Delim1) Then
      Delim1 = ThisDelim
    Else
      If InStr(strTemp, ThisDelim) <> 0 Then _
        strTemp = Replace(strTemp, ThisDelim, Delim1)
    End If 
  Next
  ReplaceAndSplit4 = Split(strTemp, Delim1)
End Function

Open in new window


Usage: If we wanted to parse SQL query Where clause, we might invoke this routine with:
Dim dicDelim As New Scripting.Dictionary
Dim strParsed() As String

dicDelim.Add "(", 1
dicDelim.Add ")", 1

strParsed = ReplaceAndSplit4("WHERE (HireDate Between #4/1/2000# And #4/1/2009#) And (Cat Like 'PROD*')", dicDelim)

Open in new window



Dic example function 2
Even though a collection item can have a key value associated with it, there is no way to retrieve the key values, just retrieve an item value by supplying the key value.  However, the dictionary object exposes both the items and they keys.  Think about these two sets of data as name=value pairs.  This is an example that uses the key value as the recordset field name and the item value as the new field value.
'=======================================================
'UpdateRecord accepts key/item (field name/value) pairs through 
'  a dictionary parameter, a recordset, and key value.
'=======================================================
Function UpdateRecord(parmRS As Recordset, _
                      parmID As Long _
                      parmNewValues As Variant) As Long
  '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  'parmRS is a recordset
  'parmID is the ID (autonumber) value
  'parmNewValues is expected to be a dictionary
  '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  Dim vFieldname As Variant
  Dim lngError As Long

  On Error Resume Next

  parmRS.Find "ID=" & parmID

  parmRS.Edit
    For Each vFieldname In parmNewValues
      parmRS.Fields(vFieldname) = parmNewValues(vFieldname)
      If Err <> 0 Then
        lngError = Err
        Exit For
      End If
    Next

  If lngError = 0 Then
    parmRS.Update
  Else
    parmRS.CancelUpdate
  End If

  UpdateRecord = lngError    'Return any error value
End Function

Open in new window


Usage: In this scenario, we have detected user changes to a row (in a non-bound control), saving the new values for each changed field in a dictionary object (key=field name).  Now we update the recordset with the function:
Dim dicDataChgs As New Scripting.Dictionary
Dim lngRC As Long

dicDataChgs.Add "VisitDate", #4/1/2008#
dicDataChgs.Add "AgeAtEncounter", 55
dicDataChgs.Add "CD4Count", 780
dicDataChgs.Add "ARTname", "Kaletra"
dicDataChgs.Add "HemoFactorType", Null

lngRC = UpdateRecord(rsClinData, 2525, dicDataChgs)

Open in new window


Col/Dic example function 3
We can expand the prior function to update multiple rows.  To accomplish this we will save the field value change dictionary object inside another dictionary object with the key being the row's ID value.  Since there may be multiple rows updated, we will return multiple error codes, one for each attempted row update.
'=======================================================
'UpdateRecords accepts key/item (field name/value) pairs through 
'  a dictionary parameter, and a recordset.
'=======================================================
Function UpdateRecords(parmRS As Recordset, _
                       parmNewValues As Variant) As Collection
  '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  'parmRS is a recordset
  'parmNewValues is expected to be a dictionary of dictionary objects.
  '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  Dim vFieldname As Variant
  Dim vRow As Variant
  Dim lngError As Long
  Dim colErrors As New Collection

  On Error Resume Next

  For Each vRow In parmNewValues

    parmRS.Find "ID=" & vRow
    parmRS.Edit

      For Each vFieldname In parmNewValues(vRow)
        parmRS.Fields(vFieldname) = parmNewValues(vRow)(vFieldname)
        If Err <> 0 Then
          lngError = Err
          Exit For
        End If
      Next

    If lngError = 0 Then
      parmRS.Update
    Else
      parmRS.CancelUpdate
    End If

    colErrors.Add CStr(lngError)    'Return any error values

  Next   'Row change

  Set UpdateRecords = colErrors
End Function

Open in new window


Usage: In this scenario, we have detected user changes to two rows (in a non-bound control), saving the new values for each changed field in a dictionary object (key=field name).  Now we update the recordset with the multi-row change function:
Dim dicDataChgs As New Scripting.Dictionary
Dim dicRowChgs As New Scripting.Dictionary
Dim colErrors As New Collection

dicDataChgs.Add "VisitDate", #4/1/2008#
dicDataChgs.Add "AgeAtEncounter", 55
dicDataChgs.Add "CD4Count", 780
dicDataChgs.Add "ARTname", "Kaletra"
dicDataChgs.Add "HemoFactorType", Null
dicRowChgs.Add 2525, dicDataChgs

Set dicDataChgs = New Scripting.Dictionary    'new instance
dicDataChgs.Add "VisitDate", #5/15/2008#
dicDataChgs.Add "AgeAtEncounter", 19
dicDataChgs.Add "HemoFactorType", "VIII"
dicRowChgs.Add 42, dicDataChgs

Set colErrors = UpdateRecords(rsClinData, dicRowChgs)

Open in new window


Using class objects
In our last example, we passed multiple dictionary objects, stored inside a dictionary object 'wrapper'.  However, you are much more likely to see multiple complex objects stored rather than multiple dictionary objects stored.  Since we can create own class objects, we can go far beyond the simple key/item pair data we've used in the dictionary object examples above.  We would still use a collection or dictionary object to contain multiple instances of our class object, providing us with the ability to pass these to a function through a single parameter.

If you are unfamiliar with using class objects, think about creating your own single-row recordset object.  You add a Class module to your project and click on the Insert | Procedure menu, you will see that the procedure type options include PROPERTY.  Class properties are most similar to recordset fields, they have a data type and a value.

The coverage of class creation and usage will be the central theme of a subsequent article.

Class object example
Behind the scenes in the following example is a class with two properties (Action, Value, NewValue).  We are going to pass a collection of these class objects to a routine that effects the Actions.
'=======================================================
'FileActions performs actions on files
'=======================================================
Function FileActions(parmActions As Collection) As Collection
  Dim clsAction As MyActionClass
  Dim colErrors As New Collection
  
  On Error Resume Next
  For Each clsAction In parmActions
    Select Case clsAction.Action
      Case ActionEnums.Copy
        FileCopy clsAction.Value, clsAction.NewValue
      Case ActionEnums.Delete
        Kill clsAction.Value
      Case ActionEnums.Move
        FileCopy clsAction.Value, clsAction.NewValue
        Kill clsAction.Value
      Case ActionEnums.Rename
        Name clsAction.Value As clsAction.NewValue
    End Select
    
    colErrors.Add CStr(Err)    'Return any error values
    
    If Err <> 0 Then
      Err.Clear
    End If

  Next
  Set FileActions = colErrors
End Function 

Open in new window

Usage: In this scenario, I create three class objects to rename, copy and delete some files.  I've done something similar in one of my commercial applications.  I might need to send an encrypted file to a user to correct some application or licensing problem.  After decrypting the file, I process the individual actions supplied.  In this example, the actions and other properties are being set from literals instead of from an external source.
Dim clsX As MyActionClass
Dim colActions As New Collection
Dim colErrors As New Collection
Dim vError As Variant

Set clsX = New MyActionClass
With clsX
  .Action = Rename
  .Value = "C:\Temp\Old.txt"
  .NewValue = "C:\Temp\New.txt"
End With
colActions.Add clsX

Set clsX = Nothing
Set clsX = New MyActionClass
With clsX
  .Action = Copy
  .Value = "C:\Temp\New.txt"
  .NewValue = "C:\Temp2\NewCopy.txt"
End With
colActions.Add clsX

Set clsX = Nothing
Set clsX = New MyActionClass
With clsX
  .Action = Delete
  .Value = "C:\Temp\New.txt"
End With
colActions.Add clsX

Set colErrors = FileActions(colActions)

'Display the error collection in the Immediate window
For Each vError In colErrors
  Debug.Print vError, Error(vError)
Next

Open in new window

The class module.  I make this as simple as I could, but keep it non-trivial.
Option Explicit
Public Enum ActionEnums
  Rename = 1
  Delete = 2
  Copy = 4
  Move = 8
End Enum

Public Action As ActionEnums
Private strValue As String
Private strNewValue As String

Public Property Get Value() As Variant
  Value = strValue
End Property

Public Property Let Value(ByVal vNewValue As Variant)
  strValue = vNewValue
End Property

Public Property Get NewValue() As Variant
  NewValue = strNewValue
End Property

Public Property Let NewValue(ByVal vNewValue As Variant)
  strNewValue = vNewValue
End Property

Open in new window


5. Pass an XMLDocument

Like objects, XML files and strings are capable of storing very complex data, including lists of items.  You will need to load the reference to the MSXML library. (early binding) for this code to work as written.  In this simple example, the XML file contains some house-related data.  The ListNodes code iterates through the nodes and prints a formatted version of the contents in the Immediate window.
Sub ListNodes(parmXMLdoc As DOMDocument)
  Dim xElement As IXMLDOMElement
  Dim xElement2 As IXMLDOMElement
  For Each xElement In parmXMLdoc.childNodes(1).childNodes
    Debug.Print xElement.getAttribute("Name")
    For Each xElement2 In xElement.childNodes
      Debug.Print , xElement2.nodeName, xElement2.Text
    Next
    Debug.Print "__________________"
  Next

End Sub

Open in new window


Usage: I initialize a variable with the contents of an XML file and then pass that variable to the ListNodes subroutine.
Dim xDoc As New DOMDocument
xDoc.Load "C:\Users\AikiMark\Documents\test.xml"
ListNodes xDoc

Open in new window

The test.XML document contents.
<?xml version="1.0" encoding="utf-8"?>
<Houses>
  <House Name="Mark">
    <Color>Carolina Blue</Color>
    <Addr>14 Flagon O Mead Ct</Addr>
    <City>Durham</City>
    <State>NC</State>
    <Zip>27714</Zip>
  </House>
  <House Name="Fred">
    <Color>Red Brick</Color>
    <Addr>2610 Flintstone Blvd</Addr>
    <City>Tullahoma</City>
    <State>TN</State>
    <Zip>98898</Zip>
  </House>
</Houses>

Open in new window


6. Conclusions and Recommendations

It is possible to pass long and complex lists of items through a single parameter.  You should use as simple a scheme as meets your needs.  The advantage of collections and dictionary objects is that you do not have to know how many items you are going to have before you start storing them in the data structure.

7. References and Extras

Excel example function -- unique values
I want to leave this nugget for the Excel-centric readers.  There are many times when you need to eliminate the duplicate values from a range.  Like we did with the two UpdateRecord(s) examples, we can return a collection or dictionary object.  In this case, the Excel user will be prompted for a range containing duplicate values and prompted for a starting point into which to start placing unique values.

Note: I am using the Appliction.InputBox() function instead of the VB InputBox() function, since I can specify the acceptable data type.
Public Sub UniqueValues()
  Dim rngOriginal As Range
  Dim rngUnique As Range
  Dim dicUniqueValues As Scripting.Dictionary
  Dim vUnique As Variant
  Dim lngOffset As Long
  Const cIBtype_Range As Long = 8

  Set rngOriginal = Application.InputBox("Select range of values", _
                    "Range Prompt", Type:=cIBtype_Range)
  Set rngUnique = Application.InputBox("Select starting location for unique values", _
                    "Unique Start Prompt", Type:=cIBtype_Range)
  
  Set dicUniqueValues = GetUniqueValues(rngOriginal)
  Application.
  Application.ScreenUpdating = False    'for better performance
  For Each vUnique In dicUniqueValues
    rngUnique.Offset(lngOffset, 0) = vUnique
    lngOffset = lngOffset + 1
  Next
  Application.ScreenUpdating = True
End Sub

Public Function GetUniqueValues(parmRng As Range) As Scripting.Dictionary
  Dim dicUnique As New Scripting.Dictionary
  Dim rngCell As Range
  For Each rngCell In parmRng
    If dicUnique.Exists(rngCell.Value) Then
    Else
      dicUnique.Add rngCell.Value, 1
    End If
  Next
  Set GetUniqueValues = dicUnique
  Set dicUnique = Nothing
End Function

Open in new window


Really Fast version: If you have a large set of unique values, you might also benefit from this trick.  Since dictionary keys will automatically be exposed as an array, we can assign these values to an array variable.  Once in an array variable, we can use the Transpose worksheet function to copy all the unique values into the worksheet in one operation.
Public Sub UniqueValuesFast()
  Dim rngOriginal As Range
  Dim rngUnique As Range
  Dim vArray() As Variant
  Const cIBtype_Range As Long = 8

  Set rngOriginal = Application.InputBox("Select range of values", _
                    "Range Prompt", Type:=cIBtype_Range)
  Set rngUnique = Application.InputBox("Select starting location for unique values", _
                    "Unique Start Prompt", Type:=cIBtype_Range)
  
  vArray = GetUniqueValues(rngOriginal).Keys

  Application.ScreenUpdating = False    'for better performance

  rngUnique.Worksheet.Range(rngUnique.Address, rngUnique.Offset(UBound(vArray), 0)) = _
           Application.WorksheetFunction.Transpose(vArray)

  Application.ScreenUpdating = True
End Sub

Open in new window


Replace function

Replace(String, Findstring, Replacewith [,Start[,Count[,Compare]]])
Parameter 	Description
String		The string to be searched
Findstring	The searched-for string -- will be replaced
Replacewith	The replacement string
Start		Optional. Specifies the start position -- Default = 1
Count		Optional. Specifies the number of substitutions to perform.  The default value is -1, which causes replacement of all the Findstring values

Compare		Optional. Specifies the type of comparison to use, case-sensitive or case-insensitive.  The default is 0 (case sensitive) which performs faster.

		Can be one of the following values:
		  0 = vbBinaryCompare - Perform a binary comparison 
		  1 = vbTextCompare 

Open in new window

6
Comment
Author:aikimark
  • 2
3 Comments
 
LVL 47

Author Comment

by:aikimark
Note:
In the class object example
colErrors.Add CStr(Err)

would probably be simpler if written as
colErrors.Add Err.Number
0
 

Expert Comment

by:merkslay
hello that such a question like sending a matrix (2.2) with all its values ¿¿as a store procedure parameter type as sql 2008 table from VB6.0? , Appreciate your help.

Mario
Lima- Perú
0
 
LVL 47

Author Comment

by:aikimark
@Mario

Do a search of articles and you should find an article about passing a complex parameter to a stored procedure in the form of XML or a delimited list.  This article only concerns itself with passing complex parameters to VB/VBA routines.
0

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Join & Write a Comment

Suggested Articles

Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Next Article:

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month