Excel VBA-避免将UF ListBox数组写入工作表的错误1004 [英] Excel VBA - avoid Error 1004 writing UF ListBox Array to Sheet
问题描述
任务
我的意图是使用数据字段方法以最有效的方式将UserForm Listbox数据写回到工作表范围.
My intention is to write back UserForm Listbox data to a sheet range in the most efficient way using the data field method.
基本方法(尤其是使用数据范围)
AFAIK要将数据写入范围的最有效方法是使用数组.如果数据源自某个范围，则最好使用数据字段数组，该字段默认为二维(基于一个数组)，并且允许 a)从工作表到数组获取数据或 b)将数组写回到工作表 一条代码行:
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
使用ListBox数据数组的区别
ListBox数据存储在2dim中，但基于零的数组，例如 ListBox1.List
ListBox data are stored in a 2dim, but zero based array, e.g. ListBox1.List
[提示:因此，可以使用预定义的数组创建项目，而不必使用众所周知的Add方法，BTW仅限于10列(索引为0到9). ]
[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). ]
尽管存在这种差异，但可以使用描述的基本方法读取ListBox数据并将其写回到工作表中:
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
此外，我说过，默认情况下，数组列数为10，因此这与以编程方式设置的ColumnCount不对应.因此，有必要调整检查数组尺寸的范围，请参见.代码示例中的调试协议.
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.
问题和解决方法
如果存在带有前导"=="字符串或类似用户输入的项目，则在一个衬里中将数据字段数组写回到工作表上会引发错误1004(应用程序定义的错误或对象定义的错误)，因为这不能被Excel正确解释.
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.
我尝试使用
rng.Text = v
代替
rng.value = v
也失败了，导致424对象需要错误.
did fail, too, causing a 424 object needed error.
问题 ==>是否可以使用VBA或API方法在所有数组项中使用前导"="字符无循环来纠正潜在的错误项?欢迎使用任何代码示例替换我的工作(代码中的第3步)
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)
我的代码
我的代码包含四个步骤 1)创建一个2dim数据字段数组(v) 2)设定目标范围(rng) 3)[我的工作围绕纠正每个数组项] 4)将数据写回到工作表
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
提示，我建议阅读"VBA中的数组和范围"皮尔逊(C.Pearson)
自17年9月17日起OP的附录(替代解决方案)
例如，如果您知道第一目标列仅是文本，则可以在第[4]节之前编写以下语句，而不用搜索以=开头的每个列表项:
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 因此，它需要修改i和j.
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
这篇关于Excel VBA-避免将UF ListBox数组写入工作表的错误1004的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
