Goalseek fails when called from a subroutine

[JohnKellett]

I have a macro which I use to do multiple Goalseeks in one go (one at a time). It normally works a treat, but in the model I'm working on at the moment, it's failing, because there are some UDFs in the sheet which are evaluating to #VALUE as soon as I run my macro. Goalseek works fine when I call it manually.
Any ideas how to get around this?
Thanks!
JK

 

[Andrew Poulsom]

Welcome to MrExcel.

What are the UDFs?

 

[JohnKellett]

Just a couple of interpolation functions (and they aren't in the goalseek loop).
thanks,
JK

 

[Andrew Poulsom]

Why are they returning #VALUE!?

 

[JohnKellett]

I don't know -- they shouldn't be, which is the problem... They're all absolutely fine until I try and call a goalseek from VBA.
I've knocked up an example, is it possible to upload it to the forum?
Thanks,
JK

 

[Andrew Poulsom]

You can put the workbook on a share like Box.com and post the URL.

 

[JohnKellett]

Ok, I've uploaded it to Box.com as you suggest:

https://app.box.com/s/yrjyxgkpp67mqo8pqph2

If you use goalseek (from the ribbon) to set C6 to zero by changing C4, then it works fine. But if you run this code...

Range("C6").GoalSeek Goal:=0, ChangingCell:=Range("C4")

... then the yellow interpolation cells in row 2 go to #VALUE! and the goalseek fails. Any idea why?
Thanks for your help!
JK

 

[Andrew Poulsom]

It's this:

Application.Volatile

You really shouldn't use Application.Caller in a function. And why do you need Goal Seek for this?

 

[JohnKellett]

I didn't write the original macro, so I don't know why Application.volatile was there – I don't think it needs it. I've taken it out and it's fixed my problem without any bad consequences that I can see.
The file I posted was just a mock-up with a trivial example to demonstrate the problem – my actual model is much more complex (hence the need for goalseek).
Thanks for your help!
JK

 

[shg]

You might try replacing that function with this:

Function Fill(vd1 As Variant, vd2 As Variant, Optional Strength As Double = 0)
    Const Pi        As Double = 3.14159265358979
    Dim iCol        As Long
    Dim nCol        As Long
    Dim adFill()    As Double

    nCol = Application.Caller.CurrentArray.Columns.Count

    ReDim adFill(1 To nCol)

    For iCol = 1 To nCol
        adFill(iCol) = vd1 + (vd2 - vd1) * (iCol / (nCol + 1) + _
                                            Strength / Pi * Sin(iCol / (nCol + 1) * Pi))
    Next iCol

    Fill = adFill
End Function