Hi!!

A variabel named in a form (general)
how and can I use this variabel in a macro (module)

Best regards alvin

Either pass it to the function in the module:

ModuleFunc(variable1)


or dont "dim" this in your form but rather dim it as
public in a module - and you can then access this variable
anywhere. However "global" variables of this type are
generally considered bad form and can lead to messy code
that is hard to debug. Generally you want to scope your
variables as tightly as possible.


-----Original Message-----
Hi!!

A variabel named in a form (general)
how and can I use this variabel in a macro (module)

Best regards alvin

.

Sorry - forgot to enter my name!


ttfn benm

A variabel named in a form (general)

Just in the same place change Dim to Public
e.g. Public vairable1.

Then in the module you can access it with the userfromname.

e.g.: userform1.variable1
But keep in mind that, by using it as above in a module, during run
time if the userform is not loaded it will get loaded (only loaded not
shown.)

Sharad

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Alvin,

It depends when you want to use it. If you want to use it whilst the form is
still in memory, you can declare it as a public property in the form.
outside of procedures, and access it as a property of the form class.
Something like

'--- in the form

myFormVar = "testing"
myModule
'...

'--- in themodule
Public Sub myModule()

Msgbox Userform1.myFormVar

End Sub

If the form won't be in memory, you need to declare it as a public variable
in a module and just access it directly, without any qualifictaion. This is
clearly the more flexible.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Alvin Hansen" wrote in message
...
Hi!!

A variabel named in a form (general)
how and can I use this variabel in a macro (module)

Best regards alvin

Hi
Thanks its working now
but i also use this variabel in a sheet
here it dosn't work ?

Alvin


" skrev:

Either pass it to the function in the module:

ModuleFunc(variable1)


or dont "dim" this in your form but rather dim it as
public in a module - and you can then access this variable
anywhere. However "global" variables of this type are
generally considered bad form and can lead to messy code
that is hard to debug. Generally you want to scope your
variables as tightly as possible.


-----Original Message-----
Hi!!

A variabel named in a form (general)
how and can I use this variabel in a macro (module)

Best regards alvin

.

But if the userform is not loaded, it will have no value. Unloading the form
clears the variables, so that's no good.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Sharad" wrote in message
...
A variabel named in a form (general)

Just in the same place change Dim to Public
e.g. Public vairable1.

Then in the module you can access it with the userfromname.

e.g.: userform1.variable1
But keep in mind that, by using it as above in a module, during run
time if the userform is not loaded it will get loaded (only loaded not
shown.)

Sharad

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Not sure of the best suggestion here as its not something
I normally do. But one obvious way is to wrtite a public
function in your module - this code assumes your global is
called fred and is a string. I also entered a set method
for testing.



Public Fred As String



Public Function GetFred()

GetFred = Fred

End Function

Public Function SetFred(AString As String)

SetFred = AString
Fred = AString

End Function

If you have declared it as public variable in a module, it should be
accessible anywhere, including a sheet.

What does not working mean exactly, an error or a wrong value? Where did you
store the variable?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Alvin Hansen" wrote in message
...
Hi
Thanks its working now
but i also use this variabel in a sheet
here it dosn't work ?

Alvin


" skrev:

Either pass it to the function in the module:

ModuleFunc(variable1)


or dont "dim" this in your form but rather dim it as
public in a module - and you can then access this variable
anywhere. However "global" variables of this type are
generally considered bad form and can lead to messy code
that is hard to debug. Generally you want to scope your
variables as tightly as possible.


-----Original Message-----
Hi!!

A variabel named in a form (general)
how and can I use this variabel in a macro (module)

Best regards alvin

.

Well, then another way to do it could be write the variable in a cell in
a worksheet. With this you even need not declare the variale as public,
simply do Dim variable1.
Select an isolated cell in the worksheet and name the cell as say
"ufVar1".
In the form code, where ever this variable is being assinged a value,
immediately below you can add

Range("ufVar1").Value = variable1

But you need to add above line, below each line in form code where
'variable1 =' appears.

In the sheet you can now use ufVar1 as variable e.g.
formula in a cell : = ufVar1

In the module you can use the variable as Range("ufVar1").Value

Sharad

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

or a workbook name

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Sharad" wrote in message
...

