Excel 2010 VBA Left & Ltrim functions???

[brucemc]

I posted this in the Windows 7 forums, but have not seen anything in 10 hours. Please forgive the double post, but I need to find out what the heck is happening-

I just opened an Office 2007 Excel project in 2010 which contains significant VBA. The compiler is puking at the Left function - Compile error - Can't find object or library

I have tried retyping the line and also tried using the Right function, just to see what happened - and that was not accepted either!

Earlier the compiler rejected the Ltrim function also.

ANY ideas?

REAL worried at what else I am now going to run into, now that I "upgraded"...

Great thanks for your thoughts, I have a feeling I am going to have many more questions due to the nature of the problems and upgrading...

And where the heck did my line and position numbers/indicator go in the 2010 VBE????

 

[Norie]

For the first part have a look in the VBE under Tools>Options... and see if any of the references are marked as MISSING.

Try unchecking them and trying the problem functions again.

If that doesn't work post back.

You could, as a sort of quick fix, precede the functions with VBA eg VBA.Right, VBA.Left etc

What line and position indicators are you referring to?

I'm not sure if either problem is related to the OS, the 2nd one might just be a wrong setting.

Normally for that you would check under Tools>Options... in previous versions.

Not quite sure what the route is in later versions.

 

[brucemc]

Strange (for me!). Though I did not find the items described in Tools:Options, in opening the sheet and running into the error the VBE does take me directly to a window titled "References <sheet name>" and checked is an item titled "MISSING: Microsoft Calendar Control 11.0".

I "unticked" it and the workbook seems to be functioning without previous error. Can you explain to me what is happening?

Also, in regards to the cursor position indicator in the VBE that I couldn't find - You know, the one that is staring at me in one of the toolbars now at the top instead of the bottom of the page where I was previously looking and couldn't locate... I SWEAR it wasn't there before!

 

[Norie]

Bruce

Well that certainly sounds like an improvement in 2010, previously if you came across this error and it could be caused by a reference nothing would happen.

ie it would error but it wouldn't give you any options or suggestions nevermind direct you to somewhere you could fix it

As for what causes it, well the simple answer is the reference marked MISSING.

How that reference ended up like that could be for various reasons:

1 The machine running Excel doesn't have the Calendar Control installed.

2 There is a different version of the reference on the problem machine.

1 could happen whatever version of Excel you are using, and could actually be because Access isn't installed, 2 could be version related but I've never heard
of any other version of the Calendar Control than 12.0.

As for the cursor in the VBE, I do that all the time in every application - normally be pressing something like ALT.

That usually takes you to the main menu.

Luckily, and I don't know why/how, when I do it and try to continue typing Excel/whatever makes a loud ding noise.

 

[brucemc]

Thank-you!

I suspect 90% of the trouble (or more) is that though at one time I was quite deep into VBA, it has been a while and I tend to forget what I am not actively using...

For that first problem I suspect I need to find that calendar control and install it somehow - as this computer was completely rebuilt and reloaded after a "catastrophic event" last November, I'll bet I am missing a number of the bells and whistles I once had running/included.

On a new front, I am trying to figure out a new problem along the same lines- I had a friend email me a copy of this workbook that I made some custom mods for him to use. If I double click the attachment from his email it opens just fine, but if I first save the attachment to a location and then double click the file from the directory I saved it to I get and "Error 91: Object Variable or With block not set" with a halt on a line that simply reads:

ActiveWorkbook.Unprotect ("rrqrqrrqq")

Hunh? Once again, it runs fine in Excel 2007. This transition and learning experience is going to be "fun" for me, isn't it...

 

[brucemc]

Ohhhhh, this is really sick.

going through the file emailed to me I don't get the "Refereces" window pop-up. under Tools: References is grayed out, so I am having to go through all the code and every Left, Right, Mid, etc that I have I am having to precede them with "VBA.". Well, I guess I am thankful that there is a workaround, but what the heck is going on???

 

[Norie]

Is ActiveWorkbook supposed to refer to the workbook the code is in?

If it is then try changing it to ThisWorkbook, and you might want to remove the () too.

Mind you the more I read it the more I'm uncertain what's causing the error.

The only object being referred to is ActiveWorkbook which surely must exist.

Where is the code located?

For the Calendar Control, you might actually have it already but the reference is pointing to the wrong location.

You can check that out by looking at the path for it in Tools>References...

If you don't have it or can't find it I think you can download it from various places, including Microsoft, or you could just copy it from another machine.

By the way is the calendar control actually being used or been used in the past anywhere?

 

[brucemc]

Located in the ThisWorkbook object. Change to ThisWorkbook worked.

Seems all character handling functions need to be preceded with "VBA." -
Left
Right
Mid
Trim
Ltrim
Rtrim

and

Format
Date
Time

Any kind of recognizable pattern?

Further:
Now that we have the ThisWorkbook object progressing, I get a
Run-Time error '1004': Select method of Range class failed
on
Worksheets("Pymt Calculator").Range("C8").Select
whereas "Pymt Calculator" is Sheet01 and, once again, worked flawlessly under 2007 and apparently when I opened this directly from the email attachment. Should I start suspecting my computer is possessed?

Hope someone might spot a patter. I am really not going to be thrilled if I have to uninstall and reinstall everything, or worse yet, just revert back to 2007...

edit: I was wrong in my presumption that the copy opened from the email worked. Though it did not trip a VBA error upon opening, once I started to try any inputs the string handlers started to cause the same errors, requiring them to be preceded by "VBA." as in "VBA.Left(<str>)".

I think one thing I now need to do is create a real simple little test, completely written from the 2010 version, with some simple character string handlers, and see if that works. It sure won't save a zillion lines of code from 2007, but at least it might relieve me in knowing that going forward my installation isn't corrput (ummmm, "corrupt", though "corrput" seems a fair way to state it also ).

 

[brucemc]

Oh my. Have a look at:
http://www.eggheadcafe.com/software/aspnet/31786052/microsoft-calendar-control-110.aspx

I think this might give me some ideas. Seems to be a good lead, and looks like someone experienced this misery (which loves company) once before...

 

[Norie]

You really don't want to be adding VBA to all those functions, and I'm pretty sure they aren't the only ones that will be/are being affected.

I really don't think this is exclusively a version issue, well not specifically Excel 2010 anyway.

Like I said this sort of thing can happen for various reasons, version differences not being particularly high up the list - usually.

As for the Select thing - using Select generally isn't needed and cause problems.

What is the code that line is in actually doing?