VBA/EXCEL -- Run-time error '02147417848 (80010108)'
Mike Genovese
worksheets on demand. That logic works.
The new worksheets have two (2) buttons; copying the buttons works
fine.
Assigning code to the actions of the buttons does NOT :
Basic Logic:
Public sub CreateNewWS()
'Create new worksheet based on hidden "template" worksheet that is
"hidden"
'Also creates the two (2) buttons
'Renames both buttons
'All works fine ...
'Can Call BOTH, but only one time.
call AddSubmitGameProcedure(WsName)
call AddClearGameProcedure(WsName)
End sub
The above nearly always seems to work OK, ONCE.
When rerun, I (nearly) always get a
Run-time error '02147417848 (80010108)'
Automation Error
The object invoked has disconnected from its clients
Often followed with an error of the type:
The instruction as "0x65009f43" referenced memory at "0x00d50328".
The memory could not be "read".
After which my options are to TERMINATE or DEBUG the program.
The above subroutine CreateNewWS can run multiple times, creating new
WorkSheets in succession and properly associating code with the
CommandButtons but ONLY if ONLY one of AddSubmitGameProcedure or
AddClearGameProcedure is called.
If both are called, once thru is all you get.
Sometimes it dies with the above error even if only one of the above
functions is called.
Note: this is only pertaining to creating/associating code with a
CommandButton_Click action, not actually running that code.
Any ideas or suggestions ?
OS is Windows/XP; EXCEL version is "MS EXCEL 2000" (9.0.2720) ...
David J. Braden
understanding the problem. I'll make a stab at it anyway:
Comments are nested in your post:
In article <52801d09.02082...@posting.google.com>, Mike
Genovese <mich...@ati.com> wrote:
> I have a VBA/EXCEL application that creates, on the fly, additional
> worksheets on demand. That logic works.
> The new worksheets have two (2) buttons; copying the buttons works
> fine.
In the same workbook as the macro? Or all in a different workbook? Or
is it that each run of the macro creates a *new* workbook?
> Assigning code to the actions of the buttons does NOT :
> Basic Logic:
>
> Public sub CreateNewWS()
> 'Create new worksheet based on hidden "template" worksheet that is
> "hidden"
> 'Also creates the two (2) buttons
Are the buttons already on the template? (and i not, why not?)
> 'Renames both buttons
Why? They are unique objects. Or do you mean you change the text
displayed on the button, as in changing a button that is labelled
"Help" to be labelled "Hilfe" ?
> 'All works fine ...
Meaning?
>
> 'Can Call BOTH, but only one time.
> call AddSubmitGameProcedure(WsName)
> call AddClearGameProcedure(WsName)
> End sub
You aren't calling buttons, you are calling subroutines. And you should
write
AddSubmitGameProcedure WsName
AddClearGameProcedure WsName
instead of what you have.
>
> The above nearly always seems to work OK, ONCE.
> When rerun, I (nearly) always get a
> Run-time error '02147417848 (80010108)'
> Automation Error
> The object invoked has disconnected from its clients
>
> Often followed with an error of the type:
> The instruction as "0x65009f43" referenced memory at "0x00d50328".
> The memory could not be "read".
> After which my options are to TERMINATE or DEBUG the program.
Well, did you consider using the Debug option???
>
> The above subroutine CreateNewWS can run multiple times, creating new
> WorkSheets in succession and properly associating code with the
> CommandButtons but ONLY if ONLY one of AddSubmitGameProcedure or
> AddClearGameProcedure is called.
> If both are called, once thru is all you get.
> Sometimes it dies with the above error even if only one of the above
> functions is called.
>
> Note: this is only pertaining to creating/associating code with a
> CommandButton_Click action, not actually running that code.
>
> Any ideas or suggestions ?
Your problem almost certainly is NOT the buttons themselves; to test
initially, comment out the two procedures you are calling, run the
macro a whole bunch of times, and examine the buttons' properties to
see that the buttons do indeed point to the correct subroutine;
possibly you didn't give a complete reference.
Next, Divide and Conquer. Use the Debug option when it arises; you can
step through your code as well (look at the Debug toolbar in the VBE).
Set breakpoints as needed to speed things up.
Also: comment out the code that generates sheets with buttons, and loop
through the two subroutines by themselves to look for a conflict. Keep
running, commenting, rerunning, recommenting, etc. until you have a
focused problem to repost.
>
> OS is Windows/XP; EXCEL version is "MS EXCEL 2000" (9.0.2720) ...
HTH
Dave Braden
mvp - Excel
--
E-mail (ROT-13'ed): qoe...@ebpurfgre.ee.pbz
Tom Ogilvy
can probably use a single routine to handle the click event for the same
buttons on all pages (using application caller) and you can simply assign
the macro by assigning it to the onaction property of the button.
Regards,
Tom Ogilvy
Mike Genovese <mich...@ati.com> wrote in message
news:52801d09.02082...@posting.google.com...
Mike Genovese
> I don't know about others, but I am having a difficult time
> understanding the problem. I'll make a stab at it anyway:
>
> Comments are nested in your post:
>
> In article <52801d09.02082...@posting.google.com>, Mike
> Genovese <mich...@ati.com> wrote:
>
> > I have a VBA/EXCEL application that creates, on the fly, additional
> > worksheets on demand. That logic works.
> > The new worksheets have two (2) buttons; copying the buttons works
> > fine.
>
> In the same workbook as the macro? Or all in a different workbook? Or
> is it that each run of the macro creates a *new* workbook?
Yep, in the same workbook as the macro.
Each run of the "macro" create a new worksheet, in the same workbook
(*.xls file) that the macro resides in.
>
> > Assigning code to the actions of the buttons does NOT :
> > Basic Logic:
> >
> > Public sub CreateNewWS()
> > 'Create new worksheet based on hidden "template" worksheet that is
> > "hidden"
> > 'Also creates the two (2) buttons
>
> Are the buttons already on the template? (and i not, why not?)
Yes, the buttons are on the template already.
>
> > 'Renames both buttons
>
> Why? They are unique objects. Or do you mean you change the text
> displayed on the button, as in changing a button that is labelled
> "Help" to be labelled "Hilfe" ?
The act of copying the buttons renames them automatically to
"CommandButton1"
and "CommandButton2"; so, I rename them.
>
> > 'All works fine ...
>
> Meaning?
Meaning, I copy the template worksheet, with the buttons, rename the
buttons,
and successfully associate code with those buttons.
> >
> > 'Can Call BOTH, but only one time.
> > call AddSubmitGameProcedure(WsName)
> > call AddClearGameProcedure(WsName)
> > End sub
>
> You aren't calling buttons, you are calling subroutines. And you should
> write
> AddSubmitGameProcedure WsName
> AddClearGameProcedure WsName
> instead of what you have.
Those routines are not button; I realize this.
They associate code WITH the buttons.
It does it like this :
Sub AddSubmitGameProcedure(ws As String)
'Adds the SubmitGame_Click call to the requested WorkSheet (ws)
'Get an Object equating to the current worksheet
Dim Current As Worksheet
Set Current = Sheets(ws)
Dim VBCodeMod As CodeModule 'Here is the associated CodeModule
Set VBCodeMod = _
ThisWorkbook.VBProject.VBComponents(Current.CodeName).CodeModule
Dim LineNum As Long
'Here is where we will insert the NEW Code
LineNum = VBCodeMod.CountOfLines + 1
Dim NewCode As String 'Generate the New Code (as a
String)
NewCode = "Private Sub SubmitGame_Click()" & Chr(13) & _
" Call PC.SubmitGame" & Chr(13) & _
"End Sub"
VBCodeMod.InsertLines LineNum, NewCode 'Insert The New Code
End Sub
Somewhere, the AddSubmitGameProcedure and AddClearGameProcedure
do something that EXCEL doesn't like --- at least part of the time.
This results in the Run-time error I mentioned.
>
> >
> > The above nearly always seems to work OK, ONCE.
> > When rerun, I (nearly) always get a
> > Run-time error '02147417848 (80010108)'
> > Automation Error
> > The object invoked has disconnected from its clients
> >
> > Often followed with an EXCEL.EXE error of the type:
> > The instruction as "0x65009f43" referenced memory at "0x00d50328".
> > The memory could not be "read".
> > After which my options are to TERMINATE or DEBUG the program.
>
> Well, did you consider using the Debug option???
As presented, the debug option allows me only to debug EXCEL.EXE;
giving me an assembler listing of the portion of EXCEL.EXE which blew
up.
>
> >
> > The above subroutine CreateNewWS can run multiple times, creating new
> > WorkSheets in succession and properly associating code with the
> > CommandButtons but ONLY if ONLY one of AddSubmitGameProcedure or
> > AddClearGameProcedure is called.
> > If both are called, once thru is all you get.
> > Sometimes it dies with the above error even if only one of the above
> > functions is called.
> >
> > Note: this is only pertaining to creating/associating code with a
> > CommandButton_Click action, not actually running that code.
> >
> > Any ideas or suggestions ?
>
> Your problem almost certainly is NOT the buttons themselves; to test
> initially, comment out the two procedures you are calling, run the
> macro a whole bunch of times, and examine the buttons' properties to
> see that the buttons do indeed point to the correct subroutine;
> possibly you didn't give a complete reference.
>
> Next, Divide and Conquer. Use the Debug option when it arises; you can
> step through your code as well (look at the Debug toolbar in the VBE).
> Set breakpoints as needed to speed things up.
The debug option, when presented, goes into EXCEL.EXE itself.
>
> Also: comment out the code that generates sheets with buttons, and loop
> through the two subroutines by themselves to look for a conflict. Keep
> running, commenting, rerunning, recommenting, etc. until you have a
> focused problem to repost.
Already done.
I now know it is definately the code that associated actions with the
copied buttons that is causing the problem. What I don't know is why.
The code that does this is (again) :
Sub AddSubmitGameProcedure(ws As String)
'Adds the SubmitGame_Click call to the requested WorkSheet (ws)
'Get an Object equating to the current worksheet
Dim Current As Worksheet
Set Current = Sheets(ws)
Dim VBCodeMod As CodeModule 'Here is the associated CodeModule
Set VBCodeMod = _
ThisWorkbook.VBProject.VBComponents(Current.CodeName).CodeModule
Dim LineNum As Long
'Here is where we will insert the NEW Code
LineNum = VBCodeMod.CountOfLines + 1
Dim NewCode As String 'Generate the New Code (as a
String)
NewCode = "Private Sub SubmitGame_Click()" & Chr(13) & _
" Call PC.SubmitGame" & Chr(13) & _
"End Sub"
VBCodeMod.InsertLines LineNum, NewCode 'Insert The New Code
End Sub
Any ideas what causes this to generate a run-time error when run more
than
once ?
Dave Peterson
code worked, sometimes it didn't. (So my universe of experience isn't too
large!)
But you may want to try Rob Bovey's code cleaner.
It essentially exports your modules to a text file and then reimports them.
http://www.appspro.com/
It's simple to use and only a few minutes out of your life if it doesn't work.
===
The way I fixed my automation error was uglier. I recreated the worksheet that
was causing trouble.
I don't know if it was getting corrupted because of the number of comments/range
names/formatting/or what. But when I copied the worksheet (either by code or
manually), xl went nuts (usually).
But when I moved it (to another workbook), it was happy.
My first fix was to change my code to do a .move and close that workbook without
saving (essentially the same thing).
But it scared me enough that I ended up recreating the worksheet. (So far, no
troubles (knock on wood!).)
This probably isn't a trivial task. You may want to experiment with copying the
code and a limited amount of data to a new workbook.
If the new workbook doesn't blow up, you may have found your solution (as ugly
as it is).
Good luck,
Mike Genovese wrote:
> Any ideas what causes this to generate a run-time error when run more
> than
> once ?
>
--
Dave Peterson
ec3...@msn.com