Function doesn't calculate

[Rob]

I've defined a function in VBA to randomly generate a
number "close to" its argument.
It uses a formula on a hiden sheet, which gives a random
number between 0.666 and 1.333 to multiply the argument by.

Function Near(Actual)
Sheets("hiden").Cells.Calculate
Near = Actual * Sheets("hiden").Cells(1, 1)
End Function

Sub test()
Sheets("hiden").Cells(1, 1) _
.FormulaR1C1 = "=(rand()-0.5)/3+1"
MsgBox Near(100)
End Sub

The test procedure above works, but if I enter the
formula "=near(100)" into a cell on my worksheet and fill
the formula down a few rows, it doesn't calculate the
values. Does anyone know why this is?

 

[Etien]

Rob, I'm not an expert (far from it), but try declaring all variables

eg Private Function Near (Actual as Long) As Long

Cheeri

 

[Patrick Molloy]

add this line to your function:

Application.Volatile

Patrick Molloy
Microsoft Excel MVP

 

[Charles Williams]

Hi Rob,

Excel will not let you use the calculate method inside a worksheet function
because it would change something in the excel environment.

You could try using the evaluate method (evaluates a string as if it was a
formula) instead:

Function Near(Actual)
Near = Actual * Sheets("hiden").evaluate(Sheets("hiden").Cells(1,
1).formula)
End Function

You could simplify this by putting the formula directly inside the
evaluate()

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm