Task
My intention is to write back UserForm Listbox data to a sheet range in the most efficient way using the data field method.
Basic Method (esp. using data ranges)
AFAIK the most efficient method in order to write data to a range is using arrays. If data originate from a range it's preferrable to use data field arrays, which by default are two dimensional (one based arrays) and allow to a) get data from sheet to array or b) write back the array to sheet with one code line:
Dim v ' As Variant
Dim rng As Range
Set rng = ...
' a) range to array
v = rng.Value
' b) array to range
rng.Value = v
Differences when using the ListBox data array
ListBox data are stored in a 2dim, but zero based array, e.g. ListBox1.List
[Hint: Therefore it's possible to create items using a predefined array instead of using the commonly known Add method, BTW restricted to only 10 columns (indexed 0 to 9). ]
Despite this difference, it is possible to read in the ListBox data and write them back to sheet with the described basic method:
' aa) ListBox data to array
v = Me.ListBox1.List
' bb) array to range
rng.Value = v
Furthermore, I remarked that by default the array columns count 10, so this won't correspond to the programmatically set ColumnCount. Therefore it's necessary to adapt the range checking the array dimensions, cf. debug protocol in the code example.
Problem and work around
Writing back the data field array to sheet in an one liner raises error 1004 (Application-defined or object-defined error) if there are items with a leading "==" string or comparable user inputs, as this can't be interpreted correctly by Excel.
My attempt to use
rng.Text = v
instead of
rng.value = v
did fail, too, causing a 424 object needed error.
Question ==> Is it possible to correct the potential error items with leading "=" character without looping through all array items using VBA or API methods? Any Code example is welcome to replace my work around (step 3 in code)
My Code
My code consists of four steps 1) create a 2dim data field array (v) 2) set a target range (rng) 3) [my work around correcting each array item] 4) write data back to worksheet
Private Sub CommandButton1_Click()
' declare variables
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Dump")
Dim s As String ' range address string
Dim sTest As String ' item test string
Dim v As Variant ' data field array
Dim rng As Range ' (target) range
Dim i As Long ' rows counter
Dim j As Long ' columns counter
Dim R As Long ' number of rows
Dim C As Integer ' number of columns
Dim lbxR As Long ' actual number of listbox items in data field
Dim lbxC As Integer ' actual number of listbox columns in data field
' Error Handler
On Error GoTo OOPS
' get programmatically defined listbox dimension
R = Me.ListBox1.ListCount
C = Me.ListBox1.ColumnCount
' clear sheet lines A2:A{r}
ws.Range(ws.Cells(2, 1), ws.Cells(R, C)).value = ""
' ===============================
' 1) create 2dim data field array
' -------------------------------
v = Me.ListBox1.List
' -------------------------------
' 2) set target range (rng)
' -------------------------------
' get actual data field dimension
lbxR = UBound(v, 1) - LBound(v, 1) + 1 ' first dimension
lbxC = UBound(v, 2) - LBound(v, 2) + 1 ' second dimension
s = ws.Range("A2").Resize(lbxR, lbxC).Address
' set correconding target range
Set rng = ws.Range(s) ' target range
' create short protocol - columns by default differ from user defined ColumnCount property !
Debug.Print String(80, "-")
Debug.Print vbNewLine & "** ListBox1 Defs Data Field Array Dimension **"
Debug.Print " [Target Range " & s & "]"
Debug.Print String(80, "-")
Debug.Print " ListCount = " & ListBox1.ListCount, "rows = " & lbxR & " = ubound(v,1)-lbound(v,1)+1 = " & UBound(v, 1) & " - " & LBound(v, 1) & " + 1 "
Debug.Print " ColumnCount= " & ListBox1.ColumnCount, "cols = " & lbxC & " = ubound(v,2)-lbound(v,2)+1 = " & UBound(v, 2) & " - " & LBound(v, 2) & " + 1 "
Debug.Print String(80, "-")
' ----------------------------------------------------------------
' 3) Work around - correct leading "=", if any occurences presumed
' (avoid error 1004 - App-defined or object-defined error)
' ----------------------------------------------------------------
' ==> Is there an alternative way?
' For i = 0 To R - 1 ' edited thx to D.Lee
' For j = 0 To C - 1
' v(i, j) = IIf(Left(Me.ListBox1.List(i, j) & "", 1) = "=", " ", "") & _
' Me.ListBox1.List(i, j)
' Next j
' Next i
' -------------------------------
' 4) write data back to worksheet
' -------------------------------
rng.value = v
Exit Sub
' =============
' Error Handler
' =============
OOPS:
MsgBox "ERL=" & Erl & " |Error " & Err.Number & "|" & Err.Description & vbNewLine & _
"s=" & s, vbExclamation
End Sub
Tip I recommend reading "Arrays and Ranges in VBA" by C.Pearson
Addendum to OP from Sep 17 '17(alternative solution)
If you know for example that the first target column is text only, you can code the following statement before section [4] instead of searching for each list item starting with =:
rng.Columns(1).NumberFormat = "@"
Lbound(v,1) = 0 ubound(v,1) = r-1 so, it needs to modify i and j.
For i = 0 To R - 1
For j = 0 To C - 1
v(i, j) = IIf(Left(Me.ListBox1.List(i, j) & "", 1) = "=", " ", "") & _
Me.ListBox1.List(i, j)
Next j
Next i