Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Unset a reference via code?

    Hi,

    Trying to track down a macro solution for bfreer's post, and it looks easy to do if you can unset a reference programatically.
    Question is: how?

    If you set a reference to the VBA Extensibility library, it looks like adding a reference is easy. You can use for example:

    VBE.ActiveVBProject.References.AddFromFile ("D:WordDataTemplatesNormal")

    or even

    VBE.ActiveVBProject.References.AddFromFile ("Normal")

    Removing it seems much harder. There is no Remove method available for a Reference object.
    There is a Remove method available for the References collection, but the expected syntax doesn't work - the Remove method wants you to specify a "Reference As Reference".

    These first two efforts gave compile 'type mismatch' errors:

    VBE.ActiveVBProject.References.Remove ("Normal")
    VBE.ActiveVBProject.References.Remove (6)

    The next two compile OK, but give runtime errors along the lines of property or method not supported:

    <pre>VBE.ActiveVBProject.References.Remove (objRef) ' previously set obj variable
    VBE.ActiveVBProject.References.Remove (VBE.ActiveVBProject.References("Normal"))
    </pre>

    Feels like I'm missing something obvious - can anyone shine a light on this one?

    Thx,
    Gary

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Unset a reference via code?

    It's not obvious, and after an hour I want to shoot myself in the head over this one: contrary to the documentation, leave out the parentheses on the Reference object to be deleted!

    Example: <A target="_blank" HREF=http://support.microsoft.com/support/kb/articles/Q213/5/29.ASP>http://support.microsoft.com/support/kb/ar...s/Q213/5/29.ASP</A>

    Anyway, at long last, this will now work:

    Sub NukeReference()
    Dim aRef As Reference, colRefs As References
    Set colRefs = VBE.ActiveVBProject.References
    For Each aRef In colRefs
    'Debug.Print aRef.Name & "; " & aRef.FullPath & "; " & aRef.Type
    If InStr(1, aRef.FullPath, "palmapp", vbTextCompare) > 0 Then
    colRefs.Remove aRef
    End If
    Next
    End Sub

    (Of course, use whatever search strings in the .Name or .FullPath make sense to you.)

  3. #3
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Unset a reference via code?

    Hi Jefferson,

    Thanks, this definitely does the trick. With all the permutations I did try, somehow I didn't try objRef without the parens.

    The object reference, and testing for conditions first to avoid an error, is definitely the way to go, but just for oddity's sake, this works too:

    <pre>VBE.ActiveVBProject.References.Remove VBE.ActiveVBProject.References("Normal")
    </pre>

    Thanks again,
    Gary

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unset a reference via code?

    Congratulations on dropping your parens in public (grin!).

    We've many of us stumbled on a trick like that; presumably you followed the time-honoured ritual of first smacking yourself in the forehead then, as the blood drianed, kicking the first four-legged object that came to hand and tossing a reference manual after it. Sigh.



    I have a question for you, and I'm not being picky, honest.

    I noticed that you don't include the optional identifier with the NEXT command at the end of the loop. Is there a reason for that, or do you just feel that its not necessary for a single, non-nested loop?

    I've had it hammered into me (mainly by experience) to include that variable for best effects all round. I know that some/many of my habits are just that - habits that die hard.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Unset a reference via code?

    Chris,

    I always include the variable reference in my Next statements too. It may not be required in the For Each ... Next loops, but it makes the code easier to understand if nothing else. It doesn't hurt, and it may help.
    Charlotte

  6. #6
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Unset a reference via code?

    Regarding Next versus Next aRef, in the early days, I recall there being some kind of performance penalty with the latter form. With today's PCs, I don't know of any reason to leave it off. Don't want to send the wrong message to you serious coders out there!

  7. #7
    Star Lounger
    Join Date
    Jan 2001
    Location
    L.A., California
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unset a reference via code?

    I've seen several times in various "tips & tricks" type columns that the code runs faster if you leave off the variable. I guess the variable name doesn't have to be re-evaluated each time??? Should be easy enouth to set up a test, by setting a variable to now(), running a 10- or 100- thousand iteration loop with the variable, then without it, recording the start and end times and comparing.

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unset a reference via code?

    > I guess the variable name doesn't have
    to be re-evaluated each time???

    I agree that a test may show something.

    My understanding of an interpreter is that the code gets tokenised, and the tokenising process works out the nesting, says "Oh bother, they left the variable identifier out; oh well, it must be ..."; that is, obviously the interpreter knows which identifier to increment (as does the human viewer if we leave it out). Placing it in, therefore, should benefit the reader with no running cost penalty.

    I hope all that makes sense. I had some very good lessons in "efficiency" in my early days ....

  9. #9
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Parentheses = passing by value

    The root cause of all this wasted time was an error in MS' online help - if we had double checked the Remove method of the Collections object, we would have had the correct answer more quickly. I am going to try to draw a lesson from this, nevertheless, and raise a question.

    To review, the method call Collection.Remove (objMember) fails with "Run-time error '438': Object doesn't support this property or method" (note how the VBE pushed the parentheses away from the word Remove...),

    but

    the method call Collection.Remove objMember works correctly,

    and, even more interestingly,

    the method call Call Collection.Remove(objMember) works just fine.

    This cryptic error message is wrong and useless. But the presence and absence of parentheses stirred something old and neglected in a dark corner of my mind. I postulated that the method was not receiving the object it needed because the parentheses caused objMember to be passed by value.

    When I was learning WordBasic and later VBA, I never really paid much attention to ByRef and ByVal and when to Call and when not to. I didn't get it (probably still don't), and it didn't seem to matter much. But maybe it matters a whole lot more than I thought?

    Obviously it matters when you are passing an object. In KB articleQ190935, dealing with this issue in the ADO context, cautions:

    <hr>FuncOrSubName (Arg1, Arg2, Arg3) 'Never use this syntax!

    This is old BASIC syntax, supported but not recommended. If one of these arguments was, for example, an ADO Recordset, what would actually be passed to the function was the default value of the recordset, which in this case is the Fields Collection.
    <hr>
    Generally speaking, VBA seems very tolerant of accidentally passing arguments by value rather than by reference, because - other than an object - what difference does it make? In the <A target="_blank" HREF=http://msdn.microsoft.com/library/devprods/vs6/vbasic/vbcon98/vbconpassingargumentstoprocedures.htm>Visual Basic Programmer's Guide</A>, MS explains that you can pass an integer to a procedure expecting a string by using parentheses:

    Sub CallingProcedure()
    Dim intX As Integer
    intX = 12 * 3
    Foo(intX)
    End Sub

    Sub Foo(Bar As String)
    MsgBox Bar 'The value of Bar is the string "36".
    End Sub

    Crusty old KB article <A target="_blank" HREF=http://support.microsoft.com/support/kb/articles/Q41/5/35.ASP>Q41535</A>, written for QuickBasic, lays out the implications of various uses of parentheses in detail:
    <pre>CALL mysub (arg1,arg2) ... by reference
    mysub arg1,arg2 ... by reference
    CALL mysub ((arg1),(arg2)) ... by value
    mysub (arg1),(arg2) ... by value
    </pre>

    Why did anyone care back then? Because users might change the value of the passed variable in a subprocedure, and not achieve the desired results (i.e., to change or not to change the value in the main program). I learned this the hard way when I sent an "April Fool" macro around my old company back in '94 or so. It was designed to minimize all the running programs in Windows 3.1 to icons and have them chase each other around the edge of the desktop 4 times. Due to a failure to pass by value, I created an infinite loop requiring many users to reboot Windows. (My bad, although the message did warn them to save their work before opening the attachment - all trojans should be so polite!) But I digress...

    The issue highlighted by this whole episode is that the same rules appear to apply to built-in methods (speculation, not yet confirmed). And this suggests that the same issue of efficiency may arise. According to Lomax's VB & VBA in a Nutshell, "ByRef is much faster than ByVal" when passing variables to (1) the same project or (2) an in-process ActiveX component. Presumably the host office application is "in-process" with a VBA program. For an out-of-process server, he recommends passing by value, to avoid the extra work of replacing the original values of the parameters with whatever comes back from the called procedure.

    "Much faster" is a claim that gets my attention and may be worth thinking about. If Collection.Remove is any indication, a lot of optimization might be possible. Any thoughts?

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parentheses = passing by value

    Lotsa thoughts.



    >ByRef and ByVal and

    [soapbox]

    The ONLY time I think Call By Value(CBV) ought to be used (but see "efficiency" below) is when I know I'm going to change the value within the called procedure. That is, if I am passing a value identified as a variable, and the procedure plans to change that value, then use CBV. (CBV seems to have no relevance if I'm passing a literal or constant value.)

    But then why not Call By Reference (CBR) and set up a parallel variable in the called procedure and copy the incoming value there? I confess that the bulk of my work (to date) deals with small objects, such as LONG and STRINGS being parsed. There's the occasioanl record-set or document but not enough to worry me yet (but see "efficiency" below).

    In this case using CBR maintains the consistent view that what gets passed as an argument is available in its original state on its return. I still have to respond to James's post of two weeks ago, and I *am* softening my edicts (grin!), but only to the point of feeling that as much as possible a FUNCTION ought to compose two arguments to one result. After James's post I'm prepared to concede that this is not always possible, and some of you/us may well have functions that need to return several changed items - a LONG, a STRING, a RecordSet and so on. I have a sneaking suspicion that this being the case we should be using CLASSes or user-defined data objects and returning such an object as a single result, leaving all arguments inviolate.

    Code readability raises its lovely head again (but see "efficiency" below).



    >"ByRef is much faster than ByVal" when passing variables

    In an earlier incarnation I worked in compiler development. From there I learned that a reference to some sort of data item was typically a few words in length, regardless of the size or type of the object. You need a pointer to a symbol table, a word describing the type of data, a word describing its size, and a pointer to where a potentially humungous block of actual data might reside. The "reference" can take a lot less space than the block of actual data. Therefore communicating the reference ought to take less time than communicating the block of data.

    Of course for tiny values such as a 1-character string, the reference can be much larger than the block of data, but it's still only a few words.

    Bonus: if a reference is always the same size, the calling sequence ought to take the same time regardless of the size of the block of data, moving towards predictable execution times regardless of the data at run time. Nice!

    And it's calling sequence we're discussing, right? Not speed-of-execution inside the called procedure.

    [/soapbox]


    [war stories]




    >"Much faster" is a claim that gets my attention

    Faster for who?

    When I was little I determined that I could squeeze 11.5 microseconds (one instruction cycle) out of the innermost loop in a payroll program for Australia's largest company. I proudly took it to my boss. he agreed that I'd saved 11.5 microseconds and thoughtfully asked me to pursue this line of thought to calculate the overall savings in computing over a year's operation. I did the calculation with pencil and paper (it was a long walk to the building housing the IBM1401). I think the savings (10,000 employees 26 times a year) came to 3 seconds. TOTAL. I'd spent 3 days working all this out.

    That taught me a lesson in "efficiiency"

    [/war stories]


    Nowadays I worry a lot less about execution speed until it really rears its ugly head. If I'm using an application that trims half an hour off my working day, why should I care that it runs 2 seconds slower?

    There are very clear design considerations in writing program code, such as loading data from file into RAM memory and processing a memory array, or obtaining as much as possible from a user before embarking on a data operation. These considerations always seem to involve the RAM-versus-something-else arena, disks and humans are markedly slower than computer memory, and it is the disk or human time we should optimize.

    Very rarely do we need to optimize instruction code to the level we think we'd like to.

    Havings said that, there are specific applications that SHOULD be optimised to death. Parts of the VBE interpreter that get used by thousands of users throughout the day, the Windows disk data caching alogorithms etc. But for us application developers, there's not a lot that's worthwhile.

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Christchurch, New Zealand
    Posts
    250
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Unset a reference via code?

    Hi Gary,

    I'm trying to use your line to unreference a DLL file. (It works fine when I unreference "Normal" as in your example.) The error message I get is "Runtime error 9 - subscript is out of range". The help message for this is:
    <font color=448800>

  12. #12
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unset a reference via code?

    Karen,

    I'm not Gary, sorry.

    You get the message about "nonexistant collection member" when the reference has already been removed.

    If you don't see it under "References" in the VBProject folder, it's probably been removed; if it appears under Tools, References, AND the box next to it is NOT ticked, then it's not attached to that template. If the box is ticked, there's more head scratching to be done. Only the ones with ticks are attached.

    Does that fit your situation?
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  13. #13
    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

    Re: Unset a reference via code?

    Hi all,
    Just to chuck in my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> (though it should be tuppence for me), I do a combination of the 2 with For...Next loops in that I always end with:
    Next 'myval
    so I can tell what it is, but the code (allegedly) is optimised.
    Just a thought.
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #14
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Set a reference via code?

    If I understand the business correctly, there is no way to distribute a locked self-installing Word97/VBA template that needs a reference to be tested/set.

    1) I have a file called Utils.dot which I have {created | obtained}. Utils.dot is a locked-for-viewing template which contains common or utility procedures.


    2) I have a file called Trail.dot which I have {created | obtained}. Trail.dot is a locked-for-viewing template which contains useful user procedures (macros) which depend upon access to Utils.dot.


    3) The locked template Trail.dot will generate a pre-execution error if the Utils.dot is not available at the time I start execution of Trail.dot.

    4) Since Trail.dot will not execute, there's no way I can embed code in Trail.dot that will test/set missing references.

    Below is pasted a simplified version of the code I would like to see working in Trail.dot


    <pre> Dim strRef As String
    strRef = Application.StartupPath & Application.PathSeparator & "Utils.dot"

    Dim colRefs As References
    Set colRefs = VBE.ActiveVBProject.References
    colRefs.AddFromFile (strRef)
    </pre>


    If anyone would like to join in an experiment, I'll be happy to bat small locked templates back and forth to prove my point or to prove a solution.

  15. #15
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parentheses = passing by value

    This is a general issue, and Word issued a proper error message.

    A.B is proper if B is a PROCEDURE that takes arguments and one wishes to pass C by value.

    A.B is proper if B is a METHOD and A.B is being used as an expression, e.g.:

    x = A.B

    A.B C is proper if C is an arg to a method, e.g.,

    range.InsertAfter stringVariable

    Using Call is optional when invoking a procedure, however, if Call is used, then the arg list must be enclosed in parens. One might view that as the procedure name and arg list being an expression in te Call method.

    One of the reasons I bitch and moan that folkes should read VB books to get a better understanding is that VB books are oriented towards programmers and do better cover such concepts. The time spent reading a book such as Gary Cornell's Visual Basicv 6 from the Ground Up will pay many times over.

    Note that VB .Net will require use of Call, so you might start getting used to using it now.

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
  •