Removing a broken reference from VBA

  • Thread starter Thread starter jfp
  • Start date Start date
J

jfp

I am adding code to an app that will check at startup if a reference to
my code library exists. On a new installation the reference will not
exist and i will then set it up by verifying that the .mdb (or .mde)
file exists and then adding that to the References collection. It is
possible that the reference will subsequently become broken by a user
deleting the corressponding file. In this case i would like to first
remove the broken reference. To test that part, i tried to run this:

Public Sub RemoveBrokenRefs()
Dim Refs As References
Dim R As Reference

Set Refs = Application.References
For Each R In Refs
If (R.IsBroken) Then
Refs.Remove (R)
Debug.Print R.Name, "*** BROKEN, Removed"
Else
Debug.Print R.Name, R.Kind, R.Major, R.Minor, R.FullPath
End If
Next R
End Sub

I get an error on the line
Refs.Remove (R)
"Object doesn't support this property or method"
Remove is a valid method of a References collection.
What's happening ?
 
This is significant since Access refuses to allow me to add a reference
if the file has been moved to another directory:

1) install library file in directory A; set reference
2) move file to directory B
3) reference is now broken but cannot (programmatically) be removed
4) attempt (programmatically) to set reference to file in directory B
5) Access error re a reference to the object already exists
-=-=-=
 
jfp said:
I am adding code to an app that will check at startup if a reference
to my code library exists. On a new installation the reference will
not exist and i will then set it up by verifying that the .mdb (or
.mde) file exists and then adding that to the References collection.
It is possible that the reference will subsequently become broken by
a user deleting the corressponding file. In this case i would like
to first remove the broken reference. To test that part, i tried to
run this:

Public Sub RemoveBrokenRefs()
Dim Refs As References
Dim R As Reference

Set Refs = Application.References
For Each R In Refs
If (R.IsBroken) Then
Refs.Remove (R)
Debug.Print R.Name, "*** BROKEN, Removed"
Else
Debug.Print R.Name, R.Kind, R.Major, R.Minor, R.FullPath
End If
Next R
End Sub

I get an error on the line
Refs.Remove (R)
"Object doesn't support this property or method"
Remove is a valid method of a References collection.
What's happening ?
Click to expand...

The code is incorrect. Drop the parentheses and write this instead:

Refs.Remove R

However, I believe you're going to fail on the next line,
Debug.Print R.Name, "*** BROKEN, Removed"
Click to expand...

since R will have been destroyed by removal. I suggest you move that
line up one, to place it before the Refs.Remove line.
 
jfp said:
This is significant since Access refuses to allow me to add a
reference if the file has been moved to another directory:

1) install library file in directory A; set reference
2) move file to directory B
3) reference is now broken but cannot (programmatically) be removed
4) attempt (programmatically) to set reference to file in directory B
5) Access error re a reference to the object already exists
Click to expand...

Although I believe you can remove the broken reference programmatically,
as I described in my earlier post, for this process as a whole you may
want to look over the cautions and procedures set out Michael Kaplan in

http://www.trigeminal.com/usenet/usenet026.asp
INFO: How to guarantee that references will work in your
applications
 
Dirk said:
The code is incorrect. Drop the parentheses and write this instead:

Refs.Remove R
Click to expand...

Thanks! But, still questions:
I realize that, starting with the original Basic, the syntax was awful
and the VBA inclusion of object oriented concepts has made it even more
confusing. Even so, i do not understand what is wrong with .Remove(R)

a) the compiler took it
b) i set a breakpoint at the line in question. as originally stated,
trying to execute it gave me the "object does not support ..." message.
So, i figured i perhaps needed the name of the reference and then tried
..Remove(" blah blah ") from the immediate pane. For this, i got a
compiler error: "Type mismatch". This implies to me that the syntax
with () is acceptable -- obviously the compiler is expecting a certain
type (not a string) inside those ().

What's up ?
 
jfp said:
Thanks! But, still questions:
I realize that, starting with the original Basic, the syntax was awful
and the VBA inclusion of object oriented concepts has made it even
more confusing. Even so, i do not understand what is wrong with
.Remove(R)

a) the compiler took it
b) i set a breakpoint at the line in question. as originally stated,
trying to execute it gave me the "object does not support ..."
message. So, i figured i perhaps needed the name of the reference and
then tried Remove(" blah blah ") from the immediate pane. For this,
i got a compiler error: "Type mismatch". This implies to me that
the syntax with () is acceptable -- obviously the compiler is
expecting a certain type (not a string) inside those ().
Click to expand...

No. Actually, the compiler is not expecting the parentheses at all;
they are not appropriate in this "imperative" form of method call, and
so are interpreted differently than you think. If you had written

Call Refs.Remove(R)

*then* the parentheses would have been required, and would be
interpreted as enclosing the argument to the Remove method. But you
didn't write it that way. You wrote this:

Refs.Remove(R)

which the compiler promptly reformatted to this:

Refs.Remove (R)

Note the space that the compiler added before the opening parenthesis.
This is a dead giveaway that the parentheses are not being interpreted
as part of the method-call syntax, but rather as "doing something" with
the argument R. I'll explain what in a minute. Right now, the point
I'm trying to make is that VBA allows several different syntaxes for
calling procedures. For subs (and for methods that don't return a
value) you can either write

Call procname(arguments...)

or

procname arguments...

For functions (and for methods that return a value) you can write

x = procname(arguments...)

if you want the returned value, or

Call procname(arguments...)

or

procname arguments...

if you don't care about the value. Notice the circumstances under which
the parentheses are part of the call syntax, and those in which they are
not. So when you wrote

Refs.Remove(R)

the parentheses are *not* part of the correct call syntax, and so are
not interpreted as such.

How then *are* they interpreted, and why didn't the compiler complain?
Recognize that parentheses around an expression are always valid in VB.
They have the effect of forcing that expression to be evaluated, and the
result substituted in further processing. This is obvious in the case
of

x = (y + 27) * 3

The parentheses instruct the compiler to evaluate the expression y + 27
first, then multiply the result by 3. When you enclose an *object* in
parentheses, what you are asking the compiler to do is evaluate that
object. Many objects have a default property, and this is what is
returned when you the object is evaluated. For example, for Access
controls, the default property is the Value property. If I have a form
named "Form1" open, with an unbound text box on it named "Text0", look
what I get if I enter certain statements in the Immediate Window:

?TypeName(Forms!Form1!Text0)
Textbox
?TypeName((Forms!Form1!Text0))
String

In both calls to the TypeName function, the outermost parentheses are
those required by the function-call syntax. In the first call, that's
all there is, so the function tells me that its argument is of type
Textbox. In the second call, there's an extra, inner set of parentheses
around the control reference, forcing it to be evaluated before passing
it to the function. Evaluating the control returns its Value, so that's
what gets passed to TypeName, which tells us that the value is of type
String.

So in the case of your code, at the run-time interpreter looks at

Refs.Remove (R)

and attemps to evaluate the Reference object R and pass its value to the
Remove method. However, objects of the Reference type have no default
property, so there's no value that can be returned and passed to the
Remove method. You asked for the default property and there is no
default property, so you get the error, "Object doesn't support this
property or method."

What the Remove method *wants* as its argument is the reference object
itself. That's what you pass it if you write

Refs.Remove R

And then it works.
 
Back
Top