locked
Why are my VBA string format functions are not working? RRS feed

  • Question

  • The vba functions "Format" "Left" "Right" are not working in VBA with office 2010, Windows 7. I am wondering where are they, or what alternatives are there?

     

    Thanks,

    Jeff Kunkel

    Monday, September 13, 2010 1:56 PM

Answers

  • The error says "Compiler Error: Cannot find project or library."

    I assume I am missing a reference to some library. What should I have referenced for the string functions to work?


    Jeff, please check in Tools/References if any entries have the text "MISSING" in front of them. If something is "MISSING", it doesn't matter what it is. This doesn't have anything to do with any particular type library you HAVE to have. It has to do with the fact that the VB functions often don't function correctly if anything at all is "MISSING".
    Cindy Meister, VSTO/Word MVP
    • Marked as answer by Bessie Zhao Monday, September 20, 2010 10:09 AM
    Tuesday, September 14, 2010 4:48 AM

All replies

  • Hi Jeff

    They should be there - VBA hasn't changed significantly :-)

    You don't provide what messages you get when you try to use them, but generally, if native VBA functions aren't recognized, it indicates a problem in Tools/References. If you look there, do you find anything markedas "MISSING"?

    P.S. Could a moderator please remove the "Proposed as Answer". I thought I was in a different message when I clicked that, and cannot unmark... <sigh>


    Cindy Meister, VSTO/Word MVP
    • Proposed as answer by Cindy Meister MVP Monday, September 13, 2010 3:12 PM
    • Unproposed as answer by Bessie Zhao Monday, September 20, 2010 9:59 AM
    Monday, September 13, 2010 3:09 PM
  • What exactly do you mean by "not working"? Do they execute but just not produce the normal results? Do you get an error? In VBA, go to the Tools menu and choose References. See if any reference is marked "MISSING". If you have a missing reference, the VBA compiler will cause errors with items in other references that are not missing. Thus, the compiler may error out on "left" even  though the VBA library is not missing. If you do not need the reference that is missing, just uncheck it in the list. If you do need it, you may have to re-install the program that owns the reference. One  easy way to possibly fix this is to run Excel with the /regserver switch.

    Close Excel, go to the Windows Start menu, choose Run, and enter:

    C:\Program Files\Microsoft Office\Office14\Excel.exe   /regserver

    and click OK. Of course, change the folder path to your specific installation of Excel. This will cause Excel to run, reset its references and registry key back to "factory defaults" and then quit. /regserver can cure any number of problems.

     


    Chip Pearson, Excel MVP Pearson Software Consulting, LLC www.cpearson.com
    Monday, September 13, 2010 3:10 PM
  • The error says "Compiler Error: Cannot find project or library."

     

    I assume I am missing a reference to some library. What should I have referenced for the string functions to work?

     

    Thanks,

    Jeff Kunkel

    Monday, September 13, 2010 6:05 PM
  • The error says "Compiler Error: Cannot find project or library."

    I assume I am missing a reference to some library. What should I have referenced for the string functions to work?


    Jeff, please check in Tools/References if any entries have the text "MISSING" in front of them. If something is "MISSING", it doesn't matter what it is. This doesn't have anything to do with any particular type library you HAVE to have. It has to do with the fact that the VB functions often don't function correctly if anything at all is "MISSING".
    Cindy Meister, VSTO/Word MVP
    • Marked as answer by Bessie Zhao Monday, September 20, 2010 10:09 AM
    Tuesday, September 14, 2010 4:48 AM
  • The file comdlg32.ocx should be in your C\WINDOWS\SYSTEM32\ folder. Simply copy it from a working computer and paste it into the faulty one. It has worked with pretty much everyone around here who has had VBA missing library problems.

    Friday, December 10, 2010 9:27 PM
  • Hello Folks,

     

    Interesting topic. I runed into the same issue with the [right] function by codding in VBA.

     

    The only solution I found was to use: application.right(blabla).

     

    Can anyone tell me if there is a known / akn root cause there?

     

    Thx

     

    yarr

    Tuesday, June 7, 2011 2:55 PM
  • Chip, your solution worked for me (running C:\Program Files\Microsoft Office\Office14\Excel.exe   /regserver).

    "Right" is part of the VBA library (according to Object Manager: C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL). I assume that when it fails, it means there is something wrong with the Office installation/settings; which is probably why Chip's fix worked. I think the suggestion to look for a "MISSING" library is still worth trying first; because as Cindy remarked, any missing library can cause problems.

    Friday, October 21, 2011 3:47 PM
  • Checking for missing references is the obvious answer, which didn't help me at all. (I didn't have any missing references).

    What did work was searching the object browser for "right"

    and I found it under VBA->strings

    I then replaced all right and left commands with:

    VBA.strings.right

    VBA.strings.left

     This worked perfectly.

    • Proposed as answer by modifieda4 Wednesday, January 16, 2013 4:34 PM
    Wednesday, January 16, 2013 4:34 PM
  • That makes me think that Right is being used for something else in your code. Do you have a module named Right?

    Regards, Hans Vogelaar

    Wednesday, January 16, 2013 5:20 PM
  • I went through the same sort of problems and have found out a solution for them.  When macros are trusted and enabled, some commands such as Right, were being used by some sort of unwanted macros (viruses) silently making them not functional.  I recoded all my VBA Functions and Subs by:

    Inserting an OPTION EXPLICIT statement in the beginning of each module.

    Declaring all variables by DIM statement

    Replacing "VBA.Command" statements to all VBA command that were not recognized.  For example, if Left is not recognized, replace the Left command by VBA.Left.  The commands not recognized differs from one computer to another.  Therefore, it is safe to replace all VBA commands to VBA.Commands and all Excel commands to Application.commands if the application software are intended to be used in more than one computers.

    Regards,

    Pushpa

    Monday, July 22, 2013 1:40 AM
  • After upgrading from Office 2010 to Office 2013 I started to incur this ridiculous error.  The VBA.Strings.Left was the hack that worked for me.  Application.Left did not work.  There were no missing references either.  

    Thank you modifieda4!

    Saturday, December 13, 2014 5:37 AM
  • you may have named a sub as "Sub Format" .ie
    Wednesday, February 3, 2016 8:35 AM
  • Just ran into this issue in Excel 2016 64 bit. Simply unchecked Missing library and all returned to normal. If it turns out that library (Microsoft Windows Common Controls 2.6.0 (SP6), in this case) is really necessary then I may just qualify each errant command with proper prefix (VBA.Left, VBA.Right, etc.).
    Saturday, January 14, 2017 3:37 PM
  • Worked like a charm 

    Thank you

    Thursday, January 19, 2017 4:05 PM
  • This saved my life! I tried about 15 things to correct this error and this one fixed the issue for me. Thanks!
    Friday, September 29, 2017 2:27 PM
  • Just another possible solution, I ran into this issue recently and it turns out that a Library that I wasn't using and that I hadn't previous selected, was being referenced. Library description is "Microsoft Vbe UI 7.1 Object Library". I deselected this library and it resolved the issue. 
    Monday, May 14, 2018 5:21 PM