Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Star Lounger SoonerJim's Avatar
    Join Date
    Aug 2011
    Location
    Temple, Texas USA
    Posts
    64
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Upgrade to Windows 7/Office 2010 - Excel macros error out

    I work for the Department of Veterans Affairs. Recently our IT service pushed Windows 7 and Excel 2010 to my computer. Since then, Excel doesn't like what I ask it to do for some reason. I have used the snipping tool to take some pictures, which are attached. If you need to see the full text of the macro I need the most, please let me know.

    Thanks,

    Jim Whitt
    jim.whitt@va.gov
    Attached Images Attached Images

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Jim,

    Open Your object browser within the VB editor and search for "Right" to see if it can find the library. (View>Object Browser) If not, to avoid headaches, I would first try a Office reinstall.

    Library1.jpg

    Post back.
    Maud

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Jim,

    Is it possible you have some libraries on networked drives that have not been reconnected with the new install? I'd check to see if you have access to all drives/shares that you had previous to the push of Win 7 / Office 2010. I'd also make sure that your Excel Trusted Locations in the Trust Center has all the library locations and shared file locations defined there. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,179
    Thanks
    47
    Thanked 983 Times in 913 Posts
    Seems you had additional libraries in your old installation that are not available now. The VBA editor should show you the dependencies for your macros.

    cheers, Paul

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Check Tools-References in the VBE. One or more checked items will be listed as 'MISSING:'
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Star Lounger SoonerJim's Avatar
    Join Date
    Aug 2011
    Location
    Temple, Texas USA
    Posts
    64
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Good morning, everyone. This is what I have found, or rather have not found:

    Rory, I don't have any checked items as "Missing."

    Paul, I thought I had found the Object Browser. But there were thousands of possible items, nothing that gave me a lead on the Right function.

    RG, I'm pretty sure that something didn't hook up correctly.

    Maud, I'm replying - I did not see Right when I checked using your suggestion.

    I think this adds up to a re-installation. Thank you all for taking time to respond. I'll let you know what my IT people think.

    Thanks again,
    Jim

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 481 Times in 458 Posts
    Hi Jim

    In your first attached thumbnail, the error message shows "can't find project or library".
    When you have a missing Reference, vba will stop on any line in the code.
    The code line itself does not indicate what is missing.
    But I am fairly certain (since it's happened to me many times) that Rory is correct, and you will indeed have a Reference that is "missing".
    So check again in the VBE, top-panel, Tools>References
    ..and then scroll down the References list until you find those marked as "MISSING - "

    zeddy

  8. #8
    Star Lounger SoonerJim's Avatar
    Join Date
    Aug 2011
    Location
    Temple, Texas USA
    Posts
    64
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi, Zeddy. I had a great great aunt named Zettie. She was a riot.

    I had one thing missing, but it showed up when I restarted the program. Please see attached screen capture.

    Thanks,
    Jim
    Attached Images Attached Images

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 481 Times in 458 Posts
    Hi Jim

    I can see that the top of the Reference list is OK.
    ..so you scrolled down the full list of References then??
    Are you still having a problem or is it now fixed??
    Did you check that all of your add-in files were loaded OK??

    zeddy

  10. #10
    Star Lounger SoonerJim's Avatar
    Join Date
    Aug 2011
    Location
    Temple, Texas USA
    Posts
    64
    Thanks
    2
    Thanked 0 Times in 0 Posts
    The problem still plagues me. IT came and did a repair on MS Office. The tech said if I needed a new installation, he would have to bring me another computer. What I don't understand is I can type "Right" as a function and it works fine. But it does not work in the user-defined function.

    One other thing which may or may not be related. I typed in a formula in a cell and I didn't get the value I wanted, but instead got the formula back. This is what it looks like. NDC is the function I'm having trouble with, but I don't understand answering back with the formula. It should give an error message or something, right?

    A B
    1111222233 =PERSONAL.xlsm!NDC(A1)

    Thanks,
    Jim

    P.S. Please excuse any typos. The system puts deleted text back and duplicates text.
    Last edited by SoonerJim; 2013-06-28 at 15:32. Reason: Typos

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If you are getting the formula to display check the format of the cell. I suspect it is set to "Text". Change it to a different one then edit and confirm the contents to display the value.

    Steve

  12. #12
    Star Lounger SoonerJim's Avatar
    Join Date
    Aug 2011
    Location
    Temple, Texas USA
    Posts
    64
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thank you, Steve. I changed it to General and it worked, until it stopped where it always stops (as above.)

  13. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Jim,

    I tried this test code:
    Code:
    Option Explicit
    
    Function Test(ByVal x As Variant)
    
       If Right(x, 1) = " " Then
         MsgBox "Trailing Space" & vbCrLf & _
                "Length: " & Format(Len(x), "####"), vbOKOnly, _
                "Status Message"
       Else
         MsgBox "NO Trailing Space" & vbCrLf & _
                "Length: " & Format(Len(x), "####"), vbOKOnly, _
                "Status Message"
       End If
         
    End Function
    Results:
    RightFunction.JPG
    References in use:
    References.JPG

    Note: since A1 contains a number even if I place a trailing space in it when I click the Green Checkmark the space is deleted so the Else clause is never executed!
    HTH

    Update: If the value is changed to text by prefixing with a ' then both branches of the test work correctly!

    Update2: You can use this formula:
    =IF(CELL("type",A1)="v","Value",IF(CELL("type",A1) ="l","Label","Blank")) in col C to determine the type of the data in col A.
    Last edited by RetiredGeek; 2013-06-28 at 18:49.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  14. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Jim,

    While maintaining a fleet of computers, your IT Department most likely installs from a customized system image. There are probably components of MS Office not enabled. A reinstall from the same image will not correct your problem. Ask your IT to do a reinstall from an original Office disk and to enable all components. Alternately, if you have permissions set, you can try Control Panel> Programs and Features. Select MS Office> Change. Select Add or Remove Features> Continue (figure 1). Any components not installed will have an "X". Expand Shared Office Features and under it, expand Visual Basic for Applications. Whether or not it has an "X", click "Run from My Computer"> Continue (figure 2) and reinstall the VB Module. See if that helps.

    BTW, As I understand it, using the RIGHT function within the Excel Interface is an internal built-in function, whereas the RIGHT function in VB is part of the VB library. This is evidenced by the fact that you can still use such commands in the interface without the VBA component installed. The UDF you speak of is accessing the VBA library which we have already identified as missing.

    Maud

    Office1.png Office2.png

  15. #15
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Jim,

    Can you post the code for the NDC function from your personal.xlsm so we can see what it is returning?

    Maud

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •