Task
我的目的是使用数据字段方法以最有效的方式将 UserForm 列表框数据写回工作表范围。
基本方法(特别是使用数据范围)
AFAIK 将数据写入某个范围的最有效方法是使用数组。如果数据源自某个范围,最好使用数据字段数组,默认情况下是二维的(一基数组)并允许
a) 将数据从工作表获取到数组或
b) 将数组写回sheet
用一行代码:
Dim v ' As Variant
Dim rng As Range
Set rng = ...
' a) range to array
v = rng.Value
' b) array to range
rng.Value = v
使用 ListBox 数据数组时的差异
ListBox数据存储在2dim中,但是零基础数组,例如列表框1.列表
[提示:因此可以使用预定义数组而不是使用众所周知的 Add 方法来创建项目,顺便说一句,仅限 10 列(索引 0 到 9)。 ]
尽管存在这种差异,但可以使用所描述的基本方法读入 ListBox 数据并将其写回工作表:
' aa) ListBox data to array
v = Me.ListBox1.List
' bb) array to range
rng.Value = v
此外,我还指出,默认情况下数组列数为 10,因此这与以编程方式设置的 ColumnCount 不对应。因此,有必要调整范围检查数组维度,参见。代码示例中的调试协议。
问题和解决方法
如果存在带有前导“==”字符串或类似用户输入的项目,则将数据字段数组写回单行中的工作表会引发错误 1004(应用程序定义的或对象定义的错误),因为这无法正确解释通过 Excel。
我尝试使用
rng.Text = v
代替
rng.value = v
也失败了,导致 424 object need 错误。
Question==> 是否可以纠正带有前导“=”字符的潜在错误项不循环使用 VBA 或 API 方法遍历所有数组项?欢迎任何代码示例来替换我的工作(代码中的步骤 3)
My Code
我的代码由四个步骤组成
1)创建一个2dim数据字段数组(v)
2)设定目标范围(rng)
3)[我纠正每个数组项的工作]
4)将数据写回工作表
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我推荐阅读“VBA 中的数组和范围”作者:C.Pearson
2017 年 9 月 17 日的 OP 附录(替代解决方案)
例如,如果您知道第一个目标列仅是文本,则可以在第 [4] 节之前编写以下语句,而不是搜索以=
:
rng.Columns(1).NumberFormat = "@"