Losing VBA code under 'ThisWorkbook' (Private Sub WorkBook_Open() )

1
votes

First three elements of the background:

  1. I have created a Excel Template which is used, in turn, to create a 'personalised template' where user name, user comment, and save data pathnames are embedded in the personalised template.
  2. The personalised template is used to produce monthly workbooks (actually, time sheets)
  3. The template has been created in Office/Excel 2007 running under Vista but the (current) target environment is a corporate network running Office/Excel 2003 under XP Professional. Both templates are therefore created as Excel 2003 templates (which in the development environment run in compatibility mode)

So far, so good - the templates work well in the development environment - the 'personalisation' code is in a WorkBook_Open() routine under 'ThisWorkbook' - it runs, DELETES ITSELF, and saves as the personalised template. Both templates have the 'process macros' in the Sheet1 code (the principle here is that the initial template has macros in 'ThisWorkbook' and 'Sheet1'; the personalised template has macros only in 'Sheet1' and the workbooks created from the personalised template have NO MACROS AT ALL.

In 'beta testing' I had problems with Excel2000 under XP (fileformats) and on a target machine my WorkBook_Open routine was deleted in the main template (instead of in the personalised template) - even though everything worked OK on Excel2007/Vista.

I felt at the time that the invalid deleting of Workbook_Open was probably a coding error -but whilst researching the issues I found a number of references to 'Macafee deleting VBA modules'. I did not look into these - but was conscious that the target machine (and environment) run Macafee whilst all my computers run Norton.

Having recoded, I successfully retested in the following:

a) Excel 2007/Vista/Norton
b) Excel 2007/XP Professional/Norton
c) Excel 2000/XP Home/Norton

so felt comfortable to re-test in Excel 2003/XP Professional/Macafee

This time I was watching for it - so, once again, saw the WorkBook_Open routine incorrectly deleted from the main template - unfortunately this was only moments before the USB Memory Key (where the template was running from) was completely destroyed.

So to the question(s) - before I go through the whole process again:

  1. Is there any objective evidence of Macafee removing 'auto-run' VBA modules?
  2. If yes, is there any work-around (this template will be use by three people in a enterprise of ten thousand - so there is no way I will be able to influence/modify the security policies!!! :( ) - if there is a problem with Macafee then I will have to re-think the whole thing!
3
excelsubroutineantivirusvba
I've never heard of McAfee (or any antivirus) deleting code. If I understand correctly, your own routine is meant to delete code? Why? Sounds like the much more likely culprit. - downwitch
Yes and no! My WorkBook_Open routine runs, interacts with the user (gets user details), creates and saves the new template then deletes itself. The new template is almost the same as the original but includes user details and doesn't include the WorkBook_Open routine. In essence the WorkBook_Open routine is designed to install the (user) template on the user's machine. However, in the XP Pro/Office 2003/Macafee environment the WorkBook_open routine seems to be deleted immediately (before running!) - Don Edmondson
As I said in my original question - the template works correctly in a) Excel 2007/Vista/Norton b) Excel 2007/XP Professional/Norton c) Excel 2000/XP Home/Norton - Don Edmondson
Maybe obvious, but did you checked if you save in xls format with macro (xlsm or similar) for XL 2007 ? Besides, did you try your code step by step to see which part might not work - especially the save as ? - JMax
There are plenty of ways to invoke a run-once "setup/config" type process without putting it in the open event and then deleting that event code. Regardless... I'm still skeptical. Try this: on the affected machine, go to the immediate window, type Application.EnableEvents = False, hit enter, and then open the workbook in question. Is the code in question still present? - downwitch

3 Answers

0
votes

To answer your actual question... YES, many antivirus programs absolutely consider autorun code that runs on opening Excel or Word docs to be unsafe, and deletes it, because for a brief moment ten years ago this was actually the way some worms worked. I don't know if MacAfee in particular does this but I've definitely heard of it happening.

0
votes

JUst in case anyone else comes to this thread - I have now fully checked out my template - and re-run it in the target environment - and the Workbook_Open routine was again deleted. after doing some digging around on the target machine I found the Macafee event log and lo and behold...
Event Type: Warning
Event Source: McLogEvent
Event Category: None
Event ID: 258
Date: 09/07/2011
Time: 15:45:40
User: NT AUTHORITY\SYSTEM
Computer:xxxxxxxxxx
Description: The file F:\Timesheets\Timesheet Generator.xlt\1.OLE contained X97M/Generic Virus. The file was successfully cleaned with Scan engine version 5400.1158 DAT version 6400.0000.

So there is the definitive proof. Same thing happens with Workbook_Activate()

The 'offending' lines appear to be

With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
        .DeleteLines 1, _
        .CountOfLines
0
votes

I have managed to get to the client machine again - and added a new simple template to prove or otherwise my issue. The new WorkBook_Open routine is...

Private Sub WorkBook_Open()
    MsgBox "Hello World"
    With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
        .DeleteLines 1, _
        .CountOfLines
    End With
    MsgBox "Goodbye to all that!"
End Sub

...and this works exactly as required i.e. the routine executes then deletes itself (the second msgbox displays even though the code has been deleted!

So, this test invalidates my question - McAfee is NOT deleting the routine as part of AV protection - so I am no longer looking for a workaround!

Now all I need to do is to figure out what my problem really is!

Thanks for the comments