How can I clean the buffer from the folder picker?

[Excel 009]

I am using the folder picker code from John Walken's site. I have a
Sub that brings up the folder picker dialog box. I assigned a string
variable to the selected folder name. After I ran the code, select the
folder and click Ok, the folder value is stored in the string variable.
When I ran the code again (after some procedures took place), before I
selected the folder, the string variable still had the value from the
prior selection. Does any one know how to get ride of the buffered
value without setting the string variable to a blank value. Just
curiours.

- Excel 009

 

[Guest]

Did you declare the variable locally or globally? A local variable s/b
destroyed when the sub terminates, global will not (which is a the primary
reason for using it). One other means to make a local variable retain it's
value after the sub terminates is to declare the variable as static (just an
FYI - I doubt that is the case).

Global:
Dim x As String
Sub Test
...
End Sub

Local
Sub Test
Dim x As String
...
End Sub

Static:
Sub Test
Static x As String
....
End Sub

 

[Excel 009]

Funny thing is the value of the variable was still there after the sub
is terminated.

Put the code into a module (code can be found in this link)
http://j-walk.com/ss/excel/tips/tip29.htm

assign the folder value to strX

call up the dialog box and select a folder and click Ok.

Type the following into your model.

Sub Test()
MsgBox strX
End Sub

run the sub, and strX still shows up!

I guess it may have something to do with the API, but not quite sure.
If anyone has the answer, please share.

- Excel 009

 

[Tom Ogilvy]

If you declared strX at the top of the module above an procedures, then it
is a public variabe or module level variable and will retain its value. If
instead, you declare inside the procedure, then the next time the procedure
is run it will have lost its value.

Other that, we would have to see the specific code you are running. I have
run John's code many times in the past and have had no problems or
surprises. It acted as I suspected.

 

[Excel 009]

The variable IS a public variable, but what I am curious to know is
that when will this variable be destroyed? It seems like once I
assigned value to the variable, it is there even after I run many Sub
manually. When I call up the folder picker dialog box the second time,
if I click Ok without selecting a folder, the old value shows up. I
cleared the clipboard, and it is still there.

(There is nothing wrong with John's code. I appreciate his sharing.)

 

[Guest]

I've also used that code from John's site w/o any surprises. John's macro,
however, does not store the return value into a variable, it only displays a
message box

Sub Test()
Dim Msg As String
Msg = "Please select a location for the backup."
MsgBox GetDirectory(Msg)
End Sub

whereas you are assigning it to the variable strx. The issue, I'm sure, is
not w/John's code or the API, but how you declared strx - Procedure Level or
Module Level? (inside of your sub or outside of your sub - see my first post
for examples).

 

[Guest]

Hit the post button a little to quick, I wanted to add another example. Try
this

Sub Test()
Dim Msg As String
Dim strX As String
Msg = "Please select a location for the backup."
MsgBox strX
strX = GetDirectory(Msg)
MsgBox strX
End Sub

 

[Excel 009]

Thanks J.

This is the format I have:

Book1
===================
Dim strX As String

Sub Test()
strX = GetDirectory(Msg)
End Sub


Sub Test_2()
MsgBox strX
End Sub
===================

Book2
===================
Sub Text _3()
Msgbox "Hi"
End Sub

After I ran Test(), I ran Sub Text _3() in workbook 2, then I came back
to workbook 1, ran Sub Test_2(), and the value stored in StrX is still
there. May be my real question is - how the value is preserved in a
way that when you leave the current workbook and come back, it is still
in the memory?

 

[Excel 009]

Thanks J.

This is the format I have:

Book1
===================
Dim strX As String

Sub Test()
strX = GetDirectory(Msg)
End Sub


Sub Test_2()
MsgBox strX
End Sub
===================

Book2
===================
Sub Text _3()
Msgbox "Hi"
End Sub

After I ran Test(), I ran Sub Text _3() in workbook 2, then I came back
to workbook 1, ran Sub Test_2(), and the value stored in StrX is still
there. May be my real question is - how the value is preserved in a
way that when you leave the current workbook and come back, it is still
in the memory?

 

[Guest]

When you close the workbook in which it is assigned, it will go away.

It has the scope of the project/workbook.

The easiest solution is to clear it after you use it.

You can further read the VBA help file on Scope.

 

[Excel 009]

Thanks Tom. That is answer I need. It has a workbook/project scope.
By the way, how can one declare a workbook/project scope variable? A
variable that preserves its value even after the sub is terminated.

 

[NickHK]

Isn't that what you have discovered with this current "Dim strX As String"
situation ?

The alternative is to declare the variable in the Sub/Function. Then is lost
when the routine finishes.

NickHK

 

[Excel 009]

Hi Nick,

This variable needs to be declare at the project level since its value
is used by other sub procedure. Right now I just wondering if there is
a way to declare a workbook/project scope variable (beside using the
folder picker) that will last even when the program finishs running.

- Excel 009

 

[NickHK]

If you mean the variable to keep its value even when the workbook containing
it is closed, then No.
You have to write the value somewhere suitable, possibly a cell on a hidden
sheet .

Variables declared Public in a module are visible through out the project,
if that's what you mean.

NickHK

 

[Excel 009]

No, that was not what I meant, Nike. What I meant was if there is a
way to declare a variable that when all the Subs finished running, the
variable will not be destroyed; the value will still be there as long
as the workbook remains opened. (A variable that has the same
characteristic as strX used in the folder picker.)

 

[Excel 009]

Nick,

No, I do not need a variable the retains its value when the workbook
containing it is closed since I know it is not possible unless we write
the info into Window registry or into a file. What I want to know is
if there is a way to declare a variable with workbook scope that
behavior as the one used in the Folder picker (which retains its value
after the all the subs finish running).

- Excel 009

 

[NickHK]

Yes,
Just Dim another variable under you StrX variable.
As you have seen it keeps its value outside the routine.
Public variables in modules are visible throughout the project.

NickHK

 

[Excel 009]

Hi Nike,

What do you meant by dim another variable under strX?

When a variable is dim as public, although it can be called through out
the project, but once the program is terminated (with the workbook
still remaining open), the variable got destroyed. So just dim the
variable as public is not good enough.

May be I should make a new post on this question.

- Excel 009

 

[NickHK]

I suppose I'm confused by what you mean by "program".
How is the "program" different from the workbook ?

NickHK

 

[Excel 009]

Hi Nike,

What do you meant by dim another variable under strX?

When a variable is dim as public, although it can be called through out
the project, but once the program is terminated (with the workbook
still remaining open), the variable got destroyed. So just dim the
variable as public is not good enough.

May be I should make a new post on this question.

- Excel 009