Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timing Issue??
Hi
I am trying to adapt Chip Pearson's code and insert the same procedure into 2 added worksheets. Strange thing is my code only fails each time I start a new instance of the workbook. Even then, when the code breaks on error with 'Subscript out of Range' and I click to continue in Debug mode the code completes without error AND the new module is installed correctly in both new worksheets. Within the same instance of the workbook and beyond the initial 'block' the procedure works without hesitation repeatedly. When creating the second of the 2 worksheets, the code never fails whether it is in the first instance of the workbook or not. The fact that the identical code works on the second worksheet insert and works repeatedly thereafter (within the same wb instance) suggests a compile timing problem but how do I get around it? The code fails at *** with Subscript out of Range... Sheets.Add After:=Sheets(Sheets.Count) Set ws = Sheets(Sheets.Count) With ThisWorkbook.VBProject.VBComponents(ws.CodeName).C odeModule '*** .InsertLines .CountOfLines + 1, _ 'required proc' End With What am I missing? I would appreciate any help. T.I.A. Geoff |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timing Issue??
ThisWorkbook refers to the workbook containing the code. You need to specify
the specific workbook that you want to operate on. -- Vasant "Geoff" wrote in message ... Hi I am trying to adapt Chip Pearson's code and insert the same procedure into 2 added worksheets. Strange thing is my code only fails each time I start a new instance of the workbook. Even then, when the code breaks on error with 'Subscript out of Range' and I click to continue in Debug mode the code completes without error AND the new module is installed correctly in both new worksheets. Within the same instance of the workbook and beyond the initial 'block' the procedure works without hesitation repeatedly. When creating the second of the 2 worksheets, the code never fails whether it is in the first instance of the workbook or not. The fact that the identical code works on the second worksheet insert and works repeatedly thereafter (within the same wb instance) suggests a compile timing problem but how do I get around it? The code fails at *** with Subscript out of Range... Sheets.Add After:=Sheets(Sheets.Count) Set ws = Sheets(Sheets.Count) With ThisWorkbook.VBProject.VBComponents(ws.CodeName).C odeModule '*** .InsertLines .CountOfLines + 1, _ 'required proc' End With What am I missing? I would appreciate any help. T.I.A. Geoff |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timing Issue??
Hi Vasant
Thanks for the reply , but nope. Qualifying the sheet does not cure the problem. At least if i understand what you mean with : Set ws = ThisWorkbook.Sheets(Sheets.Count). I might add here that I am not trying to run the installed proc at this stage merely to insert it into the worksheet module. Again fails on every new workbook instance but then works after breaking and continue. Geoff "Vasant Nanavati" wrote: ThisWorkbook refers to the workbook containing the code. You need to specify the specific workbook that you want to operate on. -- Vasant "Geoff" wrote in message ... Hi I am trying to adapt Chip Pearson's code and insert the same procedure into 2 added worksheets. Strange thing is my code only fails each time I start a new instance of the workbook. Even then, when the code breaks on error with 'Subscript out of Range' and I click to continue in Debug mode the code completes without error AND the new module is installed correctly in both new worksheets. Within the same instance of the workbook and beyond the initial 'block' the procedure works without hesitation repeatedly. When creating the second of the 2 worksheets, the code never fails whether it is in the first instance of the workbook or not. The fact that the identical code works on the second worksheet insert and works repeatedly thereafter (within the same wb instance) suggests a compile timing problem but how do I get around it? The code fails at *** with Subscript out of Range... Sheets.Add After:=Sheets(Sheets.Count) Set ws = Sheets(Sheets.Count) With ThisWorkbook.VBProject.VBComponents(ws.CodeName).C odeModule '*** .InsertLines .CountOfLines + 1, _ 'required proc' End With What am I missing? I would appreciate any help. T.I.A. Geoff |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timing Issue??
"Geoff" wrote in message
... : Hi : I am trying to adapt Chip Pearson's code and insert the same procedure into : 2 added worksheets. : : Strange thing is my code only fails each time I start a new instance of the : workbook. Even then, when the code breaks on error with 'Subscript out of : Range' and I click to continue in Debug mode the code completes without error : AND the new module is installed correctly in both new worksheets. : <snip I think you left some info out but what I am understanding is you are importing a .bas file into excel, then trying to do something with it and getting a subscript out of range error. This typically happens because your code is moving faster than the import is so your code is trying to do something with a module not there yet. Once you break, or after the code is inserted, everything then works. If I am understanding you correctly, you need to us ontime while inserting to slow down your macro Application.OnTime Now, procedu="your_module_name.ImportIt" Private Sub ImportIt() Dim vbcomp As VBComponents Set vbcomp = ThisWorkbook.VBProject.VBComponents vbcomp.import ("your_macro.bas") End Sub Paul D |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timing Issue??
Hi PaulD
I am adding 2 worksheets and trying to write the same worksheet_activate event into each of the sheet code modules. I am not trying to run the installed code immediately after. I understand what you are saying about timing and after experimenting (a lot) I still think it is part of the solution. I have tried: Application.Wait Now() + 0.0001 and Application.OnTime Now + TimeValue("00:00:01"), "testtime" The crazy thing now is that when using OnTime, the break on fail occurs at the second worksheet Add. No code has been installed on the first sheet Add BUT code has been installed on the second sheet Add. This is now really getting perplexing. There is an explanation and a solution - but what? Geoff "PaulD" wrote: "Geoff" wrote in message ... : Hi : I am trying to adapt Chip Pearson's code and insert the same procedure into : 2 added worksheets. : : Strange thing is my code only fails each time I start a new instance of the : workbook. Even then, when the code breaks on error with 'Subscript out of : Range' and I click to continue in Debug mode the code completes without error : AND the new module is installed correctly in both new worksheets. : <snip I think you left some info out but what I am understanding is you are importing a .bas file into excel, then trying to do something with it and getting a subscript out of range error. This typically happens because your code is moving faster than the import is so your code is trying to do something with a module not there yet. Once you break, or after the code is inserted, everything then works. If I am understanding you correctly, you need to us ontime while inserting to slow down your macro Application.OnTime Now, procedu="your_module_name.ImportIt" Private Sub ImportIt() Dim vbcomp As VBComponents Set vbcomp = ThisWorkbook.VBProject.VBComponents vbcomp.import ("your_macro.bas") End Sub Paul D |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advancing Timing | Excel Discussion (Misc queries) | |||
Timing Chart | Charts and Charting in Excel | |||
VB Timing Question | Excel Programming | |||
Timing Problem can someone help please | Excel Programming | |||
Program timing | Excel Programming |