Who can recommend about DIM?

danial

Board Regular
Joined
Apr 29, 2006
Messages
107
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?
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
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

Board Regular
Joined
Apr 29, 2006
Messages
107
This is Thai flag.
Red is the Nation
White is the Buddhist
Blue is the King (all Thai people love him)
 

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
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

Well-known Member
Joined
Dec 5, 2002
Messages
8,254
Office Version
  1. 2016
Platform
  1. Windows
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 :

Code:
Function MyVar() As Variant

    MyVar = Var

End Function

Where Var is a Public Variable .


Now, in the Target workbook, run this Code :

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.
 

Forum statistics

Threads
1,137,339
Messages
5,680,910
Members
419,940
Latest member
WilliamPorter

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top