On Tue, 3 Feb 2004 14:22:29 -0000, "Lorne"
wrote:
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.
"Hank Scorpio" wrote in message
.. .
On Tue, 3 Feb 2004 04:46:35 -0600, dreamer
wrote:
Is there a way to make the dialog box which opens a file appear when a
certain cell is selected? For example if cell A2 is selected, a
dialogbox for opening a file asks user which file should be opened and
the writes the name of the file to cell A2.
Right click on the sheet tab and select "View Code", then paste this
into the module that appears:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim vnt As Variant
If Target.Address = "$A$2" Then
vnt = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If vnt = False Then
Exit Sub
Else
Target.Value = vnt
End If
End If
End Sub
---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *