Re: file browser when a cell in selected

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Hank Scorpio (ApolloXVIII_at_Hates.Spam)
Date: 02/04/04 

Date: Wed, 04 Feb 2004 21:49:20 +1100

On Tue, 3 Feb 2004 14:22:29 -0000, "Lorne"
<Lorne_Anderson@hotmail.com> 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" <ApolloXVIII@Hates.Spam> wrote in message
>news:klvu10d94cvlqrptd342jsljvmpjd1l2mk@4ax.com...
>> On Tue, 3 Feb 2004 04:46:35 -0600, dreamer
>> <<dreamer.111qzl@excelforum-nospam.com>> 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! *

Relevant Pages

  • Re: VB6, SQL2000 and Word
    ... Private tempdoc As Word.Document ... use RaiseEvent with the following syntax: ... assignment. ... Private Sub CreateMailMergeDataFileAs String) ...
    (microsoft.public.vb.general.discussion)
  • Re: Symbol tables
    ... > sub func1() { ... > But when I put the package FAT32 into a separate file, ... > Is there any camel-dung I can put onto my hash assignment to make it ... "Reply" at the bottom of the article headers. ...
    (comp.lang.perl.misc)
  • [PATCH] update checkpatch.pl to version 0.11
    ... add a summary to the bottom of the main report ... sub top_of_kernel_tree { ... # Pre-scan the patch looking for any __setup documentation. ... # Check for illegal assignment in if conditional. ...
    (Linux-Kernel)
  • Re: OMG. I am glad I never had to dealt with it
    ... >> static sub for? ... > IIRC, a variable or a sub can be declared as static, the value of ... which when assigned to a boolean variable, ... precedence before the assignment, and since the resulting ...
    (borland.public.delphi.non-technical)