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).CodeModule '***
.InsertLines .CountOfLines + 1, _
'required proc'
End With

What am I missing? I would appreciate any help.

T.I.A.

Geoff

ThisWorkbook refers to the workbook containing the code. You need to specify
the specific workbook that you want to operate on.

--

Vasant


"Geoff" <Geoff@discussions.microsoft.com> wrote in message
news:65DDF8E7-9955-4A35-8722-51E38934CE09@microsoft.com...
> 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).CodeModule '***
> .InsertLines .CountOfLines + 1, _
> 'required proc'
> End With
>
> What am I missing? I would appreciate any help.
>
> T.I.A.
>
> Geoff
>

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" <Geoff@discussions.microsoft.com> wrote in message
> news:65DDF8E7-9955-4A35-8722-51E38934CE09@microsoft.com...
> > 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).CodeModule '***
> > .InsertLines .CountOfLines + 1, _
> > 'required proc'
> > End With
> >
> > What am I missing? I would appreciate any help.
> >
> > T.I.A.
> >
> > Geoff
> >
>
>
>

"Geoff" <Geoff@discussions.microsoft.com> wrote in message
news:65DDF8E7-9955-4A35-8722-51E38934CE09@microsoft.com...
: 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, procedure:="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

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" <Geoff@discussions.microsoft.com> wrote in message
> news:65DDF8E7-9955-4A35-8722-51E38934CE09@microsoft.com...
> : 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, procedure:="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
>
>
>