CODEWITHSUNDEEP
vbaexcel
bill_080
bill_080

Reputation: 4760

Excel VBA function that returns an answer only when viewed

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?

Upvotes: 1

Views: 253

Answers (1)

Tim Williams
Tim Williams

Reputation: 166531

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

Upvotes: 2

Related Questions