vba 如何在vba中引用数组变量访问另一个变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8608824/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to reference array variable in vba access to another variable
提问by user677275
hi guys im trying to take the value of data(0)
and put it into say variable InvoiceNumber. I tried putting an image of my watch screen but i ended up not being allow. but here is what my watch screen would look like.
data
data(0)
data(0,1) 1
data(0,2) 2
data(0,3) 3
嗨，伙计们，我正在尝试获取 data(0) 的值并将其放入变量 InvoiceNumber 中。我试着放一张我的手表屏幕的图像，但我最终没有被允许。但这是我的手表屏幕的样子。数据
数据(0)
数据(0,1) 1
数据(0,2) 2
数据(0,3) 3
I have tried
我试过了
dim InvoiceNumber as variant <br/>
invoiceNumber = data(0)
but i keep getting error. I dont know how to reference just the part of that array. any help would be greatly appreciated.
但我不断收到错误。我不知道如何只引用该数组的一部分。任何帮助将不胜感激。
here is the full code for anyone that would like to see a little more.
这是任何希望看到更多内容的人的完整代码。
Dim db As Database
Dim rs As Recordset
Dim data As Variant
Dim colummn1 As Variant
Dim obj As Object
Set db = CurrentDb
Set rs = db.OpenRecordset("select * from Table1")
'set obj = new object
While Not rs.EOF
'MsgBox (rs.RecordCount)
'MsgBox (rs.Fields.Count)
data = rs.GetRows(rs.Fields.Count)
Column1 = data.data(0)
Wend
rs.Close
db.Close
End Sub
回答by Olivier Jacot-Descombes
Try
尝试
Column1 = data(0,0)
instead of
代替
Column1 = data.data(0)
datacontains a two-dimensional array. The fist index is the field number, the second index is the row number. Both start at zero. So data(0,0)is the first field of the first row. data(1,0)is the second field of the first row.
data包含一个二维数组。第一个索引是字段号，第二个索引是行号。两者都从零开始。data(0,0)第一行的第一个字段也是如此。data(1,0)是第一行的第二个字段。
I would try to make an array of invoices by using a user defined type
我会尝试使用用户定义的类型制作一组发票
Public Type Invoice
Nr As Variant
IssueDate As Variant
Total As Variant
'Or whatever your invoice contains
End Type
Public Sub TestGetRecords()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim data As Variant
Dim numRecords As Long, i As Long
Dim Invoices() As Invoice
Set db = CurrentDb
Set rs = db.OpenRecordset("select * from Table1")
data = rs.GetRows()
rs.Close
db.Close
numRecords = UBound(data, 2) + 1
ReDim Invoices(0 To numRecords - 1) As Invoice
For i = 0 To numRecords - 1
Invoices(i).Nr = data(0, i)
Invoices(i).IssueDate = data(1, i)
Invoices(i).Total = data(2, i)
Next i
Debug.Print Invoices(0).Total
End Sub
An error in you solution is, that you placed GetRowsin a loop. However, GetRows returns all the rows at once!
您的解决方案中的一个错误是，您将其置于GetRows循环中。但是，GetRows 一次返回所有行！
回答by Eric
I am finding it a little hard to understand what you are trying to achieve.
我发现有点难以理解您想要实现的目标。
You are reading an entire table of data into a recordset object, loading the fields of the recordset object into a two dimensional array and then trying to iterate through the array to output the results to a single variable (or a sum of array values).
您正在将整个数据表读入记录集对象，将记录集对象的字段加载到二维数组中，然后尝试遍历数组以将结果输出到单个变量（或数组值的总和）。
Why don't you just use an SQL expression to extract the data directly from your table?
为什么不直接使用 SQL 表达式从表中提取数据？
If we can assume you have a table of invoices (say ... "myInvoiceTable") with at least the following fields ...
如果我们可以假设您有一张发票表（比如……“myInvoiceTable”），至少包含以下字段……
invoiceID invoiceValue (and probably many others, eg. invoiceDate, clientID, etc.)
invoiceID invoiceValue（可能还有很多其他的，例如 invoiceDate、clientID 等）
you can write an expression something like this
你可以写一个这样的表达式
"SELECT SUM(invoiceValue) AS MyTotal FROM myInvoiceTable WHERE invoiceID > xxxx"
“SELECT SUM(invoiceValue) AS MyTotal FROM myInvoiceTable WHERE invoiceID > xxxx”
This might go into your VBA code like this.
这可能会像这样进入您的 VBA 代码。
Dim rst as Recordset
Dim strSQL as string
strSQL = "SELECT SUM(invoiceValue) AS MyTotal FROM myInvoiceTable WHERE invoiceID > 400" '-- use an appropriate WHERE clause if needed
Set rst = CurrentDb.Openrecordset(strSQL) '-- the rst object should contain a single value "MyTotal" which is the sum of the fields you are trying to access
MsgBox rst!MyTotal '-- use this value wherever you need to use it
