×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Wierd problem with VBA/Excel

Wierd problem with VBA/Excel

Wierd problem with VBA/Excel

(OP)
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.
 

RE: Wierd problem with VBA/Excel

(OP)
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.

RE: Wierd problem with VBA/Excel

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: www.essexsteam.co.uk

RE: Wierd problem with VBA/Excel

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.

RE: Wierd problem with VBA/Excel

(OP)
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.

RE: Wierd problem with VBA/Excel

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

RE: Wierd problem with VBA/Excel

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?

RE: Wierd problem with VBA/Excel

(OP)
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).

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources