RE-manipulation of an array from a UDF

[simonphillips]

Hi Everyone,

I have a UDF that returns an array. The function call in a single cell is =returnSimonsarray(variables here).

Once the function completes it populates the single cell and all the cells surrounding it as expected. It also places a light blue border around the cells and includes the same formula in the formula bar but in light grey for any cell in the array except the top left corner. All good.

I make a lot of these array calls and would like to avoid changing the array if a different cell is set to say '1'. However when I pass the cells of the array returned from the fist pass of the UDF in the second call of the UDF they are all seen as empty ?

Looking for some expert wisdom on how to adapt my UDF with some form of switch so that I can choose whether to keep or change the values in the cells from a past function call when I re-evaluate a cell or hit save.

TIA

Simon

 

[StephenCrump]

It sounds like you need to pass the other cell to the UDF as a second argument.

But it's not at all clear how you're calling the UDF, and what you mean by first pass and second call.

Can you please post your code and your layout so we can see?

It looks like you're using Excel 365 or 2021. It would be good if you updated your Account details to confirm, as the best solutions often vary depending on Excel version.

 

[simonphillips]

Hi @StephenCrump,

As always, thank you for taking the time to read my post and offer some support, it s gratefully appreciated.

My excel version is: Microsoft® Excel® for Microsoft 365 MSO (Version 2404 Build 16.0.17531.20140) 64-bit

The call to my UDF is like this: =LookupExternalData(AQ13,$A$17:$A$101, AQ15:BD15,AR13)

The UDF is defined as follows:

Option Explicit

Public Function LookupExternalData( _
ByVal FilePath As String, _
ByVal ProjectArray As Range, _
ByVal ColumnArray As Range, _
ByVal Macro_switch As Integer) _
As Variant

I would like to be able to add a variable to the function call (but this is not in the definition above) that avoids recalculation and return of the array. The UDF currently retruns an array of data (14 x 80) cells. However it seems excel sees the array as a "contiguous"data set (blue border around the cell. So if this additional variable passed to the UDF is set I would like to rewrite the function to not calculate the array. However when I have tried this it clears the full array and returns a single value for cell where the function is called.

I was considering adding code to copy and paste the cells to another location before checking the variable to recalculate. That way if the variable was set to not recalculate then I could copy the values and return them in the array call, but I am not sure if that would work ?

Thanks again and please feel free to ask any more questions

Simon

 

[StephenCrump]

The UDF currently retruns an array of data (14 x 80) cells. However it seems excel sees the array as a "contiguous"data set (blue border around the cell. So if this additional variable passed to the UDF is set I would like to rewrite the function to not calculate the array. However when I have tried this it clears the full array and returns a single value for cell where the function is called.

OK, the results spill, as you'd expect using Excel 365.

I would like to be able to add a variable to the function call (but this is not in the definition above) that avoids recalculation and return of the array .... However when I have tried this it clears the full array and returns a single value for cell where the function is called.

Again, this is expected behaviour. You're calling the Function from a cell, so If your function doesn't define LookupExternalData, you'd expect to see the cell showing the single value 0. You could initialise LookupExternalData to any other value if you wish, e.g. "" or "-".

Perhaps (I am only guessing) you are looking instead to call a Sub (using similar arguments) that, depending on the value of the "different cell", either:

1. Populates a cell with the results of the function, or
2. Does nothing, i.e. the cell is not populated with any formula/result?

 

[simonphillips]

Hi Stephen,

I think you are correct in your comments regarding the Sub.

My goal was to have a function call that could do one of 2 things: return a new array of (recalculated) values or leave the existing array untouched (from the last function call) intact.

But I don't believe that is possible with a UDF for a couple of reasons:
1 - If I try and copy/paste the values the UDF fails because it can only impact the cell from where the function is called (I even tried callin a sub from inside the UDF)
2 - If I reference the "spilled" cells I get a circular reference

I need to *somehow* use a sub routine (?) to copy and paste the array and then pass this to the function. Then with an additional variable in the function call that determines which of the two options above is needed the function returns a new array with different values or the old array.

Is this making sense ?

 

[StephenCrump]

Again, a little hard to say definitively without seeing the detail of your code. But rather than calling a function iteratively, can't you handle everything in the one function call?

Function returnSimonsarray(r As Range, Process As Variant) As Variant

    Dim tmp As Variant
    Dim i As Long, j As Long
    
    'Get Array
    tmp = r.Value

    If Process = 1 Then
        'Process Array - optional
        For i = 1 To UBound(tmp)
            For j = 1 To UBound(tmp, 2)
                tmp(i, j) = tmp(i, j) * 2 'say
            Next j
        Next i
    End If
    
    returnSimonsarray = tmp

End Function

	A	B	C	D	E	F	G	H	I
1	Process Array?	1					1	2	3
2							4	5	6
3	Results	2	4	6			7	8	9
4		8	10	12
5		14	16	18
Sheet1

Cell Formulas
Range	Formula
B3:D5	B3	=returnSimonsarray(G1:I3,B1)
Dynamic array formulas.

 

[simonphillips]

Thanks Stephen, your suggestion helped me to solve my problem !

 

[StephenCrump]

Excellent, I'm glad we could point you in the right direction.

 

[drpdrpdrp]

@StephenCrump is there a reason (performance?) to use or not use “For Each Cell” in these cases? Looping through elements of an array. Thanks

 

[StephenCrump]

... to use or not use “For Each Cell” in these cases?

Not sure what "these cases" are?

I certainly use For Each to loop through ranges sometimes.

But in this particular case, where we want the function to output an array equal in size to the input range, it made sense to loop by row and column so we could get the right dimensions.

I loaded the entire range into a VBA array, rather than reading the range cell by cell, as it should be faster, although the speed difference won't be noticeable if the range is relatively small.