在VBA中基于两个数组字段创建一个数组

本文介绍了在VBA中基于两个数组字段创建一个数组的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有几个表，其中的查找字段指向第三个表中的相同数据，并且两个字段都可以选择多个值.用户可以从每个表中选择一条记录，而我需要能够在VBA中分离出重叠的值.

I have a couple tables with lookup fields that point to the same data in a third table, and both fields can select multiple values. A user can select a record from each table, and I need to be able to separate out the overlapping values in VBA.

为此，我尝试在VBA中创建第三个数组，并将结果值转储到该数组中，但是出现Type Mismatch错误.我似乎找不到发生这种情况的原因.以下是我尽可能减少的代码:

To do this, I've tried creating a third array in VBA into which I'll dump the resulting values, but I'm getting a Type Mismatch error. I can't seem to find a reason that this would occur. The code, reduced as much as I can without losing the sense of it is below:

Function SetEnabledColours() 'Indexes for arrays of available colours Dim IndA As Long, IndG As Long, IndO As Long 'Arrays of available colour options Dim AuthorCol, GenreCol, OverlapCol() AuthorCol = DLookup("[AllowedColours]", "tblAuthor", "[Author]= '" & cmbAuthor & "'") GenreCol = DLookup("[AllowedColours]", "tblGenre", "[Genre]= '" & cmbGenre & "'") 'Separate overlapped options 'Cycle through AuthorCol For IndA = LBound(AuthorCol) To UBound(AuthorCol)

[然后我得到了错误]

[And then I get the Error]

'Check each GenreCol against this AuthorCol For IndG = LBound(GenreCol) To UBound(GenreCol) If GenreCol(IndG) = AuthorCol(IndA) Then 'Add to OverlapCol(CountO) ReDim Preserve OverlapCol(IndO) OverlapCol(IndO) = GenreCol(IndG) IndO = IndO + 1 'Skip over to next AuthorCol GoTo Escape1 End If Next IndG Escape1: Next IndA

最初，我已经将索引调暗为整数，但是后来我意识到这是因为我将数组视为范围.我知道这种类型的数据存储为数组而不是范围.

Originally I had Dimmed the Indexes as integer, but I have since realised that this was because I was thinking of the arrays as ranges. I understand data of this type is stored as an array rather than a range.

此处Erik的答案将我引向了数组:将多选查询字段数据VBA

Erik's answer here points me towards arrays: Multi-select Lookup Field data to VBA

这就是我基于以下内容创建重叠数组的原因:"> www.experts-exchange/questions/28530517/remove-array-element-in-access-vba.html

And this is what I'm basing my overlap array creation on: www.experts-exchange/questions/28530517/remove-array-element-in-access-vba.html

推荐答案

DLookup for multiple values字段返回一个字符串，该字符串的值列表用逗号(和空格)分隔，因此您应使用Split函数对此进行转换字符串到数组:

DLookup for multiple values field returns a string with list of values separated by comma (and a space), so you should use the Split function for converting this string to an array:

AuthorCol = Split(DLookup("[AllowedColours]", "tblAuthor", "[Author]= 'd'"), ", ") GenreCol = Split(DLookup("[AllowedColours]", "tblGenre", "[Genre]= '" & cmbGenre & "'"), ", ")