Output array from VBA function to Excel sheet using formula

Question

I found a nifty RegEx function that I'm using (see below). The function outputs an array. This is fine if I only ever need the first element of the array. But I'm trying to extract authors from citation data, so I need to pull multiple items from this output.

I know about arrays in Excel sheets. So I tried ={ReFind(A3,"[^()]+")}, selected an area and pressed ctrl+shift+enter, but it returns an error and {=ReFind(A3,"[^()]+")} just duplicates the formula across the cells.

Is there a way to have the function output the array to multiple cells using a formula? Can I get away without having to write some more vba?

Function ReFind(FindIn, FindWhat As String, _
    Optional IgnoreCase As Boolean = False)
Dim i As Long
Dim matchCount As Integer
Dim RE As Object, allMatches As Object, aMatch As Object
Set RE = CreateObject("vbscript.regexp")
RE.Pattern = FindWhat
RE.IgnoreCase = IgnoreCase
RE.Global = True
Set allMatches = RE.Execute(FindIn)
matchCount = allMatches.Count
If matchCount >= 1 Then
    ReDim rslt(0 To allMatches.Count - 1)
    For i = 0 To allMatches.Count - 1
        rslt(i) = allMatches(i).Value
    Next i
    ReFind = rslt
Else
    ReFind = ""
End If
End Function

My hacked solution would be to add a new optional variable to the function to pull the i'th item from the array. But I'd like it if I could use the formula array functionality built in to Excel. — Jesse
– Jesse, Commented Jul 6, 2011 at 17:57

Charles Williams · Accepted Answer · 2011-07-06 19:34:25Z

2

You need to return a 2-dimensional array that matches the range of calling cells (so that you are entering it as a multicell array formula (do not enter the { } just select the cells, enter the formula and press Control-shift-enter)) You are currently returning a 1-dimensional array

answered Jul 6, 2011 at 19:34

Charles Williams

23.6k5 gold badges40 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jesse Over a year ago

Actually, without any changes to the function, and just removing the braces {} from the formula, it worked perfectly. Thanks!

Collectives™ on Stack Overflow

Output array from VBA function to Excel sheet using formula

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related