VBA compatibility issues: Excel 2007, 2010, 2013

Hi All,

I have been working for months on a series of projects that are due to be submitted imminently. The agreement with the client was that the VBA functionality will be compatible with Excel 2007, 2010 and 2013, both 32-bit and 64-bit versions.

To date, I have been working and testing in 2010 and 2013 (32-bit) and have had no problems but the tools have just been run on a client's machine running 2007 and it's started throwing up a load of errors I haven't encountered before now. My problem is that I don't have a copy of Excel 2007 to test it with, making identifying the cause of the conflicts very difficult. I have included error handling routines in the code so I am able to identify which subs the problems arise in and the type of error code being generated, but there are hundreds of subroutines and some of them are quite long so pinning the issues to specific lines is proving tricky.

My question is: is there any way of testing VBA code for compatibility with specific versions of Excel? Or at the very least, is it possible to use some form of emulator to replicate earlier versions of the software? I have an Office 365 subscription for what it's worth...

Any suggestions would be very welcomed!

I have never come across such a thing but I am surprised you have so many errors between those versions. Are you using API calls in these workbooks?

Hi, yeah I had previously included the line

If FileExists(addr) then Kill addr

but it didn't help. The directory in question should be ok as the user will have previously saved the parent file there.

I am now of the opinion that the procedure of exporting the image to a file, uploading to the VBA control and deleting the temporary file is causing a conflict because of system lag - the VBA is running faster than windows can process the file creation/deletion, resulting in the code effectively being bottlenecked and crashing. It could actually have more to do with the computer than the version of Excel. Interestingly, I just remoted onto a machine where the error was occurring every time and it doesn't happen while I am connected via WebEx. I reckon the additional delay caused by the remote operation is just enough to allow the system to catch up. Does that sound feasible?

Hi Andy,

I have 2010 and 2013 running on the same machine but that's it. The cost of the licenses were not included (to be honest I have been shafted from beginning to end in terms of workload/money but that's another issue and won't be repeated!).

I guess my only solution would be to install Excel 2007 on top of the other two versions but I know that's going to give me no end of trouble and is not really feasible now on the time scales involved. To be honest, when I started working on the project everyone was using 2007 or 2010, now a lot of the user base has upgraded to 2013, some of them 64-bit (stupid idea if you ask me but there we are) and I have spent a lot of time fighting fires in those versions - it's all very frustrating to now be finding issues in 2007 (and inconsistent issues at that!).

I founded my company off the back of work similar to this, creating very complex interfaces in VBA to accommodate Excel statistical models (public sector work). I'm about to start transferring all of my products over to web platforms so hopefully my days of battling with these compatibility issues in Excel are drawing to a close...

Thanks, as always, for your valuable input.
AdLoki

I am using API calls but the errors do not derive from the module that contains them. It seems that (at least some of) the errors are stemming from the following subroutine:

Sub LoadImage(ByVal imgType$, imgname$, ByRef uForm As UserForm, Optional ByVal ctlName$)
On Error GoTo errorhandler

If ctlName = "" Then ctlName = imgname

Dim addr$, ratio#, PicHeight#, PicWidth#

addr = ThisWorkbook.Path & "\" & "~TempImg." & imgType

If FileExists(addr) Then Kill addr

PicHeight = uForm.Controls.Item(ctlName).Height
PicWidth = uForm.Controls.Item(ctlName).Width

With ThisWorkbook.Sheets("Images")
    .Shapes(imgname).Select
    
    ratio = PicHeight / .Shapes(imgname).Height
    .Shapes(imgname).Height = PicHeight
    .Shapes(imgname).Width = .Shapes(imgname).Width * ratio
 
    .ChartObjects(imgname).Chart.Export Filename:=addr, filtername:=imgType
End With

With uForm.Controls.Item(ctlName)
    .Picture = LoadPicture(addr)
    If InStr(ctlName, "Start") Or InStr(ctlName, "Stop") Then
        .Visible = False
    Else
        .Visible = True
    End If
End With
Kill addr
ThisWorkbook.Sheets("Images").Visible = xlSheetVeryHidden

Exit Sub
errorhandler:
ErrorTrap "#070001", Err
End Sub

Essentially (all the fiddly details aside) the routine takes an image control in a userform and loads a picture to it which is saved in a worksheet, embedded in a chart frame; the chart frame is first re-sized to match the dimensions of the control (this had to be done to accommodate different local resolution settings). The image in the worksheet is then exported to a static image file ("~TempImg.xxx"), which is loaded to the control and then the temporary file is deleted. All pretty standard and straight forward and not a problem in Excel 2010 and 2013 but it 2007 it falls down.

It seems to fall down specifically when it is being called multiple times in quick succession - the same routine is used without a problem to load images at other points in the code. My suspicion is that the code is running faster than the excel 2007 system can export the image file, load it and delete it. I have tried using a sleep procedure to add a pause in the code to allow the system to "catch up", I have tried adding DoEvents (which, incidentally, I have never found to make a difference in any scenario) but to no avail.

The most common errors are 70 (permission denied) and 75 (path/file access error), which I think lends weight to my suspicion. Any thoughts?

That's a really good idea, I might just give that a go - thanks.

Andy, I added the following code

Dim p%
While FileExists(addr)
    Sleep 50
    p = p + 1
    If p > 40 Then Kill addr
Wend

It seems to have done the job, so thanks for the suggestion!