2011 - 2007/2003 compatability

[Twarner405]

Hi all,

I am new to these forums, and I confess new to the world of VBA programming.
I have what I think is a compatability issue between versions of excel. I would appreciate anyone advising me how to get round this issue.

I have written a procedure using excel 2011 that works perfectly, but does not run using excel 2003 or 2007. Basically I have narrowed down the problem to this one line:

DateToCompare = Workbooks(Tracker).Worksheets(Class).Range("M" & LoopCounter).Value

Where DateToCompare is set to a date variable and LoopCounter is set to an integer variable.

The contents of the cell in question is definitely a date value.

I can't remember the exact error, I'm now at home - I can't post from work as I work in a prison, but it's something along the lines of an invalid data type.

I just cannot figure it out and have come to the conclusion that it must be the wrong syntax for 2003/2007.

Can anyone help me?


Thanks,


T

[MarvinP]

HI Twarner405 and welcome to the forum.

I think the system default date formats between the Mac and the PC are different. I run into this problem with some workbooks sent in my Mac users and it drives me crazy.

See if
http://support.microsoft.com/kb/180162 is the problem.
You may need to set the PC to the Mac date formats for your Mac created stuff to work correctly. I don't know if you can switch the default Mac date settings to what the PC uses.

[Twarner405]

HI Twarner405 and welcome to the forum.

I think the system default date formats between the Mac and the PC are different. I run into this problem with some workbooks sent in my Mac users and it drives me crazy.

See if
http://support.microsoft.com/kb/180162 is the problem.
You may need to set the PC to the Mac date formats for your Mac created stuff to work correctly. I don't know if you can switch the default Mac date settings to what the PC uses.

Thanks for the advice MarvinP.

Your info is interesting but I don't think that its my problem. Basically my procedure won't run at all, I 'think' that your suggestion means I would get the wrong date value?

My sub wont run on 2003 or 2007 (admittedly on a PC), but will run in 2011 (yes that's on a Mac). I really wish I could remember the error, but I do recall that its a runtime error that stops at this line and it reports a data type error.

Can someone confirm if the syntax should work on 2003/2007?


Thanks,

T

[MarvinP]

Hi,

Try putting quotes around Tracker and Class (unless they are defined strings already.
Make
DateToCompare = Workbooks(Tracker).Worksheets(Class).Range("M" & LoopCounter).Value
DateToCompare = Workbooks("Tracker").Worksheets("Class").Range("M" & LoopCounter).Value

ALSO - I don't know if "Class" is one of those reserved words that is a object and can't be used...

[Twarner405]

Hi,

Try putting quotes around Tracker and Class (unless they are defined strings already.
Make
DateToCompare = Workbooks(Tracker).Worksheets(Class).Range("M" & LoopCounter).Value
DateToCompare = Workbooks("Tracker").Worksheets("Class").Range("M" & LoopCounter).Value

ALSO - I don't know if "Class" is one of those reserved words that is a object and can't be used...

Tracker and Class are indeed defined as strings. But your thought on the word 'Class' is worth a try. Have to wait till Monday to try it, can't just walk into a prison!

Thanks,

T

[MarvinP]

I though you just couldn't walk out of a prison?

Using the VBA Object Browser (not to be used by the weak of heart), I don't find the word "Class", so I'm still looking.

How about this.... That line points to a workbook, right? Perhaps the workbook or path to the workbook are different between Mac and PC? Perhaps the PC can't find the workbook so you get that "Can't find Object" error?

[Twarner405]

O.K. Clearly I need to give you more information. I was hoping that someone would tell me that the syntax was incorrect for that single line and I'd have it fixed in no time.

While I don't have the exact error, I can post the whole of the code for folks to look at. The bottom line is that it works on 2011 but not on 2003/2007.

I'm hoping that someone can tell me why, cos I can't figure it out.


Thanks.

Please Login or Register  to view this content.

[MarvinP]

Hi Twarner405,

Please click on Edit below your last post, then on "Go Advanced", select all the VBA code and click on the "#" icon above the advanced message area. This will put your code in Code Tags and be readable. Also the forum moderators won't be mad at you.

I've looked at your code and see you need files open and in the correct places to have things run correctly. I'm still suggesting that one OS can find them and the other can't.

If this were my problem, I'd set a breakpoint near the top and step through the lines of code. I'd mouse hover over variables to make sure each is what I expected. Then if it hangs on that line, see which variable is undefined. This is how we do it, in the trenches. See http://www.cpearson.com/excel/Debug.htm for breakpoints and stepping.
If that didn't work I'd be searching the net for the exact error messge and see what MSDN says about it. And lastly I'd be asking us "experts" who would try to duplicate your problem.

I wish I knew exactly what it was but you will need to find it yourself. Breakpoint and Step is my best answer.

[Twarner405]

Thanks.

I did as you suggested and hashed the code. Hopefully thats o.k. now.

I know for certain that the correct files are open, but I will follow your suggestion next week and post my findings. Hopefully it will be straight forward.

T

[Twarner405]

O.k. I had 10mins today to look at this problem.

The error I get is:

Run-time error '9'
Subscript out of range

I set a break point and sure enough the line in question seems to be:
DateToCompare = Workbooks(Tracker).Worksheets(Class).Range("M" & LoopCounter).Value

I am certain that the file names are correct.

I did not have enough time to step thru the code, but did notice what I think is a bug in excel 2007: if I set a break point after this line I don't get any variable content when mousing over. If I set the break point before this line I do. Not sure what's going on here.

Any ideas?

Thanks,

T

[Twarner405]

I'm still struggling with this problem.

Last week I did some more digging: as was suggested the problem does seem to be related to the file names. I changed: Workbooks(Tracker)....... To ThisWorkbook..... And got past the line the code was stopping at.

So instant conclusion is that the filenames are I correct. But they arn't. I have double and triple checked.

The code now stops with the same error at the line where it tries to paste to the second file.

Workbooks(CompletionForm).WorkSheets(Sheet1)......

So I suspect that CompletionForm is wrong. Thing is, the following line works:

WorkbooksOpen(CompletionForm) Open

I know this because after the codes stops, this file is open.

Has anyone got any ideas? It's driving me crazy.



Thanks,

T

[Twarner405]

Right, I have more information about this problem. I am sure I am missing something really obvious.

I have the code working but in a roundabout way:

I changed the reference to the "Tracker" file by using the ThisWorkbook function as that's were I was running the code from anyway.

Now the code stopped at the line: Workbooks(CompletionForm).Worksheets("Sheet1").Range("M" & CompletionRowCounter).Paste etc etc

The variable CompletionForm is set correctly since the line: Workbooks.Open CompletionForm works perfectly.

In order to get this to work I left the variable CompletionForm alone and changed the problem line to: Workbooks("Blank Completion Form").Worksheets("Sheet1").Range("M" & CompletionRowCounter).Paste etc etc

I then copied the file "Blank Completion Form" to the same folder as where I am running the code from, and it worked!!!!!

Can someone please tell me what's happening here?


Many thanks,

T

[MarvinP]

Questions?

1. When you step into that line of code of .......Paste....... Is there anything in the clipboard. If there is nothing in the clipboard it won't work but be confusing on why.

2. If you hover over the value of CompletionRowCounter, is a valid number and make sense?

3. Are filenames, and what is valid for the Mac vs PC different? That is: Does the Mac allow spaces in filenames? Is the maximum lenght of filename or path the same between the PC and Mac.

It is great that you are working in the debugger and discovering how to resolve these problems on your own.

I hope these suggestions allow you to discover what is wrong between the two OSs.