VBA Express : Multiple Apps - Create a string containing array values

Multiple Apps

Create a string containing array values

Ease of Use

Intermediate

Version tested with

2000

Submitted by:

MWE

Description:

ListOfVals creates a string containing (any type of) array values separated by blanks (or user?s option), and, optionally, CrLfs. The string is then easily displayed using, say, MsgBox

Discussion:

VBA?s formatting and display capability is generally quite good. However, there is a glaring lack of ability to easily display the values in an array. One typically ends up indexing through the array, stuffing the values into a string variable, adding separation and line feeds, and then displaying the string variable. Easy to do, but tedious. ListOfVals is a function (of type String) that does this ?stuffing? operation and allows the calling procedure to specify how values are separated and how CrLfs are to be inserted (or not). ListOfVals has 6 passed arguments (only 2 are required): 1) the array containing the values (can be of any type); 2) the (useful) length of the array; 3) [optional] the separator to be used (can be virtually anything, but is probably a space or two, default = ? ?); 4) [optional] the increment for CrLf, i.e., a vbCrLf will be added every ?m? values (default is m = 0, i.e., no vbCrLf added); 5) [optional] decimal point formatting (default is ?not used?); and, 6) [optional] index flag: tells proc if the array index should be part of the list (default is ?do not include?) The function is useable in virtually any VBA application. The Excel-based demonstration illustrates most options. Although simple is concept and execution, ListOfVals is a very handy and useful function.

Code:

instructions for use

			Option Explicit 
 
Sub Test_ListOfVals() 
     '
     '   Demonstration of ListOfVals function
     '
     '
    Dim N As Integer 
    Dim xlCell As Range 
    Dim xlVals(25) As Variant 
     
     '
     '           read in all cell values for the selection
     '
    N = 0 
    For Each xlCell In Selection 
        N = N + 1 
        If N > 25 Then 
            MsgBox "too much data for this demo (limited to 25 values)" & vbCrLf & _ 
            "continuing with 25 values", vbCritical + vbOKOnly 
            N = 25 
            Exit For 
        End If 
        xlVals(N) = xlCell.Value 
    Next xlCell 
     '
     '           display values using ListOfVals with defaults
     '
    MsgBox "Demo of ListOfVals {using defaults}" & vbCrLf & vbCrLf & _ 
    ListOfVals(xlVals, N) 
     '
     '           display values using ListOfVals with CrLf = 1 (CrLf after each item)
     '
    MsgBox "Demo of ListOfVals {CrLf = 1}" & vbCrLf & vbCrLf & _ 
    ListOfVals(xlVals, N, , 1) 
     '
     '           display values using ListOfVals using CRLf = 1 and Index = 1 (show
     '               array index, array is "one-based")
     '
    MsgBox "Demo of ListOfVals {CrLf = 1, Index = 1}" & vbCrLf & vbCrLf & _ 
    ListOfVals(xlVals, N, , 1, , 1) 
     '
     '           for floating point data, display values using ListOfVals and setting
     '               # dec pts at 3
     '
    If Selection.Column = 4 Then 
        MsgBox "Demo of ListOfVals {CrLf = 1, DecPt = 3, Index = 1}" & vbCrLf & vbCrLf & _ 
        ListOfVals(xlVals, N, , 1, 3, 1) 
    End If 
     
End Sub 
 
Function ListOfVals(X, N, _ 
    Optional Separ As String = " ", _ 
    Optional CrLf As Integer = 0, _ 
    Optional DecPt As Integer = 0, _ 
    Optional Index As Integer = -1) As String 
     '
     '****************************************************************************************
     '       Function:   creates a text string from any array with each array value
     '                   separated by a user-defined separator.  This proc is quite useful
     '                   when displaying array values via, say, MsgBox
     '       Passed Values:
     '           X     [in, any]  array of values
     '           N     [in, integer]  length of X
     '           Separ [in, string, OPTIONAL]  separator to be used; default = " "
     '           CrLf  [in, integer, OPTIONAL]  CrLf flag:
     '               if = 0, only Separ separates array values {default}
     '               if = m, vbCrLF added every "m" values
     '           DecPt [in, integer, OPTIONAL} decimal formating value; default = 0 (do
     '                   nothing)
     '               if = 0, nothing special is done
     '               if = k (k > 0), then the proc assumes X to be numeric and
     '                       will format the data values with k places to the right of
     '                       the decimal point
     '           Index [in, integer, OPTIONAL] flag to indicate if array index is to be
     '                       included:
     '               if = -1, do not knclude
     '               if = 0, include as zero-based
     '               if = 1, include as one-based
     '
     '***************************************************************************************
     '
     '
    Dim I As Long, CrLfCount As Long 
    Dim strFormat As String 
     
    If DecPt > 0 Then strFormat = "#." & String(DecPt, "#") 
    ListOfVals = "" 
    CrLfCount = 0 
    For I = 1 To N 
        Select Case Index 
        Case Is = -1 
        Case Is = 0 
            ListOfVals = ListOfVals & "[" & Trim(I - 1) & "]  " 
        Case Is = 1 
            ListOfVals = ListOfVals & "[" & Trim(I) & "]  " 
        End Select 
        If DecPt < 1 Then 
            ListOfVals = ListOfVals & X(I) & Separ 
        Else 
            ListOfVals = ListOfVals & Format(X(I), strFormat) & Separ 
        End If 
        CrLfCount = CrLfCount + 1 
        If CrLfCount = CrLf Then 
            ListOfVals = ListOfVals & vbCrLf 
            CrLfCount = 0 
        End If 
    Next I 
     
End Function

How to use:

Copy the above code.
Open any workbook.
Press Alt + F11 to open the Visual Basic Editor (VBE).
In the left side window, hi-lite the target spreadsheet [it will likely be called VBAProject(filename.xls) where filename is the name of the spreadsheet]
Select an existing code module for the target worksheet; or from the Insert Menu, choose Insert | Module.
Paste the code into the right-hand code window.
Close the VBE, save the file if desired.
See ?Test The Code? below

Test the code:

Open the Excel example
The demonstration spreadsheet contains a single worksheet with several examples of data in separate columns. Select a data sample and either click on the yellow box or navigate to Tools | Macro | Macro (or Alt-F8) and double click on Test_ListofVals
For the data in columns A, B, C, or E (integer, string chars, string words and mixed types, respectively), the test macro will read the selection into an array and then use ListOfVals and MsgBox to display the selection value. The first display calls ListOfVals with Separ and CrLf at their default values. The second display explicitly sets CrLf = 1, i.e., a vbCrLf is added after each value. A 3rd display explicitly sets CrLf = 1 and Index = 1 (include array index, array is ?one-based?). Column D contains real data with many digits to the right of the decimal point (only 4 are shown on the worksheet cells). If col D is selected, the test macro displays the data 3 times as above and then a 4th time with LrCf = 1, DecPt = 3 and Index = 1

Sample File:

ListOfVals.zip 21.46KB

Approved by mdmackillop

This entry has been viewed 64 times.