Friday, April 26, 2019

excel vba - VBA: compile error after .copy

If have a large Workbook with a sub, that copies a specific worksheet to a new workbook and then saves this worksheet as FileFormat51 (xlsx without macro) to get rid of the contained code:



Public Sub savefile()
Dim WB As Workbook, WBtemp As Workbook

Dim path As String, antw As String, ext As String
Dim filetobesaved

path = ThisWorkbook.path
With Application.FileDialog(msoFileDialogSaveAs)
.InitialFileName = path & "\" & "standard name"
Application.ScreenUpdating = True
antw = .Show
Application.ScreenUpdating = False
If antw = -1 Then

filetobesaved = .SelectedItems(1)
ext = Right(filetobesaved, Len(filetobesaved) - InStrRev(filetobesaved, ".")) 'InStrRev() finds the first dot from the right and gives its position to Right() to write the file extension into ext
If ext <> "xlsx" Then
MsgBox "You chose ." & ext & " as filename extension. As this might cause problems during the current procedure I will change it to .xlsx."
filetobesaved = Left(filetobesaved, InStrRev(filetobesaved, ".")) & "xlsx"
End If
Else
Exit Sub
End If
End With


Set WB = ActiveWorkbook
TKontur.Copy
Set WBtemp = ActiveWorkbook

WBtemp.SaveAs Filename:=filetobesaved, FileFormat:=51 '51 = .xlsx without macros
WBtemp.Close

End Sub



This worked well for years until Excel started compiling the code right after the Worksheet got copied.
The code of the workbook compiles well before the copy-task (debug->Compile VBAProject works fine) but after the copy-statement the code fails to compile for many reasons that all include that the worksheet got copied into a new workbook without all the other worksheets and modules it references.



Currently if I restart the PC and then open the workbook and only execute the given sub, then I get said error.



The weird thing is at first I thought it was a data corruption error and rebuilt the whole thing (and rebuilt it again multiple times until now) and after every rebuild everything works fine for at least one time but eventually the same bug reappears and I have no clue what causes it to reappear.



I also found out that deleting any module (no matter which one), saving the Workbook and then reopening the workbook causes the error to not occur at least one time (-> all fine) no matter which module I delete.
So I thought it might be a problem with memory overflow.

But then when I rewrote the program with the most basic functions but half the code and half the modules this worked fine for two weeks and then the error reappeared.
It gets weirder: I wrote a more basic version with version number say 2.0
In those two weeks I changed some things to version 2.5
Most versions were in use at some time and worked.
But when the error occured in version 2.5 once all versions back to 2.0 started to have the same error right away when they did not have it before.



Also if the error occured at least one time, then no matter what I change it will occure every time except if I remove every single one of the many references to things in other modules and worksheets than the copied one.



There's also a very similar error that happens to be kind of unrelated (either error can happen without the other error and sometimes they happen both and sometimes neither happens) but has very similar properties:
When closing the workbook sometimes excel closes the excel-objects first and then compiles the modules and fails with that.

When that happens, then



ThisWorkbook.Saved = True
ActiveWorkbook.Close


usually helps to ignore that error for a while, but then (usually after a year or so) it reappears even with those two lines. Edit: Also this workaround seems to have a 20% chance to crash Excel including all other open workbooks.



Last thing: As soon as the error occurs once
most of the time it then occurs on all PCs and Notebooks including different update status, Operating System and Office version.

No comments:

Post a Comment

plot explanation - Why did Peaches&#39; mom hang on the tree? - Movies &amp; TV

In the middle of the movie Ice Age: Continental Drift Peaches' mom asked Peaches to go to sleep. Then, she hung on the tree. This parti...