UDF returns an array UNLESS it is put into the cell using code (.Formula)

OaklandJim

Board Regular
Joined
Nov 29, 2018
Messages
77
Office Version
  1. 2019
Platform
  1. Windows
In a summary sheet I use UDFs to get specific data from two data sheets. Need UDFs because sheets accessed may change so I need to use data sheets' Code Name to determine which sheet a formula refers to.

Sometimes my UDFs just do not recalculate. OK, the workbook has lots of code, names, etc. I guess Excel gets bogged down sometimes.

So, I wrote code that iterates offending cells to "refresh" formulas (i.e. rewrite them). That works, sort of.

Except for one of the function calls there is one return value. When I refresh those formulas all works just fine, the correct value is calculated.

The other formula is supposed to return an array of 1 x 24 cells. When I enter the UDF into a worksheet cell "by hand" it works (i.e., spills as expected). BUT if I put the same formula into the same cell using code Excel adds the @ char and refuses to recognize the array, it just shows one -- the leftmost -- value. I even tried to calculate the offending range in code. Nope.

If I remove the @ char from the formula in the cell "by hand" the result spills as expected.

Any suggestions?

This won't help, I suspect, but here is the simple UDF involved.

VBA Code:
Function GetValPortfolio(piIndex As Integer, psName As String) As Variant
    
    Dim sSheet As String
    
    Dim wsSource As Worksheet
    
    If piIndex > 2 Then Exit Function
    
    If piIndex = 1 Then Set wsSource = [Portfolio1]
    If piIndex = 2 Then Set wsSource = [Portfolio2]
    
    GetValPortfolio = ""
    
    On Error Resume Next
    GetValPortfolio = wsSource.Range(psName).Value
    On Error GoTo 0

End Function
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,898
Office Version
  1. 365
Platform
  1. Windows
What is the code that inserts the formula into the cell?
 

OaklandJim

Board Regular
Joined
Nov 29, 2018
Messages
77
Office Version
  1. 2019
Platform
  1. Windows
VBA Code:
'       --- Portfolio Allocations Target ---
        sRangeName = "Header_" & sPortfolio & "Target"
        sFormula = "=GetValPortfolio(" & iPortfolioNum & "," _
                    & """" & "Allocations_Target" & """" & ")"
        With .Range(sRangeName).Offset(0, 1)
            .Formula = sFormula
            .Resize(1, 24).Calculate
        End With
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,898
Office Version
  1. 365
Platform
  1. Windows
Thanks for that, just change .Formula to .Formula2& you should be fine.
 
Solution

OaklandJim

Board Regular
Joined
Nov 29, 2018
Messages
77
Office Version
  1. 2019
Platform
  1. Windows
Thank you Fluff! I appreciate the assistance a lot. I was not aware of .Formula2, now I am.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,898
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
Formula2 (along with Formula2R1C1 etc) only appeared with the advent of the dynamic array functions, so hasn't been around long.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,472
Messages
5,596,354
Members
414,060
Latest member
hermanseck

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top