Excel VBA 函数返回一个数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1815980/
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
Excel VBA function returning an array
提问by moleboy
Can you create an Excel VBA function that returns an array in the same manner as LINEST does, for example? I would to create one that, given a supplier code, returns a list of products for that supplier from a product-supplier table.
例如，您能否创建一个以与 LINEST 相同的方式返回数组的 Excel VBA 函数？我想创建一个，给定供应商代码，从产品供应商表中返回该供应商的产品列表。
回答by moleboy
ok, here I have a function datamapping that returns an array of multiple 'columns', so you can shrink this down just to one. Doesn't really matter how the array gets populated, particularly
好的，这里我有一个函数数据映射，它返回一个包含多个“列”的数组，因此您可以将其缩小为一个。数组如何填充并不重要，特别是
Function dataMapping(inMapSheet As String) As String()
Dim mapping() As String
Dim lastMapRowNum As Integer
lastMapRowNum = ActiveWorkbook.Worksheets(inMapSheet).Cells.SpecialCells(xlCellTypeLastCell).Row
ReDim mapping(lastMapRowNum, 3) As String
For i = 1 To lastMapRowNum
If ActiveWorkbook.Worksheets(inMapSheet).Cells(i, 1).Value <> "" Then
mapping(i, 1) = ActiveWorkbook.Worksheets(inMapSheet).Cells(i, 1).Value
mapping(i, 2) = ActiveWorkbook.Worksheets(inMapSheet).Cells(i, 2).Value
mapping(i, 3) = ActiveWorkbook.Worksheets(inMapSheet).Cells(i, 3).Value
End If
Next i
dataMapping = mapping
End Function
Sub mysub()
Dim myMapping() As String
Dim m As Integer
myMapping = dataMapping(inDataMap)
For m = 1 To UBound(myMapping)
' do some stuff
Next m
end sub
回答by marg
I think Collectionmight be what you are looking for.
我想Collection可能是你正在寻找的。
Example:
例子：
Private Function getProducts(ByVal supplier As String) As Collection
Dim getProducts_ As New Collection
If supplier = "ACME" Then
getProducts_.Add ("Anvil")
getProducts_.Add ("Earthquake Pills")
getProducts_.Add ("Dehydrated Boulders")
getProducts_.Add ("Disintegrating Pistol")
End If
Set getProducts = getProducts_
Set getProducts_ = Nothing
End Function
Private Sub fillProducts()
Dim products As Collection
Set products = getProducts("ACME")
For i = 1 To products.Count
Sheets(1).Cells(i, 1).Value = products(i)
Next i
End Sub
Edit:Here is a pretty simple solution to the Problem: Populating a ComboBox for Products whenever the ComboBox for Suppliers changes it's value with as little vba as possible.
编辑：这是该问题的一个非常简单的解决方案：只要供应商的 ComboBox 使用尽可能少的 vba 更改其值，就为产品填充 ComboBox。
Public Function getProducts(ByVal supplier As String) As Collection
Dim getProducts_ As New Collection
Dim numRows As Long
Dim colProduct As Integer
Dim colSupplier As Integer
colProduct = 1
colSupplier = 2
numRows = Sheets(1).Cells(1, colProduct).CurrentRegion.Rows.Count
For Each Row In Sheets(1).Range(Sheets(1).Cells(1, colProduct), Sheets(1).Cells(numRows, colSupplier)).Rows
If supplier = Row.Cells(1, colSupplier) Then
getProducts_.Add (Row.Cells(1, colProduct))
End If
Next Row
Set getProducts = getProducts_
Set getProducts_ = Nothing
End Function
Private Sub comboSupplier_Change()
comboProducts.Clear
For Each Product In getProducts(comboSupplier)
comboProducts.AddItem (Product)
Next Product
End Sub
Notes: I named the ComboBox for Suppliers comboSupplier and the one for Products comboProducts.
注意：我将 ComboBox 命名为供应商 comboSupplier，将产品命名为 comboProducts。
