Function doesn't calculate
 

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?

Function doesn't calculate
 

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

eg Private Function Near (Actual as Long) As Long

Cheeri

--
Message posted from http://www.ExcelForum.com

Function doesn't calculate
 

add this line to your function:

Application.Volatile

Patrick Molloy
Microsoft Excel MVP
-----Original Message-----
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?

.

Function doesn't calculate
 

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

"Rob" wrote in message
...
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?