Well, then another way to do it could be write the variable in a cell in
a worksheet. With this you even need not declare the variale as public,
simply do Dim variable1.
Select an isolated cell in the worksheet and name the cell as say
"ufVar1".
In the form code, where ever this variable is being assinged a value,
immediately below you can add

Range("ufVar1").Value = variable1

But you need to add above line, below each line in form code where
'variable1 =' appears.

In the sheet you can now use ufVar1 as variable e.g.
formula in a cell : = ufVar1

In the module you can use the variable as Range("ufVar1").Value

Sharad

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Ok i try to explain

In my form i have
omroeder = omroeder.Value
in a module i have
Public omroeder

In a shette i have:
INDIRECT.EXT("'c:\city breaks\priser\usa\"& omroeder
&"\["&beregn!$B$1&".xls]prisliste'!$b$1"))

and here i get an error it can't find this file

Alvin


"Bob Phillips" skrev:

If you have declared it as public variable in a module, it should be
accessible anywhere, including a sheet.

What does not working mean exactly, an error or a wrong value? Where did you
store the variable?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Alvin Hansen" wrote in message
...
Hi
Thanks its working now
but i also use this variabel in a sheet
here it dosn't work ?

Alvin


" skrev:

Either pass it to the function in the module:

ModuleFunc(variable1)


or dont "dim" this in your form but rather dim it as
public in a module - and you can then access this variable
anywhere. However "global" variables of this type are
generally considered bad form and can lead to messy code
that is hard to debug. Generally you want to scope your
variables as tightly as possible.


-----Original Message-----
Hi!!

A variabel named in a form (general)
how and can I use this variabel in a macro (module)

Best regards alvin

.

If you mean that
INDIRECT.EXT("'c:\city breaks\priser\usa\"& omroeder
&"\["&beregn!$B$1&".xls]prisliste'!$b$1"))
is a worksheet function, you are way off track here. You cannot access a VBA
variable directly from a worksheetfunction , you would need to use a UDF and
call that to get the value, but of course, your true will have ended by then
so the variable is likely to be empty unless you declare it static.

Your best way will be to use a worksheet cell and save the value in there
and add that in the worksheetfunction (or use a workbook name).

BTW, how does omroeder = omroeder.Value work, it is not making sense to me?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Alvin Hansen" wrote in message
...
Ok i try to explain

In my form i have
omroeder = omroeder.Value
in a module i have
Public omroeder

In a shette i have:
INDIRECT.EXT("'c:\city breaks\priser\usa\"& omroeder
&"\["&beregn!$B$1&".xls]prisliste'!$b$1"))

and here i get an error it can't find this file

Alvin


"Bob Phillips" skrev:

If you have declared it as public variable in a module, it should be
accessible anywhere, including a sheet.

What does not working mean exactly, an error or a wrong value? Where did
you
store the variable?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Alvin Hansen" wrote in message
...
Hi
Thanks its working now
but i also use this variabel in a sheet
here it dosn't work ?

Alvin


" skrev:

Either pass it to the function in the module:

ModuleFunc(variable1)


or dont "dim" this in your form but rather dim it as
public in a module - and you can then access this variable
anywhere. However "global" variables of this type are
generally considered bad form and can lead to messy code
that is hard to debug. Generally you want to scope your
variables as tightly as possible.


-----Original Message-----
Hi!!

A variabel named in a form (general)
how and can I use this variabel in a macro (module)

Best regards alvin

.

Hi

as far as I am aware youy cannotreference variable
directly in a sheet (I could be wrong - quite new to
Excel) This is why I sent you those two functions - put
those in your module also and replace the variable name in
your formula with the function getfred() or whatever you
call it.

Ben,

that is unlikely to work, see my reply as to why.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ben McBen" wrote in message
...
Hi

as far as I am aware youy cannotreference variable
directly in a sheet (I could be wrong - quite new to
Excel) This is why I sent you those two functions - put
those in your module also and replace the variable name in
your formula with the function getfred() or whatever you
call it.

Wll
in my form i have
omroeder = omroeder.value ( omroeder.value is from a combobox)

My variabel omroeder i use in a module with
GetData "c:\city breaks\priser\usa\" & omroeder & "\byer.xls", "byer",
"A1:C200", Sheets("byer").Range("A1"), True

and here my variabel "omroeder" works

Alvin

"Bob Phillips" skrev:

