How can I clean the buffer from the folder picker?
 

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

How can I clean the buffer from the folder picker?
 

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" wrote:

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

How can I clean the buffer from the folder picker?
 

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

How can I clean the buffer from the folder picker?
 

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.

--
Regards,
Tom Ogilvy


"Excel 009" wrote in message
oups.com...
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

How can I clean the buffer from the folder picker?
 

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.)

How can I clean the buffer from the folder picker?
 

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).


"Excel 009" wrote:

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

How can I clean the buffer from the folder picker?
 

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



"JMB" wrote:

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).


"Excel 009" wrote:

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

How can I clean the buffer from the folder picker?
 

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?

How can I clean the buffer from the folder picker?
 

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?

How can I clean the buffer from the folder picker?
 

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.

--
Regards,
Tom Ogilvy



"Excel 009" wrote:

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?

How can I clean the buffer from the folder picker?
 

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.

How can I clean the buffer from the folder picker?
 

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" wrote in message
ups.com...
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.

How can I clean the buffer from the folder picker?
 

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

How can I clean the buffer from the folder picker?
 

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"
groups.com...
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

How can I clean the buffer from the folder picker?
 

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.)

How can I clean the buffer from the folder picker?
 

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

How can I clean the buffer from the folder picker?
 

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" wrote in message
oups.com...
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

How can I clean the buffer from the folder picker?
 

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

How can I clean the buffer from the folder picker?
 

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

NickHK

"Excel 009"
groups.com...
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

How can I clean the buffer from the folder picker?
 

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

How can I clean the buffer from the folder picker?
 

What I meant
program = Subs
workbook = Excel file

When the program finishs, a public variable will be terminated, but I
want to know if there is a way to keep it still alive.

Here is an example:

Say this is the only code in my module in the whole project.

Public strX as String

Function getString(str as String) as String
strX = str
getString = strX
End Function

Sub x()
Msgbox getString
End Sub

After I ran Sub x(), the

The message came up and after I click Ok, the program terminated. stX
is lost.
I want to know if there is a way to keep it still alive (not store it
physically some where, but only in memory).

How can I clean the buffer from the folder picker?
 

Change your code slightly yo that below. Then call it repaeted from the
button click. The value of the variable is retianed.
I just thought; you're not using End anywhere in your code are you ? Because
that clear all variables.

NickHK

'<Worksheet code
Private Sub CommandButton3_Click()
Call x
End Sub
'</Worksheet code

'< Module code
Public strX As String

Sub x()
If Len(strX) = 0 Then
MsgBox getString("New Value")
Else
MsgBox "the value of strX is " & Chr(34) & strX & Chr(34)
End If
End Sub

Function getString(str As String) As String
strX = str
getString = strX
End Function
'</ Module code

NickHK

"Excel 009"
egroups.com...
What I meant
program = Subs
workbook = Excel file

When the program finishs, a public variable will be terminated, but I
want to know if there is a way to keep it still alive.

Here is an example:

Say this is the only code in my module in the whole project.

Public strX as String

Function getString(str as String) as String
strX = str
getString = strX
End Function

Sub x()
Msgbox getString
End Sub

After I ran Sub x(), the

The message came up and after I click Ok, the program terminated. stX
is lost.
I want to know if there is a way to keep it still alive (not store it
physically some where, but only in memory).

How can I clean the buffer from the folder picker?
 

Thanks, Nick. What I need to know is a way to retain the StrX value
after all the subs finished running.

For example, I created

Sub y()
MsgBox strX
End Sub

in additional to your code. When I clicked the button, the message
comes up with the StrX value. After I clicked "OK" on the message box,
the sub finishs.

Now I run Sub Y. The message is blank because the StrX value is not
retained. In the situation where the Folder Picker is used, the value
is still there.