CODEWITHSUNDEEP
vbaexcel
Robert S.
Robert S.

Reputation: 115

Function Return Value Not Updating

I have an Excel workbook with multiple sheets and I am trying to run a test function to return a cell value that gets updated based on values from a different sheet. Here is my basic function which simply returns a value from an array:

Function test(t2)
    Application.Volatile
    Dim costArray As Variant
    costArray = Array(Range("W34"), Range("W35"), Range("W36"))
    test = costArray(1)
End Function

This properly returns the correct value, but when I update a dependency cell value from the different sheet, the return value gets set to 0 and I have to manually refresh the function to get the updated value. As you can see, I have tried to use the Application.Volatile method, but does not seem to help.

Any thoughts?

Upvotes: 1

Views: 278

Answers (1)

Vityata
Vityata

Reputation: 43565

Try like this:

Public Function TestMe(ParamArray costArray() As Variant) As Variant
    Application.Volatile
    TestMe = costArray(1)
End Function

I guess that the Application.Volatile concerns only the parameters. Thus, give the three parameters as a range.

The ParamArray specifies that a procedure parameter takes an optional array of elements of the specified type. ParamArray can be used only on the last parameter of a parameter list.

https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/modifiers/paramarray

Upvotes: 2

Related Questions