Add-in / xla causing random wierd GPF / crash

[Terry Aney]

I've written an Excel Add-in (xla) and had everything working fine. The
'main' purpose of the xla was to have 'template' sheets that the user could
insert into their own workbook via menus that I create on toolbar.

As I said, everything was working fine, then I continued to 'extend' one of
the sheets and I'm not sure what caused the problem, but now when I call the
function that worked before, it always GPFs, and not always in the same
spot. And when I say not always in the same spot, it is always somewhere in
the 4 calls to NewPage()...usually happens right after NewPage "RBLInput",
but 15% of the time happens right BEFORE that call.

Note, the two major changes I made are one of the sheets has fairly complex
charts on them that get copied to new spreadsheet, and also I changed the
code that actually does the copy (part of NewPage() method).

Before, NewPage() used something like this to 'create' the new sheet:

Set oSheet = ActiveWorkbook.Worksheets.Add(Worksheets(n))
ThisWorkbook.Worksheets(sSourcePageName).Cells.Copy
oSheet.Cells.PasteSpecial xlPasteAll
Application.CutCopyMode = False

Then the charts didn't come over, so I dug a bit to find a .Copy method on
WorkSheet object, so now code is:

Dim myWkSht As Worksheet: Set myWkSht =
ThisWorkbook.Worksheets(sSourcePageName)
Call myWkSht.Copy(ActiveWorkbook.Worksheets(n))
Set oSheet = ActiveWorkbook.Worksheets(n)

So I guess the 'real' question is, has anyone seen a pattern where you get
vba/xla to GPF/Crash 'consistently' for no real apparent reason. The reason
I say no reason, is that after reading the code below, if I do all these
step 'individually' (i.e. call NewPage() four times from Immediate Window)
it seems to work all right. Any tips on going about debuging this (I'm
currently in the process of recreating entire sheet and trying to determine
when it starts to crash) or better resources that I should try (I've
searched google up and down with no luck)

The function that fails is as follows (I can provide more info/code if
necessary):

Sub ConvertToRbl()
On Error GoTo Err_ConvertToRbl

If val(Application.Version) >= 10 Then
On Error Resume Next
Dim VBP As Object ' as VBProject
Set VBP = ActiveWorkbook.VBProject
If Err.Number <> 0 Then
MsgBox "Your security settings do not allow this procedure to
run." _
& vbCrLf & vbCrLf & "To change your security setting:" _
& vbCrLf & vbCrLf & " 1. Select Tools - Macro - Security." &
vbCrLf _
& " 2. Click the 'Trusted Sources' tab" & vbCrLf _
& " 3. Place a checkmark next to 'Trust access to Visual Basic
Project.'", _
vbCritical
Exit Sub
End If
On Error GoTo 0
End If

NewPage "Info", "RBLInfo"
NewPage "RBLData", "RBLData"
NewPage "RBLInput", "RBLInput"
NewPage "RBLResult", "RBLResult"

Dim oModule As VBComponent
On Error Resume Next
Set oModule = ActiveWorkbook.VBProject.VBComponents("mRBL")
On Error GoTo Err_ConvertToRbl
If Not oModule Is Nothing Then
Call ActiveWorkbook.VBProject.VBComponents.Remove(oModule)
End If

Set oModule =
ActiveWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)
oModule.Name = "mRBL"
If oModule.CodeModule.CountOfDeclarationLines > 0 Then
Call oModule.CodeModule.DeleteLines(1,
oModule.CodeModule.CountOfDeclarationLines)
End If
Call
oModule.CodeModule.AddFromString(ThisWorkbook.VBProject.VBComponents("mRBLSpreadEngine").CodeModule.Lines(1,
ThisWorkbook.VBProject.VBComponents("mRBLSpreadEngine").CodeModule.CountOfLines
+
ThisWorkbook.VBProject.VBComponents("mRBLSpreadEngine").CodeModule.CountOfDeclarationLines))

Call RemoveDefaultSheets

Exit Sub
Err_ConvertToRbl:
MsgBox "Error converting to RBL SpreadEngine." & vbCrLf & vbCrLf &
"Details: " & Err.Description
End Sub

 

[Terry Aney]

Bit 'paranoid' here since I see a lot of un replied to posts...if anyone
looks at this and doesn't know, I wouldn't mind a 'I don't know' or
something so at least I know people are reading it...or is there a better
way to view how many times a post has been viewed?

 

[Guest]

I have a similar issue. My crashes when I change the sheet name. What's up
with that?