Excel VBA function returns an array

Question

Can you create an Excel VBA function that returns an array just like LINEST, for example? I would like to create one that, given the vendor code, returns a list of products for that vendor from the product vendor table.

Answer 1

ok, here I have a datamapping function that returns an array of several columns, so you can reduce this to one. It doesn't matter how the array is filled, especially

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 

Answer 2

I think Collection may be what you are looking for.

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 fairly simple solution to the problem: populating the ComboBox for products whenever the ComboBox for suppliers changes the value with a minimum amount of vba.

 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 ComboBox for Suppliers comboSupplier and the one for comboProducts Products.