Passing lists and complex data through a parameter

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

Passing lists and complex data through a parameter

1. Introduction

2. Simple solution using a delimited list

3. Pass an Array

4. Pass a Collection or Dictionary object

5. Pass an XMLDocument

6. Conclusions and Recommendations

7. References and Extras

Comments (3)