Excel VBA函数返回一个数组 [英] Excel VBA function returning an array
本文介绍了Excel VBA函数返回一个数组的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
您可以创建一个返回数组的Excel VBA函数,就像LINEST一样吗?我将创建一个供应商代码,从产品供应商表返回该供应商的产品列表。
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.
推荐答案
好的,这里我有一个函数datamapping返回一个多列数组,所以你可以缩小到一个
数组的填充方式并不重要,特别是
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
这篇关于Excel VBA函数返回一个数组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文