I am teaching myself VBA by reading this newsgroup and getting understanding
the code you all kindly put on it and can now do quite a surprising amount
of things, but I have a question. Almost always the code sets something
equal to a variable and then tests the variable rather than simply testing
whatever the something is - you do it in your solution below & so does Frank
Kabel in the same thread. Why don't you use:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address <> $A$2" Then Exit Sub
If Application.GetOpenFilename("Excel Files (*.xls), *.xls") Then
Target.Value = Application.GetOpenFilename("Excel Files (*.xls),
*.xls")
End If
End Sub
As Tom pointed out in a later message, the code that you have above
will call the dialog twice and (as Dave also pointed out) there's no
guarantee that the user will make the same selection on both
occasions.
Generally speaking, though, the question of whether to assign a value
to a variable or to use it directly just depends on circumstances and,
to some extent, a person's particular style.
In some cases a value returned by a function or method will need to be
used more than once. In such cases it obviously makes sense to assign
it to a variable. In others, the return value may be of different data
types depending on the input. That's the case with the
Application.GetOpenFilename method. A valid assignment will return a
string of text. Cancelling the dialog, however, will return a logical
value of False. While I COULD have done a direct assignment like:
Target.Value = _
Application.GetOpenFilename("Excel Files (*.xls),*.xls")
then if the user cancelled the dialog the value False would end up
being stored in the cell A2. I assumed that the developer and user
wouldn't want that to happen, so I only put the value into the cell if
the value returned by the method is NOT False.
Another reason can be that some of the return values of a function or
method may not be compatible with your following line(s) of code. In
such a case, it makes sense to test the returned value first to
prevent a type mismatch or similar error from occurring. This is
particularly so when a function or method returns a variant value,
which, as we've already seen, could be any data type depending on the
circumstances.
And in some cases, assigning to a variable just makes the code easier
to read and more self documenting.
There isn't always One Right Way, but these are some of the factors
that can help determine the choice of approach.
I am trying to understand if there is a technical reason for the extra steps
of creating the variable vnt and setting it equal to
Application.GetOpenFilename or whether it is just the way everyone is taught
to do it.
Many thanks if you can spare the time help (or if there is a web resource to
answer such questions).
There are quite a few web resources, such as:
http://j-walk.com/ss/
and
http://www.cpearson.com/excel.htm
but for specific questions like this one it's probably faster to just
ask it in the Usenet group.
