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:

  1. Copy the above code.
  2. Open any workbook.
  3. Press Alt + F11 to open the Visual Basic Editor (VBE).
  4. 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]
  5. Select an existing code module for the target worksheet; or from the Insert Menu, choose Insert | Module.
  6. Paste the code into the right-hand code window.
  7. Close the VBE, save the file if desired.
  8. See ?Test The Code? below
 

Test the code:

  1. Open the Excel example
  2. 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
  3. 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 93 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express