Monir,

From your post I should have figured that you are using the function to
return value in a cell. Please note that when you use a function in this way,
you are not allowed to change the Excel environment in any way. In other
words, you should not make another cell or sheet activated or even change the
color or formatting of the cell(though I have tried the latter personally).

Alok Joshi

"monir" wrote:

Hello;

The following simple custom function returns #Value!
The function does not appear to activate mySheet, nor assigns a value to
NumOfRows.

------------------------------------------------------------------------------------
Function GEL_Reg_1 (mySheetIndex, arg1, arg2, arg3, arg4, arg5, arg6)
' mySheetIndex : from 1 to 16, is the index of the applicable w/s in this w/b
' NumOfRows : value bet ~200 to 600, is stored in cell C14 in mySheet. It
is the
' number of rows of coefficients, starting row 21 of mySheet. Each row has 7
' values; col C to col I. Values in col C are multipliers, so apply them
after the
' inner loop is complete.
'
Dim NumOfRows As Integer, myI As Integer, myJ As Integer
Dim mySum, mySumR
Dim mySheet As Worksheet
'
' create an array for the input parameters
myParm = Array (arg1, arg2, arg3, arg4, arg5, arg6)
'
' activate the w/s that has the relevant coefficients
Set mySheet = Worksheet (mySheetIndex)
mySheet.Activate
NumOfRows = Range ("C14")

mySum = 0
For myI = 1 To NumOfRows
myReg = Array (Cells(21 + myI - 1,3), Cells(21 + myI - 1,4), ...,
Cells(21 + myI - 1,9) )
mySumR = 1
For myJ = 1 To 6
mySumR = mySumR * myParm (myJ) ^ myReg (myJ + 1)
Next myJ
mySum = mySum + myReg(1) * mySumR
Next myI
GEL_Reg_1 = mySum
End Function
------------------------------------------------------------------------------------

Your suggestions would be greatly appreciated. Thank you.