Excel VBA function that returns an answer only when viewed

Question

I'm using Excel 2007. I have the following function in an Excel VBA module:

Function test(Loc, Dis)

    Dim StaRan As String
    Dim EndRan As String

    Application.Volatile

'   Build the Start Range and End Range strings

    StaRan = Loc.Offset(-Dis, 0).Address
    EndRan = Loc.Offset(Dis, 0).Address

'   Feed the range strings to Excel's Average function to get the answer

    test = WorksheetFunction.Average(Range(StaRan & ":" & EndRan))

End Function

When inserted into an Excel worksheet, the test function uses the distance Dis as a plus/minus row count to offset the location Loc to build a range that can be used to calculate an average. The worksheet is set for Manual Calculation so that it doesn't update until the F9 button is pressed.

In Sheet1 enter the following:

At cell A2: 1

At cell A3: 1

At cell A4: 1

At cell B3: =test(A3,1)

This function works (it returns 1) if you're looking at the worksheet where the function is placed (Sheet1) when you hit F9. When viewing any other worksheet (for example, Sheet2), hit the F9 button to recalculate. Then go back to the original worksheet (Sheet1) and look at what test had returned. It returned #VALUE!. If you hit F9 again while looking at the original worksheet (Sheet1), it returns the right answer.

Is this an Excel bug? Is there a way around this?

Tim Williams · Accepted Answer · 2014-02-23 16:10:19Z

2

Range() refers to the Active sheet unless you qualify it.

This is more direct:

Function test(Loc, Dis)

    Application.Volatile
    test = WorksheetFunction.Average(Loc.Offset(-Dis,0).Resize((2*Dis)+1))

End Function

edited Feb 23, 2014 at 16:10

answered Feb 23, 2014 at 1:03

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

bill_080 Over a year ago

Tim, your solution not only worked, but it's a lot cleaner. Also, there's a 1 missing after the last comma in your test line.

Tim Williams Over a year ago

That last parameter is optional - I left it out in case you meant to use the function with more than one column in the input range. If you leave either the first or second Resize() parameter empty then that dimension is not resized.

bill_080 Over a year ago

When I don't provide a value in that position, I get "Compiler Error: Syntax Error". It may be because I'm using Excel 2007.

Collectives™ on Stack Overflow

Excel VBA function that returns an answer only when viewed

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related