Activate a Worksheet from a Custom Function ??

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.

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

"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.

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.

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.





In article <[email protected]>,
"monir" <[email protected]> wrote:

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

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.

"Alok" wrote:

> 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.

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.



"JE McGimpsey" wrote:

> 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.
>
>
>
>
>
> In article <[email protected]>,
> "monir" <[email protected]> wrote:
>
> > The following simple custom function returns #Value!
> > The function does not appear to activate mySheet, nor assigns a value to
> > NumOfRows.
>