If you mean that
INDIRECT.EXT("'c:\city breaks\priser\usa\"& omroeder
&"\["&beregn!$B$1&".xls]prisliste'!$b$1"))
is a worksheet function, you are way off track here. You cannot access a VBA
variable directly from a worksheetfunction , you would need to use a UDF and
call that to get the value, but of course, your true will have ended by then
so the variable is likely to be empty unless you declare it static.

Your best way will be to use a worksheet cell and save the value in there
and add that in the worksheetfunction (or use a workbook name).

BTW, how does omroeder = omroeder.Value work, it is not making sense to me?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Alvin Hansen" wrote in message
...
Ok i try to explain

In my form i have
omroeder = omroeder.Value
in a module i have
Public omroeder

In a shette i have:
INDIRECT.EXT("'c:\city breaks\priser\usa\"& omroeder
&"\["&beregn!$B$1&".xls]prisliste'!$b$1"))

and here i get an error it can't find this file

Alvin


"Bob Phillips" skrev:

If you have declared it as public variable in a module, it should be
accessible anywhere, including a sheet.

What does not working mean exactly, an error or a wrong value? Where did
you
store the variable?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Alvin Hansen" wrote in message
...
Hi
Thanks its working now
but i also use this variabel in a sheet
here it dosn't work ?

Alvin


" skrev:

Either pass it to the function in the module:

ModuleFunc(variable1)


or dont "dim" this in your form but rather dim it as
public in a module - and you can then access this variable
anywhere. However "global" variables of this type are
generally considered bad form and can lead to messy code
that is hard to debug. Generally you want to scope your
variables as tightly as possible.


-----Original Message-----
Hi!!

A variabel named in a form (general)
how and can I use this variabel in a macro (module)

Best regards alvin

.

Alvin,

Be that as it may, it won't work in a worksheet function.

As I suggested, best to save it to a worksheet/ So where you have omroeder =
omroeder.value , use
Worksheets("Sheet1").Range("IV1").Value = onroeder.value

and in your worksheet use

INDIRECT.EXT("'c:\city breaks\priser\usa\"& Sheet1!$IV$1
&"\["&beregn!$B$1&".xls]prisliste'!$b$1"))



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Alvin Hansen" wrote in message
...
Wll
in my form i have
omroeder = omroeder.value ( omroeder.value is from a combobox)

My variabel omroeder i use in a module with
GetData "c:\city breaks\priser\usa\" & omroeder & "\byer.xls", "byer",
"A1:C200", Sheets("byer").Range("A1"), True

and here my variabel "omroeder" works

Alvin

"Bob Phillips" skrev:

If you mean that
INDIRECT.EXT("'c:\city breaks\priser\usa\"& omroeder
&"\["&beregn!$B$1&".xls]prisliste'!$b$1"))
is a worksheet function, you are way off track here. You cannot access a
VBA
variable directly from a worksheetfunction , you would need to use a UDF
and
call that to get the value, but of course, your true will have ended by
then
so the variable is likely to be empty unless you declare it static.

Your best way will be to use a worksheet cell and save the value in
there
and add that in the worksheetfunction (or use a workbook name).

BTW, how does omroeder = omroeder.Value work, it is not making sense to
me?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Alvin Hansen" wrote in message
...
Ok i try to explain

In my form i have
omroeder = omroeder.Value
in a module i have
Public omroeder

In a shette i have:
INDIRECT.EXT("'c:\city breaks\priser\usa\"& omroeder
&"\["&beregn!$B$1&".xls]prisliste'!$b$1"))

and here i get an error it can't find this file

Alvin


"Bob Phillips" skrev:

If you have declared it as public variable in a module, it should be
accessible anywhere, including a sheet.

What does not working mean exactly, an error or a wrong value? Where
did
you
store the variable?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Alvin Hansen" wrote in
message
...
Hi
Thanks its working now
but i also use this variabel in a sheet
here it dosn't work ?

Alvin


" skrev:

Either pass it to the function in the module:

ModuleFunc(variable1)


or dont "dim" this in your form but rather dim it as
public in a module - and you can then access this variable
anywhere. However "global" variables of this type are
generally considered bad form and can lead to messy code
that is hard to debug. Generally you want to scope your
variables as tightly as possible.


-----Original Message-----
Hi!!

A variabel named in a form (general)
how and can I use this variabel in a macro (module)

Best regards alvin

.