Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 Functio ------------------------------------------------------------------------------------ Your suggestions would be greatly appreciated. Thank you. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate a Worksheet from a Custom Function ??
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate a Worksheet from a Custom Function ??
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate a Worksheet from a Custom Function ??
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 , "monir" wrote: The following simple custom function returns #Value! The function does not appear to activate mySheet, nor assigns a value to NumOfRows. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate a Worksheet from a Custom Function ??
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate a Worksheet from a Custom Function ??
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 , "monir" wrote: The following simple custom function returns #Value! The function does not appear to activate mySheet, nor assigns a value to NumOfRows. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to? custom worksheet function using VBA | New Users to Excel | |||
Custom Worksheet Function - Absolute...? | Excel Worksheet Functions | |||
Activate Built-in chart in Custom Types tab | Charts and Charting in Excel | |||
Custom Worksheet Function not updating | Excel Programming | |||
Worksheet Name as argument in Custom Function | Excel Programming |