Activate a Worksheet from a Custom Function ??

[Guest]

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 Functio

 

[Guest]

Are you using this function in your code or to return value in a cell like
the other built in functions in excel.

Alok Joshi

 

[Guest]

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

 

[JE McGimpsey]

Worksheet functions, including User Defined Functions called from the
worksheet, can only return values to their calling cells. They can't
change values in other cells, nor can they activate anything.

You can address the ranges in your tables directly, but you can't select
them. This might give you a start (untested):

Public Function GEL_Reg_2(mySheetIndex, _
arg1, arg2, arg3, arg4, arg5, arg6)
Dim myParm As Variant
Dim mySheet As Worksheet
Dim myReg As Range
Dim NumOfRows As Long
Dim i As Long
Dim j As Long
Dim mySumR As Double
Dim mySum As Double

myParm = Array(arg1, arg2, arg3, arg4, arg5, arg6)
Set mySheet = Worksheets(mySheetIndex)
With mySheet
NumOfRows = .Range("C14").Value
For i = 1 To NumOfRows
Set myReg = .Cells(21 + i - 1, 3).Resize(1, 7)
mySumR = 1
For j = 1 To 6
mySumR = mySumR * myParm(j) ^ myReg(1, j + 1)
Next j
mySum = mySum + myReg(1, 1) * mySumR
Next i
End With
GEL_Reg_2 = mySum
End Function


Note that, by default, Array() returns a zero based array unless you
have Option Base 1 at the top of your module.

 

[Guest]

Alok;

I knew that certain actions can not be executed from a custom Function's
code, such as open a file! But I was not aware that activating a w/s (in
this w/b) or even selecting a cell in a different w/s are not permitted!

JE McGimpsey in his response provided an excellent idea by addressing the
cells directly without selecting them. You may wish to review his/her
response.

Regards.

 

[Guest]

JE McGimpsey;

Brilliant workaround idea ! addressing the cells directly without Selecting
or Activating anything outside the w/s where the Function is used!

Your version of the Function's code works perfectly! I simply added OPTION
BASE 1 at the top of the module, and replaced:
......NumOfRows = .Range("C14").Value (compile error) by
......NumOfRows = .Range("C14").Value

Thank you kindly for your help. Greatly appreciated.