Who can recommend about DIM?

[danial]

I want to declare variables for serveral procedures and I want to keep it for any time when I need to use it(not close the excel)
and I use it for multiple worksheets/workbooks
Can you give me some advice?
1. Kept value on the cell in worksheet(inconvenion on cal to use)
2.declare with "Public" (I'm not sure it's still for every time)


any way which work?

 

[RalphA]

I see that 42 people have looked at your post. It's strange that no one has answered you yet, as the answer, I believe, is known to most VBA users. I am not one of those, however. Perhaps, since my answer will "bump" your post to the beginning of the list, someone else will read it and solve your problem.

By the way, please do not feel bad about your English! I understood it very well.

Oh, by the way, isn't it curious that your flag and mu Costarrican flag are so similar, with the red and blue colors simply interchanged? We describe ours as blue, white, double red, white, and blue.

 

[danial]

This is Thai flag.
Red is the Nation
White is the Buddhist
Blue is the King (all Thai people love him)

 

[RalphA]

danial:

If you declare a varable as Public, that will make that variable available (and the same) for all procedures in your workbook. That is, any Sub or Function will be able to use the value of that variable, as well as change it. I'm not sure how to make \ that variable available from any worksheet and from any workbook. Perhaps a VBA-knowledgeable person will respond with a good answer.

 

[Jaafar Tribak]

How about storing the values in the Registry ?

Look up the SaveSetting and GetSetting VBA Methods in the Help files.

Another way is to use the Run Method.

Add a Function to the Workbook that has the Variable as follows :

Function MyVar() As Variant

    MyVar = Var

End Function

Where Var is a Public Variable .


Now, in the Target workbook, run this Code :

Sub Test()

    MsgBox Run("SourceWorkBook.xls! MyVar")

End Sub

Where SourceWorkBook.xls is the name of the WorkBook containing the Variable to be retrieved.

Regards.