Wierd problem with VBA/Excel

[ivymike]

I've got a spreadsheet with some macros to automate various tasks. It does all the things I intend for it to do... but only on my computer. When I open it from another user's computer, none of the built-in VBA functions seem to work. I can't find any missing library references.

Btw, when I say "none," I mean it. For example, the Mid() function doesn't work unless I call it as VBA.strings.Mid(), the "Like" statement doesn't seem to work, etc.

 

[ivymike]

oh, I forgot to mention the real kicker -
if I create a new spreadsheet on the other user's machine, and re-key my subroutines (verbatim) into it, they run just fine. If I switch windows back to my other sheet, they still don't work. Switch back, they work again.

 

[johnwm]

Check your Tools|Options|Security|Macro security. If it's set to High you will get the symptoms you describe. Either lower the security setting, or look into getting a digital signature and signing the macros

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

Steam Engine enthusiasts:

http://www.essexsteam.co.uk

 

[handleman]

Which version of Excel are you using? I believe a setting of "High" in 2003 will not give you the results you describe. High macro security in 2003 disables macros completely. I've seen your symptoms before on some of my spreadsheets. I really haven't dug deeply into it. I just got in the habit of using VBA.[function] explicitly. It's somewhat inconvenient, but that way it works on everyone's machine. Debugging machine-specific problems means you have to check every user's machine before they can run your macro. VBA.[function] is a band-aid that's worked on every machine I've seen so far.

 

[ivymike]

Macro security is not the problem.

The vba.[function] seems to be a 70% fix - there are some things that still don't seem to work, like
Selection.ShapeRange.ScaleWidth 0.9, msoFalse, msoScaleFromBottomRight

in which neither msoFalse nor msoScaleFromBottomRight are recognized. So far my workaround is to use vbFalse and omit the msoScaleFromBottomRight. This means that I need to reposition the pic later, but that is do-able.

 

[CajunCenturion]

You may have a reference problem.

In the VBA Editor look at Tools->References and then ensure that VBA for Applications is checked as well as is the Excel <version> Object Library. Depending on other things in your code, you may require other references as well.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[handleman]

I've seen the issue with the VBA reference checked and at the top of the priority list.

As for the constants (msoFalse and msoScaleFromBottomRight), you can just use the integer number instead. If you need to find the number, just go to a machine where the function works and run a quick MsgBox msoScaleFromBottomRight.

You do realize that those constants are in the Office library rather than the VBA library, right?

 

[ivymike]

I figured they were, but as far as I can tell I've got the same libraries checked on all concerned machines. It won't let me uncheck VBA or Excel libraries ("in use"). I did notice that there are about 7 different libraries named "Visual Basic (VBA)" on the list, and that I can only select one at a time (the one I can't deselect), because otherwise it complains about duplicate names.

Everything is ironed out now... but it still irks me that the stupid thing didn't work the first time. The most bizarre example of the problem was when I recorded a macro using the recorder, and then couldn't run it moments later on the same machine (for lack of the right library).