Excel 2010 does not recognize vba functions such as date and time, left etc

[Son]

Hi, I have a workbook that has about 35 modules. The problem is that my wb is made in excel 2003, and works fine in excel 2007, but in excel 2010 I get the message of compile error in hidden module 1 etc. When I debug, I get error at the point that I have :

range("A1") = Dat
range("A2") = Time
range("A1") = left(range("A1"), 2)

The code breaks in various modules, some of which begin with "option explicit" and some do not. I have not been able to research the issue further because my wb is protected and I don't have excel 2010 in my pc, I can just try to use my colleagues' computer for a short while.

I would really appreciate any of your ideas on this because more and more of the users of the wb want to upgrade to excel 2010 and i'm afraid I will not be able to support this.

 

[Zack Barresse]

Hi there,

Occasionally I've run into this. Preface it with "VBA.", so "VBA.Date()", or "VBA.Now()"

 

[Son]

vba. ? ok, i'll try and let you know, thanks!
But what about left(xxx, 1) it also breaks on this! Any thoughts?

 

[Zack Barresse]

Same thing. They're part of the VBA object library, as opposed to the Excel object library. When you're in the VBE hit F2 to view the Object Browser. In the top-left corner you can select the drop down to view one of the loaded libraries.

 

[Son]

great! Now I understand! I did not know about F2 and the libraries. I will try it out tomorrow and let you know! thanks!!!

 

[Zack Barresse]

Very welcome.

I'm not sure the reason that's caused and a lot of times a restart will fix it. I'm not sure if it's references or what, but it is good practice to reference your objects more explicitly.

 

[Son]

hmm, what do you mean i should reference objects more explicitly?

And also, now I'm thinking that i have various other functions such as index/match, max, round etc. I have introduced them with application.worksheetfunction or simply worksheetfunction. I am not sure that excel 2010 won't break there, i will have to try it and see. Do you have any suggestions on when I should use vba. before a function and when not?

 

[Zack Barresse]

So doing things like prefacing your functions in VBA with "Application.WorksheetFunction" is what I mean, specifying the object containing that method or property. It's like when referencing a Range object, you should always reference its parent, or Worksheet object, and when working in multiple workbooks you always need to reference the Worksheet's parent, or the Workbook object in which it's contained. That's what I mean by explicitly referencing objects. That way there's no room for error (read: assumption).

I'd use "VBA." preface for any function/routine you're calling from that library, for instance all of the VBA functions (as opposed to those in the WorksheetFunction list of methods, which is a different library). Examples would be Left(), Right(), Mid(), Replace(), etc. There are VBA functions as well as WorksheetFunction's (functions). Make sense?

 

[Artik]

... but in excel 2010 I get the message of compile error in hidden module 1 etc. When I debug, I get error at the point that I have ...

The problem does not have to be related to the version of the XL2010, but with references to the used libraries. Check in VBE menu Tool/References. See if there are items that start with MISSING.

Artik

 

[Son]

There are VBA functions as well as WorksheetFunction's (functions). Make sense?

I think that's the thing that troubles me now, how to distinguish between the two kinds of functions and which library each function belongs to. But I will try to do as you say and let you know. Thanks again.