Function doesn't calculate

  • Thread starter Thread starter Rob
  • Start date Start date
R

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?
 
Rob, I'm not an expert (far from it), but try declaring all variables

eg Private Function Near (Actual as Long) As Long

Cheeri
 
add this line to your function:

Application.Volatile

Patrick Molloy
Microsoft Excel MVP
 
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
 
Back
Top