How to display a 1 or 2 dim varraint array() return from an excel UDF function in an excel range without Ctrl+Shift+Enter array forumla method

Question

I've searched for solutions to this question for 3 days & have only found answers that either use Ctrl+Shift+Enter (array formula method) or functions which either return an empty range or which raise err 1004.

I'm using 64-bit Windows 8.1 and Excel-2013 write UDFs that return variant arrays from time to time with unknown array size returns... for example 'MyFunction(args...) as Variant()'. I can see the result in immediate window or write it to file or display it with Ctrl+Shift+Enter as an array formula.

What I want to do is use MyFunction() as an argument to a sub arr2rng(Myfunction) such that arr2rng() fills a range on the activesheet starting at ActiveCell inorder to avoid the whole manual routine of using array formula method (e.g. highlight a range of some size larger, by guessing, than the returned array, then the combination Ctrl+Shift+Enter to display the array).

I've even tried the long subroutine by Nile -- see his A generic VBA Array To Range function at VBA Excel 2-Dimensional Arrays,

Public Sub ArrayToRange(rngTarget As Excel.Range, InputArray As Variant)

but at every statement in 'ArrayToRange()' where 'rngOutput.Value2 = InputArray' occurs function bombs with err.Number 1004. Just before that statement is executed both 'InputArray' and 'rngOutput.VaAlue2' elements are correctly dimensioned and filled (from Immediate or Local Window observations)... though 'rngOutput.Value2'elments are still empty as they should be. After the statement executes 'rgnOutput.value2' elements are still empty though, and err = 1004 has been raised. This occurs no matter which one of his tests in the code are executed. I've even gone so far as to invoke his sub by my function at the end of my own VBA's as:

myFunction(args....) as Variant()
[do stuff...]
ConArr = vbaTransposeVar(ConArr)
Set DestCell = ACTIVE_CELL_DESTINATION
ArrayToRange DestCell, ConArr
myFunction = ConArr
End Function

where 'ConArr' is the name of the resultant variant() array and also the return from myFunction(), where 'ACTTIVE_CELL_DESTINATION' is declared Public as Range in the Module.

What I prefer to do however is just invoke the 'sub ArrayToRange myDest, myArray' or any other sub such as a generic 'sub arr2rng(myDest as range, myArray() as Variant)' either from within some other function that invokes the sub or do it manually from the Macro window.

Can anybody help or tell me why all I get are either an empty range of cells or the 1004 error? I guess what I'm really asking is how to get around using the array formula method Ctrl+Shift+Enter. There must be a way! u

A function called as a UDF from a worksheet can only reign values to the cell(s) containing the calling formula: that is a well-known limitation (at least it is here, where it comes up pretty regularly). There are work-arounds for this which you could investigate to do what you want. For example: http://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet/23437280#23437280 — Tim Williams, Oct 23 '16 at 23:43
@TimWilliams - I was intrigued by what "reign" meant in this context! I was thinking either "assign" or "return", and wondering if you split the difference and formed a new word. (Or, at least, a new meaning for an existing word. Long live the Queen!!) — YowE3K, Oct 24 '16 at 02:08
@Tim Williams - Thank-you very much ... for the answer & link.. I now vaguely recall that I probably (certainly) came across this limiting condition years ago at work but just used the 'array formula' method to _get my work done_ thinking sooner or later I'd look for and find the "Excel VBA" provided alternative. So it's at least a decade later & since I'm now retired I've spent the time to "find" the solution to no avail — Longtooth, Oct 24 '16 at 06:19
@TimWilliams - I followed the link. What it seems to boil down to is that Micrsoft's code will invoke a UDF's return array to display a range of cells the user selects manually (where the selected range's origin includes the UDF) by recognizing the keystroke combination Ctrl+Shift+Enter, but the developers won't let a UDF or sub do the same (defining a range & then invoke what-ever code the Ctrl+Shift+Enter combo get's pointed to). Thanks much for your kind help. — Longtooth, Oct 24 '16 at 07:21
@TimWilliams After months of letting this problem fester and being resigned to the fact that it's not reliably possible by MS Excel design, the thought suddenly occurred to me that an effective reliable work-around might be: Use a named variable within a UDF that calls a Sub() with the variable as a ByRef arg to the Sub(). Then whatever the sub did with the variable's contents would be "effectively returned" to the UDF with the changed contents to the named ByRef variable. I haven't tried it yet though... just a sudden "ah hah" thought moment. — Longtooth, Dec 05 '16 at 17:27
There are (unsupported and use-at-your-own-risk) workarounds to the UDF constraints. E.g. http://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet/23437280#23437280 You might be able to use that to do what you want. — Tim Williams, Dec 05 '16 at 17:35
@TimWilliams: I've looked at the linked questions you provide as late as last week again (as well as before I suBmitted this question). They either don't work reliably or don't solve the problem. Not a solution, but thank-you for trying to help me.. — Longtooth, Dec 05 '16 at 19:13

score 0 · Answer 1 · answered Dec 05 '16 at 19:44

This example worked for me in testing, BUT there is no code to account for clearing any previous values which resulted from any earlier calculations. If the current run results in a smaller array then previous values will not be cleared.

You could account for this maybe by tracking the last range filled and making sure you clear it first, but depending on your exact use case that might not be viable.

Function ChangeIt(func As Range, c1 As Range, c2 As Range)
    Dim arr(), r, c, rv, v

    For r = 1 To c1
    For c = 1 To c2
        v = "Row" & r & ":Col" & c
        If r = 1 And c = 1 Then
            rv = v 'return this to the calling cell via myArray
        Else
            'all other values are written directly
            func.Offset(r - 1, c - 1).Value = v
        End If
    Next c
    Next r

    ChangeIt = rv
End Function

'this is called from the worksheet
Function MyArray(arg1 As Range, arg2 As Range)

    Dim v

    v = arg1.Parent.Evaluate("Changeit(" & Application.Caller.Address(False, False) & "," & _
                                           arg1.Address(False, False) & "," & _
                                           arg2.Address(False, False) & ")")

    MyArray = v 'set the top-left array value

End Function

How to display a 1 or 2 dim varraint array() return from an excel UDF function in an excel range without Ctrl+Shift+Enter array forumla method

1 Answers1