| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Unexpectedly calls a Function
I can't figure this problem out and am getting quite frustrated and hope that
someone can HELP, PLEASE!! My environment is: Windows XP Version 2002 Service Pack 2 and MS Office Excel 2003 SP2. My workbook has three worksheets with many hyperlinks in each worksheet. I developed a user defined function (UDF) that exists in only the last worksheet. The purpose of the UDF counts the cells within the last worksheet based on the fill color. I wrote another program (ListHyplinks) to list the hyperlinks within each worksheet. It cycles through each worksheet and lists the hyperlinks in a Hyperlinks worksheet. Here is the UDF function line, which is stored in a module named: ContColr. Function CntClr(InRange As Range, ColorIndex As Long, Optional OfText As Boolean = False) As Long Here is part of the Hyperlink list program, which is stored in a module named: Module2 Sub ListHyplinks() Option Explicit (((( There are various declaration statements that go here )))) totwksnbr = Worksheets.Count wrbkname = ActiveWorkbook.Name Firstwks = True icnt = 1 Cycle through each worksheet For wksnbr = 1 To totwksnbr curwksname = Worksheet(wksnbr).Name Answer = Msgbox(curwksname & is the current worksheet. _ & vbCr & vbCr & Do you want to list the links?, vbYesNo, List Hyperlinks) If Answer = vbYes Then If(Firstwks) Then Sheets.Add(after:=Worksheets(Worksheets.Count)).Na me = Hyperlinks Firstwks = False Call WriteHeading For Each h In ActiveSheet.Hyperlinks Cells(icnt, 1) = h.address icnt = icnt + 1 Next (((( Additional statements go here )))) When I run the ListHyplinks program control jumps to Function CntClr after creating the "Hyperlinks" worksheet in the "If (Firstwks)" statement. It cycles through that function for a bit and then returns. Then upon executing the "Cells(icnt, 1) = h.address" statement control once jumps to the Function CntClr again, cycles through and returns. To investigate this phenomenon I performed the following debug process: Scenario 1: Set debug breakpoint @ "If Answer = vbYes" Ran the ListHyplinks program Step to the "If (Firstwks) statement "Hyperlinks" worksheet is created Unexpectedly control then jumps to Function CntClr I stop the program Scenario 2: After completing Scenario 1 "Hyperlinks" worksheet still exists and is cleared out I comment out the "If (Firstwks)" statement Re-ran the ListHyplinks program using the same debug breakpoint Step through the program Once it reaches "Cells(icnt, 1) = h.address" control jumps to Function CntClr I stop the program Scenario 3: After completing Scenario 2 "Hyperlinks" worksheet still exists and is cleared out Re-ran the ListHyplinks program using the same debug breakpoint The program runs as intended without control jumping to Function CntClr???? Any assistance in resolving this issue would be greatly appreciated. Thank you in advance for your time and efforts. Steve |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro Unexpectedly calls a Function
Along the lines of stopping everything else, it is useful to set this code at the top and then undo it at the bottom of your code: ----------- Application.EnableEvents = False ----------- That turns off all other code until your macro is done running, be SURE to turn it back on. If you have error traps that let the code exit in various spots, put the True statement in all of them. Just make sure it gets turned back on. -- JBeaucaire ------------------------------------------------------------------------ JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=47926 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro Unexpectedly calls a Function
Steve,
It is a curious thing, but I posted a similar case than yours January, 8th. I bed you have't fixed the problem yet... Application.EnableEvents has nothing to do with this....(I admit it was my first point to check) In fact trying to debug (in my case) has no sense since the flow is 'managed' by something apparently NOT LOGICAL. It goes to the UDF but instead of doing all steps it cycles to just a few of them (several times) and return to original position without reaching End Function statment. I've been waiting an answer but I'm afraid this Forum is not what it use to be... sadly for all. I found a workaround which consists in replacing the formulas where UDF is called with another name, say CntClr changes to KntClr, and when the UDF is really needed giving back the real name in the formulas where it was replaced. But I still wait for an answer, since I noticed this issue in many Excel VBA projects. HTH, Sharon "Steve" wrote: I can't figure this problem out and am getting quite frustrated and hope that someone can HELP, PLEASE!! My environment is: Windows XP Version 2002 Service Pack 2 and MS Office Excel 2003 SP2. My workbook has three worksheets with many hyperlinks in each worksheet. I developed a user defined function (UDF) that exists in only the last worksheet. The purpose of the UDF counts the cells within the last worksheet based on the fill color. I wrote another program (ListHyplinks) to list the hyperlinks within each worksheet. It cycles through each worksheet and lists the hyperlinks in a Hyperlinks worksheet. Here is the UDF function line, which is stored in a module named: ContColr. Function CntClr(InRange As Range, ColorIndex As Long, Optional OfText As Boolean = False) As Long Here is part of the Hyperlink list program, which is stored in a module named: Module2 Sub ListHyplinks() Option Explicit (((( There are various declaration statements that go here )))) totwksnbr = Worksheets.Count wrbkname = ActiveWorkbook.Name Firstwks = True icnt = 1 Cycle through each worksheet For wksnbr = 1 To totwksnbr curwksname = Worksheet(wksnbr).Name Answer = Msgbox(curwksname & is the current worksheet. _ & vbCr & vbCr & Do you want to list the links?, vbYesNo, List Hyperlinks) If Answer = vbYes Then If(Firstwks) Then Sheets.Add(after:=Worksheets(Worksheets.Count)).Na me = Hyperlinks Firstwks = False Call WriteHeading For Each h In ActiveSheet.Hyperlinks Cells(icnt, 1) = h.address icnt = icnt + 1 Next (((( Additional statements go here )))) When I run the ListHyplinks program control jumps to Function CntClr after creating the "Hyperlinks" worksheet in the "If (Firstwks)" statement. It cycles through that function for a bit and then returns. Then upon executing the "Cells(icnt, 1) = h.address" statement control once jumps to the Function CntClr again, cycles through and returns. To investigate this phenomenon I performed the following debug process: Scenario 1: Set debug breakpoint @ "If Answer = vbYes" Ran the ListHyplinks program Step to the "If (Firstwks) statement "Hyperlinks" worksheet is created Unexpectedly control then jumps to Function CntClr I stop the program Scenario 2: After completing Scenario 1 "Hyperlinks" worksheet still exists and is cleared out I comment out the "If (Firstwks)" statement Re-ran the ListHyplinks program using the same debug breakpoint Step through the program Once it reaches "Cells(icnt, 1) = h.address" control jumps to Function CntClr I stop the program Scenario 3: After completing Scenario 2 "Hyperlinks" worksheet still exists and is cleared out Re-ran the ListHyplinks program using the same debug breakpoint The program runs as intended without control jumping to Function CntClr???? Any assistance in resolving this issue would be greatly appreciated. Thank you in advance for your time and efforts. Steve |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro Unexpectedly calls a Function
Sharon,
You are right in that Application.EnableEvents did/does not correct the problem, BUT turning off automatic calculation DID just the trick. Thank you Nigel !!!! At the beginning of my subroutine I turned off the automatic calculation: Application.Calculation = xlCalculationManual And just before exiting I turned it back on. Application.Calculation = xlCalculationAutomatic The program is now very fast. By the way, I also ran into another condition that was/is equally frustrating. The Function name cannot be the same name as the Module name. When they are, the cells that contain the function get confused and report NAME? error. But, when the Function and Module name are different there is no ambiguity issues. However, this condition is not true for regular Subroutine/Module names, don't know why, but that is what I've found to be true. HTH ... Steve The module name of the "sharon" wrote: Steve, It is a curious thing, but I posted a similar case than yours January, 8th. I bed you have't fixed the problem yet... Application.EnableEvents has nothing to do with this....(I admit it was my first point to check) In fact trying to debug (in my case) has no sense since the flow is 'managed' by something apparently NOT LOGICAL. It goes to the UDF but instead of doing all steps it cycles to just a few of them (several times) and return to original position without reaching End Function statment. I've been waiting an answer but I'm afraid this Forum is not what it use to be... sadly for all. I found a workaround which consists in replacing the formulas where UDF is called with another name, say CntClr changes to KntClr, and when the UDF is really needed giving back the real name in the formulas where it was replaced. But I still wait for an answer, since I noticed this issue in many Excel VBA projects. HTH, Sharon "Steve" wrote: I can't figure this problem out and am getting quite frustrated and hope that someone can HELP, PLEASE!! My environment is: Windows XP Version 2002 Service Pack 2 and MS Office Excel 2003 SP2. My workbook has three worksheets with many hyperlinks in each worksheet. I developed a user defined function (UDF) that exists in only the last worksheet. The purpose of the UDF counts the cells within the last worksheet based on the fill color. I wrote another program (ListHyplinks) to list the hyperlinks within each worksheet. It cycles through each worksheet and lists the hyperlinks in a Hyperlinks worksheet. Here is the UDF function line, which is stored in a module named: ContColr. Function CntClr(InRange As Range, ColorIndex As Long, Optional OfText As Boolean = False) As Long Here is part of the Hyperlink list program, which is stored in a module named: Module2 Sub ListHyplinks() Option Explicit (((( There are various declaration statements that go here )))) totwksnbr = Worksheets.Count wrbkname = ActiveWorkbook.Name Firstwks = True icnt = 1 Cycle through each worksheet For wksnbr = 1 To totwksnbr curwksname = Worksheet(wksnbr).Name Answer = Msgbox(curwksname & is the current worksheet. _ & vbCr & vbCr & Do you want to list the links?, vbYesNo, List Hyperlinks) If Answer = vbYes Then If(Firstwks) Then Sheets.Add(after:=Worksheets(Worksheets.Count)).Na me = Hyperlinks Firstwks = False Call WriteHeading For Each h In ActiveSheet.Hyperlinks Cells(icnt, 1) = h.address icnt = icnt + 1 Next (((( Additional statements go here )))) When I run the ListHyplinks program control jumps to Function CntClr after creating the "Hyperlinks" worksheet in the "If (Firstwks)" statement. It cycles through that function for a bit and then returns. Then upon executing the "Cells(icnt, 1) = h.address" statement control once jumps to the Function CntClr again, cycles through and returns. To investigate this phenomenon I performed the following debug process: Scenario 1: Set debug breakpoint @ "If Answer = vbYes" Ran the ListHyplinks program Step to the "If (Firstwks) statement "Hyperlinks" worksheet is created Unexpectedly control then jumps to Function CntClr I stop the program Scenario 2: After completing Scenario 1 "Hyperlinks" worksheet still exists and is cleared out I comment out the "If (Firstwks)" statement Re-ran the ListHyplinks program using the same debug breakpoint Step through the program Once it reaches "Cells(icnt, 1) = h.address" control jumps to Function CntClr I stop the program Scenario 3: After completing Scenario 2 "Hyperlinks" worksheet still exists and is cleared out Re-ran the ListHyplinks program using the same debug breakpoint The program runs as intended without control jumping to Function CntClr???? Any assistance in resolving this issue would be greatly appreciated. Thank you in advance for your time and efforts. Steve |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro Unexpectedly calls a Function
Hi Steve,
It works fine after setting calculation to Manual. I must admit I should think of that possibility first of all, but my head was overheatted... Thanks a lot. BTW, Should you rate Nigel's post? Sharon, "Steve" wrote: Sharon, You are right in that Application.EnableEvents did/does not correct the problem, BUT turning off automatic calculation DID just the trick. Thank you Nigel !!!! At the beginning of my subroutine I turned off the automatic calculation: Application.Calculation = xlCalculationManual And just before exiting I turned it back on. Application.Calculation = xlCalculationAutomatic The program is now very fast. By the way, I also ran into another condition that was/is equally frustrating. The Function name cannot be the same name as the Module name. When they are, the cells that contain the function get confused and report NAME? error. But, when the Function and Module name are different there is no ambiguity issues. However, this condition is not true for regular Subroutine/Module names, don't know why, but that is what I've found to be true. HTH ... Steve The module name of the "sharon" wrote: Steve, It is a curious thing, but I posted a similar case than yours January, 8th. I bed you have't fixed the problem yet... Application.EnableEvents has nothing to do with this....(I admit it was my first point to check) In fact trying to debug (in my case) has no sense since the flow is 'managed' by something apparently NOT LOGICAL. It goes to the UDF but instead of doing all steps it cycles to just a few of them (several times) and return to original position without reaching End Function statment. I've been waiting an answer but I'm afraid this Forum is not what it use to be... sadly for all. I found a workaround which consists in replacing the formulas where UDF is called with another name, say CntClr changes to KntClr, and when the UDF is really needed giving back the real name in the formulas where it was replaced. But I still wait for an answer, since I noticed this issue in many Excel VBA projects. HTH, Sharon "Steve" wrote: I can't figure this problem out and am getting quite frustrated and hope that someone can HELP, PLEASE!! My environment is: Windows XP Version 2002 Service Pack 2 and MS Office Excel 2003 SP2. My workbook has three worksheets with many hyperlinks in each worksheet. I developed a user defined function (UDF) that exists in only the last worksheet. The purpose of the UDF counts the cells within the last worksheet based on the fill color. I wrote another program (ListHyplinks) to list the hyperlinks within each worksheet. It cycles through each worksheet and lists the hyperlinks in a Hyperlinks worksheet. Here is the UDF function line, which is stored in a module named: ContColr. Function CntClr(InRange As Range, ColorIndex As Long, Optional OfText As Boolean = False) As Long Here is part of the Hyperlink list program, which is stored in a module named: Module2 Sub ListHyplinks() Option Explicit (((( There are various declaration statements that go here )))) totwksnbr = Worksheets.Count wrbkname = ActiveWorkbook.Name Firstwks = True icnt = 1 Cycle through each worksheet For wksnbr = 1 To totwksnbr curwksname = Worksheet(wksnbr).Name Answer = Msgbox(curwksname & is the current worksheet. _ & vbCr & vbCr & Do you want to list the links?, vbYesNo, List Hyperlinks) If Answer = vbYes Then If(Firstwks) Then Sheets.Add(after:=Worksheets(Worksheets.Count)).Na me = Hyperlinks Firstwks = False Call WriteHeading For Each h In ActiveSheet.Hyperlinks Cells(icnt, 1) = h.address icnt = icnt + 1 Next (((( Additional statements go here )))) When I run the ListHyplinks program control jumps to Function CntClr after creating the "Hyperlinks" worksheet in the "If (Firstwks)" statement. It cycles through that function for a bit and then returns. Then upon executing the "Cells(icnt, 1) = h.address" statement control once jumps to the Function CntClr again, cycles through and returns. To investigate this phenomenon I performed the following debug process: Scenario 1: Set debug breakpoint @ "If Answer = vbYes" Ran the ListHyplinks program Step to the "If (Firstwks) statement "Hyperlinks" worksheet is created Unexpectedly control then jumps to Function CntClr I stop the program Scenario 2: After completing Scenario 1 "Hyperlinks" worksheet still exists and is cleared out I comment out the "If (Firstwks)" statement Re-ran the ListHyplinks program using the same debug breakpoint Step through the program Once it reaches "Cells(icnt, 1) = h.address" control jumps to Function CntClr I stop the program Scenario 3: After completing Scenario 2 "Hyperlinks" worksheet still exists and is cleared out Re-ran the ListHyplinks program using the same debug breakpoint The program runs as intended without control jumping to Function CntClr???? Any assistance in resolving this issue would be greatly appreciated. Thank you in advance for your time and efforts. Steve |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro unexpectedly jumps to other macros and functions Options | Excel Programming | |||
| Using ODBC function calls | Excel Programming | |||
| VBA: How to pass arrays in Function Calls? | Excel Programming | |||
| Macro unexpectedly jumps to other macros and functions | Excel Programming | |||
| function calls | Excel Programming | |||
Hybrid Mode
