Very strange add-in file behaviour

[RB Smissaert]

Have developed a large Excel .xla add-in that now shows some very strange
behaviour.
This add-in works completely fine at home, but when I upload the file to a
website and
download it on a different location there are bits of code missing, for
example all the code
in the ThisWorkbook module and also code from normal code modules. When I
download
the same file at home everything is fine.
It is a large file, 3.8 Mb with a lot of form and modules, but why would it
be fine on one computer
and not the other. The differences are:
Home computer (all fine): Windows XP Pro, Excel 2002
Other computer (not fine): Windows 2000, Excel 2003
I have run the run Andrew Baker's Workbook Rebuilder several times.
Thanks for any advice about this.

RBS

 

[Bob Phillips]

Have you run Rob Bovey's Code Cleaner on it?

You could also try building it anew, copying all code modules into a new
workbook before compiling as an addin .

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

I have run the run Andrew Baker's Workbook Rebuilder several times.

is the same as Rob Bovey's code cleaner or doing it manually.

 

[RB Smissaert]

Building it anew to a new workbook and then make it into an .xla file is the
one thing I haven't done yet.
Will give that a try.

RBS

 

[RB Smissaert]

I didn't think there was a difference, but for completeness I will run Rob
Bovey's code cleaner as well.

RBS

 

[RB Smissaert]

Now this is interesting.
I moved all the code and forms from the add-in to a normal workbook and
saved as an .xla file.
The filesize moved up from 3.8 Mb to 6.7 Mb.
Downloaded Rob Bovey's code cleaner, ran that and still 6.68 Mb.
Then ran Andrew Baker's workbook rebuilder and I went down again to 3.8 Mb.
Both files run fine, but what is the difference and what is the file to go
for?
I would like to keep the 3.8 file as the 6.7 one is too big to be uploaded
even when zipped.
The upper limit is 2 Mb and the 3.8 file will zip to 1.65.
The 6.7 file will zip to 2.6.
Would be very interested in any insights in this.

RBS

 

[RB Smissaert]

OK, a bit less interesting now, because after running the code cleaner again
the filesize has
gone down to 3.8.
This saves me the trouble of finding out what the right file is; they are
both the same.

RBS

 

[Bob Phillips]

That is the opposite of my usual experience. I find that files bloat as you
work on them, and rebuilding just before production usually takes a large
lump off the size.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[RB Smissaert]

I find the same, but what I did is have the add-in loaded and drag and drop
modules
from the add-in to an empty workbook. Maybe this is not the same as
exporting to
text and then reimporting.
I have made some progress with this:
The faulty .xla file always had 2 modules where the code was gone, one of
them the
ThisWorkbook module. The other one a normal code module where code was added
most recently.
By combining some normal code modules, one of them the last mentioned, I was
able to
fix this. Maybe there is a limit on the total number of modules. I did have
a lot of modules:
21 Forms
77 Normal code modules
6 Class modules
Over 82000 lines of code
Could the limit perhaps be 100 modules?

RBS

 

[Tushar Mehta]

Another possibility to consider is that the download client machine has
some kind of anti-virus software that strips out what it considers
potentially malicious code.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[RB Smissaert]

That is what my first thought was as I had just added some code that would
make a VB Script file.
There was some other strange behaviour though that made me think it was not
that, such as a never
ending save of the .xla file, needing a Ctrl + Alt + Del.

This is the code that makes the VB Script file and it would be clever
software that would find that malicious:

Sub MakeVBScriptAutoRunFile(strFile As String)

Dim strVBS As String
Dim strIndent As String

strIndent = String(4, Chr(32))

strVBS = "dim app" & vbCrLf & _
"dim wb" & vbCrLf & vbCrLf & _
"On Error Resume Next" & vbCrLf & _
"Set app = GetObject(," & Chr(34) & "Excel.Application" &
Chr(34) & ")" & vbCrLf & vbCrLf & _
"if Err <> 0 Then" & vbCrLf & _
strIndent & "set app = CreateObject(" & Chr(34) &
"Excel.Application" & Chr(34) & ")" & vbCrLf & _
strIndent & "app.Visible = True" & vbCrLf & _
strIndent & "On Error GoTo 0" & vbCrLf & _
"End If" & vbCrLf & vbCrLf & _
"With app" & vbCrLf & _
strIndent & ".DisplayAlerts = False" & vbCrLf & _
strIndent & ".OnKey " & Chr(34) & "{F2}" & Chr(34) & ", " &
Chr(34) & "StartReportForm" & Chr(34) & vbCrLf & _
strIndent & ".OnKey " & Chr(34) & "{F4}" & Chr(34) & ", " &
Chr(34) & "StartReadBrowser" & Chr(34) & vbCrLf & _
strIndent & ".OnKey " & Chr(34) & "{F12}" & Chr(34) & ", " &
Chr(34) & "LoadPatientFromID" & Chr(34) & vbCrLf & _
strIndent & ".OnKey " & Chr(34) & "%z" & Chr(34) & ", " &
Chr(34) & "MakeBPGraph" & Chr(34) & vbCrLf & _
strIndent & ".Workbooks.Open(" & Chr(34) & strFile & Chr(34) &
")" & vbCrLf & _
strIndent & ".UserControl = True" & vbCrLf & _
strIndent & ".DisplayAlerts = True" & vbCrLf & _
"End With"

'done it this way to hopefully fool the v-checker
'------------------------------------------------
StringToTextFile Replace(strFile, _
".xls", _
Chr(46) & Chr(118) & Chr(98) & Chr(115), _
1, _
1, _
vbTextCompare), _
strVBS

End Sub


Maybe I should change the name of the Sub and not mention VBScript !?


RBS

 

[Tushar Mehta]

The fact that you were having problems saving the file in the first
place would give more weight, IMO, to problems with the upload-download
phases of the transaction than corruption of the VBA modules.

As far as the definition of malicious goes, the AV software may define
it as 'anything in a VB module.' Also note that some firewall software
(ZoneAlarm, for example) has also gotten into the business of vetting
downloads.

You may also want to check if the upload process worked successfully.
Can you download the file onto the same machine from which you uploaded
it? Does this downloaded version work? You will have to be careful to
ensure you use the downloaded file and not the original one.

Can you do the same test from some other machine? One that you know
has no AV/firewall software. Or at least, one where you have turned
both off.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[RB Smissaert]

When I downloaded the file to the home PC it worked fine. This was
definitely the downloaded
file. Now that I have reorganised the Project (I now only have 97 modules)
it behaves better in that
it saves quicker and the size has gone slightly down although there was code
added.
Also the reorganised file worked fine when downloaded to the other PC that
had the trouble in the
first place.
All in all I think it is the number of modules that caused the problem, but
I have no previous experience
with this, so can't be sure.
I know the size of individual modules ideally have to stay below 64 Kb, but
what about the number of modules,
what about the size of Form modules and what about the total size of the
..xla file?
I could strip out all the comments in the distributed file, but I haven't
found an easy way of doing this yet.
Tried Rob Bovey's code cleaner, but that couldn't handle this.

RBS