Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Trusted Source (VBA/Excel and Word/2003)

    <P ID="edit" class=small>(Edited by wdwells on 30-Dec-06 21:17. Note the new paragraph at the end of the post.)</P> The following code when run from the Normal template of Word, provides the appropriate results in the Immediate window:
    <pre>Sub test()
    Debug.Print ActiveDocument.Name
    Debug.Print ActiveDocument.VBProject.VBComponents.Count
    End Sub
    </pre>

    However, the following similar code <pre>Sub test()
    Debug.Print ActiveWorkbook.Name
    Debug.Print ActiveWorkbook.VBProject.VBComponents.Count
    End Sub
    </pre>

    when run from the Personal project of Excel throws the following error:
    <hr>Run-time error '1004':
    Application-defined or object-defined error<hr>

    The initial attempt to run the offending code (?ActiveWorkbook.VBProject.VBComponents.Count), from the immediate window of Excel VBA throws errors; initially
    <hr>Run-time error '1004':
    Programmatic access to Visual Basic Project is not trusted<hr>
    , while subsequent attempts throw:
    <hr>Run-time error '1004':
    Application-defined or object-defined error<hr>
    I don't understand the inconsistency between the results obtained when running essentially the same code in the Word and Excel versions of VBA, and would appreciate any thoughts on making the code in my Personal.xls project "Trusted".

    Added Note I just tried the same test on my otther machine and find that there, in the same environment (office 2003 on XL), Word throws rthe error, while Excel performs as one would expect. Am I missing references perhaps?

    T.I.A.
    Regards
    Don

  2. #2
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Trusted Source (VBA/Excel and Word/2003)

    In each application, go to Tools > Macro > Security, open the "Trusted Publishers" tab and set the checkbox "Trust access to Visual Basic project".

    Note: This does make it easier for some types of Virus to propagate.

    StuartR

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Trusted Source (VBA/Excel and Word/2003)

    Stuart has already given you the solution. As he notes, "Trust access to Visual Basic project" must be turned on in each Office application separately.

    If you want to manipulate VBA code programmaticaly, you must set this, and you must also set a reference to the Microsoft Visual Basic for Applications Extensibility 5.3 library in Tools | References in the Visual Basic Editor. Although VBA itself exposes the basic objects needed such as VBComponents, the Extensibility library provides access to the code itself.

  4. #4
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thanks to all

    Stuart and Hans

    Without the continued support of dedicated mentors such as you; life in the MS Office world would be considerably more trying.

    Thank you both; and may the New Year bring a continuing sense of satisfaction to you and all contributors to the Lounge.
    Regards
    Don

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,896
    Thanks
    0
    Thanked 86 Times in 82 Posts

    Re: Trusted Source (VBA/Excel and Word/2003)

    Hans,
    Surely you only need the reference to the library in order to get the Intellisense - you can still work with the VBProject and its components without referencing the Extensibility library (though I don't know why you would want to). Or has that changed in Office 2003?
    Regards,
    Rory
    Microsoft MVP - Excel.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Trusted Source (VBA/Excel and Word/2003)

    You're correct, all the objects are available in standard VBA. I'd feel rather lost without IntelliSense though...
    Without the reference, the objects, methods and properties are not listed in the Object Browser, even if you display hidden members.

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,896
    Thanks
    0
    Thanked 86 Times in 82 Posts

    Re: Trusted Source (VBA/Excel and Word/2003)

    You mean you haven't memorised the entire Object Model yet? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory
    Microsoft MVP - Excel.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Trusted Source (VBA/Excel and Word/2003)

    Erm, my memory isn't what it used to be...

    What was the question again? <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

Posting Permissions

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