Compatibility Issue Excel 2000 and Excel 2003

rcash864

I wrote a macros for a customer in Excel 2003. He didn't tell me he used Excel 2000 until after the project and like an idiot I forgot to ask. The macros will not run on his version of Excel 2000. A compile error shows up.

So he got a version of Excel 2003 and the macros will still not work for him. I have tested this macros on other machines using 2003 and they all work correctly for me. I live in the US and the customer lives in the UK. When he runs the macros it immediately goes into the debugging window and highlights a bit of code that simply references the path of the existing folder to a variable. The exact code is:

DestFolder = ThisWorkbook.Path & "\"

There is a compile error with a message that the data folder or project cannot be accessed. The folder the macros resides in that I zipped and sent him was not password protected by me nor can I think of why he cannot access the path to the folder with this bit of code.

Does anyone know if there any issues between UK versions and US versions of Excel that I don't know about Any ideas

Jon Peltier

Is the client running the code from within the zip file Or from his email program ThisWorkbook.Path will fail in these cases for sure.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

rcash864

No I've walked him through the macro process and he unzipped the file to a new folder on his desktop. Strange thing is when he tries it on his home computer and work computer he gets the same errors. I'm in SC and doing another project for a guy in LA, Cali. I got him to test it for me and it works for him. I guess I should ask if there is anyone on the forum who would be interested in testing it for me.

Jon Peltier

Was the file unzipped into a folder with special privileges For example, if he unloaded it as an admin into a system kind of directory, then tried running the program as a restricted user. But this is starting to stretch it.

If you want I'll take a quick look at it. jonxlmvp at peltiertech dot com

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

Andy Pope

Hi,

If you want to email the file I will test tonight, I'm in the UK but I don't think this is a locale problem. At least not the code sample you posted.

email addy can be found on my website www.andypope.info

Do you know what version of xl the client has   yes you do. And so would I if I had read your first post again

rcash864

Thanks a bunch

I sent an email to Jon and Andy. The macros is attached inside a folder

Thanks for taking a look.

---------------------------------------------------------------------------------------------------

Another thing I did was send a different macros to the same client that included a single button. When he clicked the button it displayed the path to the desktop in a message box.

Private Sub CommandButton1_Click()
    Dim DestFolder As String
    
    On Error GoTo thisError
    DestFolder = ThisWorkbook.Path & "\"
   
    MsgBox Prompt:=DestFolder

thisError:
   
    If Err.Number <> 0 Then
        MsgBox Prompt:="There was error with this macros: " & vbNewLine & "Error description: " & Err.Description & vbNewLine & "Error code: " & Err.Number
        Exit Sub
    End If

End Sub

I was trying to recreate the same error in a different macros to see if it would work and it did. So I don't know why in one macros he gets an error for the DestFolder = ThisWorkbook.Path and in another he doesn't.

He also said something about an error saying a VB library could not be found when he tries to run the macros from the main project.

 

Any ideas

Andy Pope

No email arrived for me.

rcash864

I went to your web page and sent it to the email for Andy. Is that the right email or am I supposed to use Addy like in your post.

Jon Peltier

The program had no problem with 'ThisWorkbook.Path'. I found a lot of variables were not declared, and it was these variables that led to the program not running on your customer's machine and on mine. The VB project had a missing reference on my machine, Dot Net Web Site Objects. Missing references are often associated with apparently unrelated errors, such as undeclared variables halting execution.

Here are a few hints on successful coding. They require more work up front, but less while implementing, especially on a remote user's computer.

1. In the VB Editor, go to Tools menu > Options > Editor tab. Check 'Require Variable Declaration' and uncheck 'Auto Syntax Check'. This puts 'Option Explicit' at the top of each newly created code module, and thus requires a declaration for each variable.

2. Insert the line 'Option Explicit' at the top of every existing code module which does not show it.

3. Declare a type for each variable. A line like this:

  Dim rows, num, i As Integer

only declares i as an integer, while the ones preceding it are declared as variants. Either alter the line like this:

  Dim rows As Integer, num As Integer, i As Integer

or split it into one separate line per variable. I find one line per variable is better, because I can more easily add or remove one variable, and I can put a comment on the line after the variable.

4. Make variable names as descriptive as possible. You have both MyFile and ThisFile in one sub. Both, it turns out, are strings, one refers to a file name, the other to a path and file name. You might use sFileName and sFullName for these two variables, and have a better idea six months hence what they refer to.

5. Attach only as many references to a project as are absolutely necessary. I don't think Dot Net Web Site Objects is required for what your code is trying to do.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

Andy Pope

the email address is

andy at andypope dot info


But looks like Jon's got you sorted.

rcash864

Thanks to both of you Andy and John.

Like I said in the emails my coding would probably seem amatuer to both of you. The reason there was all the undeclared variables is because I hate having to declare another integer for incrementing or counting when I'm in the middle of creating a loop. So I just define a bunch up front and go back and delete the unused ones when I finished.

I should use Option Explicit. Thanks for reminding me.

I think I found the problem just before I read Jon's post. I had two references to Visual Studio that are not on the clients machine. I was messing around with them earlier in the macros and forgot to deselect them. He was getting the error "Compile error. Cannot find project or library." Searched for the problem on google and most of the reasons had to do with bad references. I hope this solves the problem for him.

Thanks for the help, comments, and pointers.

Jon Peltier

I started as an amateur, writing code for myself only, mostly to ease the boredom of doing tedious repetitive tasks over and over again continuously (redundancy intended to convey ennui). By the time I turned pro I was much better, but still pretty amateurish. What my goal is, is to write better code this year than last, and better still next year.

My tips are meant to instruct and to save others the mistakes I've made, freeing them up to make their own mistakes. I used to hate declaring all those variables, until I slowly came to realize how important it really is. Now I am continuously going to the declarations section of a procedure and modifying what's there. Five minutes spent now to save an hour later.

- Jon 
------- 
Jon Peltier, Microsoft Excel MVP 
Tutorials and Custom Solutions 
http://PeltierTech.com 
_______