Unsing names to store input from Userforms

[Guest]

I prefer to avoid writing intermediate values to the sheet. It makes the
spreadsheet difficult to modify. I would like to store the inputs from a
userform in a name eg
ActiveWorkbook.Names.Add Name:="MyNamep", RefersToR1C1:="5"

Theoretically this means that if I want to use this value in a cell, I can
say
=MyName

The problem is that I want to use the name value to initialise the control
on my form so tha next time the form is opened, the previous selection is
set. It appears that Msgbox names("MyName").value renders a TEXT value ="5"
and not a numerical one.

I wanted to use this value in a Select Case statement, but it seems working
with a TEXT value may be difficult. Is there a way I can get a numerical
value instead?

I would also like to be able to say
NewVariable = 3.14 * names("MyName").value

Any Suggestions ??

.- -. Bye Bye
/|6 6|\ - Pavlos
{/(_0_)\}
/ ^ \_
(/_/^\_\)

 

[Guest]

Type convert the string using Val(names("MyName").value)
or CDbl(names("MyName").value). If you may have decimal
values and use ',' as separator Val will only return
numbers to the left of the decimal separator as it only
recognize '.' as such.

ojv

 

[Guest]

I tried this but it just renders a zero. It seems that for the Val command to
work, you need to enter the actual string. eg
Val(" This is my 2nd attempt") renders the value 2

 

[Guest]

Sorry, use Range("MyName").Value instead of Names
("MyName").Value as the value property of Name returns
the formula the name refers to.

ojv

 

[Tom Ogilvy]

Sorry, use Range("MyName").Value instead
That will raise an error since MyName isn't a range.

to the OP

You can use evaluate. Demonstrated from the immediate window:

? Names("Myname").Refersto
=5
? evaluate(Names("MyName").Refersto)
5
? application.IsNumber(evaluate(Names("MyName").Refersto))
True

Or you can parse out the equal sign and do the conversion.

Dim sStr as String, lVal as Long
sStr = Names("MyName").Refersto
lVal = cdbl(right(sStr,len(sStr)-1))