String to Reserved

[monvani]

Please help - just need one last piece to make this work.

I have collected string inputs, need to convert them to reserved word.
Example

Sub Macro2()
Dim AAA As String
AAA = "msoThemeColorAccent2"

'this part doesn't work because AAA is a string
'need to convert AAA from string into a recognized keyword
Cells(1, 1).Interior.Color = AAA

'This works 
'Cells(1, 1).Interior.Color = msoThemeColorAccent2

End Sub

[ranman256]

color is NOT a string, dont define AAA as string, dont assign the value as string...its numeric.

AAA = msoThemeColorAccent2

[monvani]

ranman - Wish I didn't have to, but a large form has been prepopulated with all of these codes. Not converting the string would mean a re-write of a much larger project. I'm looking for a work around...

[June7]

Why bother with the AAA string variable?

What do you mean by 'form has been populated'? What bearing does that have on the code you posted? Why are you showing worksheet code in an Access forum?

[monvani]

I am touching a large piece of code written by someone else that has ties lots of other places. I don't want to redo the entire project.

I just want to use the text held in the string variable and do stuff with it. I used worksheet reference just to make it easy to understand.

Rather than lecture, I am just wanting the workaround to convert string to reserved name.

[June7]

msoThemeColorAccent2 is an intrinsic constant. It has numeric value. If you have "msoThemeColorAccent2" as a string in a text field of table, I don't see any way to directly convert that string to the intrinsic constant. I tried Eval() function and that didn't work.

I didn't lecture, I asked questions. Still don't understand what you are trying to do. Two replies have offered suggestions. From the code you posted I see no reason to use the AAA variable. If you want relevant suggestions then post thorough explanation and relevant code for analysis - Now that's a lecture.

[monvani]

Here's more detail on what's happening. Theres about 10k lines of code that I don't really want to wade through, so concept I think is well described below. I tried eval and some string functions but can't get there. There was a CallByName, but couldn't figure that out either.

This is solvable by select case but I don't want to have to test for every instance loaded into the array, would prefer not having that redundancy. I think original author wanted to save from using a conversion from a friendly name to VBA constant in the form, and I'm just picking it up to try and add the line option. Yes....this is excel.
Thanks...

Sub macro3()
Dim myChtO As ChartObject
Dim myCht As Chart
Dim mySrsC As SeriesCollection
Dim mySrs As Series

Set myChtO = ActiveSheet.ChartObjects("Chart1")
Set myCht = myChtO.Chart
Set mySrsC = myCht.SeriesCollection
'Array is created with string values of a bunch of VBA constants
dasArray = Array("<No Chg>", "msoLineSolid", "msoLineDash", "msoLongDash", _
                "msoLineRoundDot", "msoLineSquareDot", "msoLineDashDot")
'Array is then loaded into a userform listbox
For l = 0 To UBound(dasArray, 1)  ' widArray count of items
    ListBox1.AddItem dasArray(l)
Next l
'Form is loaded, user selets which type of VBA constant they want to apply to something, say a graphed line
'user selects "msoLineDash"
AAA = ListBox1.Value
'now AAA = "msoLineDash"
'in this one line of code, I want to convert the chosen string from the form into a VBA constant value
mySrs.Format.Line.DashStyle = AAA
End Sub

[June7]

Options:

1. make the listbox multi-column - one column has the numeric value of the constant (this can be hidden from user) and another column shows descriptive text - the numeric column is the BoundColumn

dasArry = Array("<No Chg>", "msoLineSolid", "msoLineSquareDot", "msoLineRoundDot", "msoLineDash", "msoLineDashDot")
'Array is then loaded into a userform listbox
For l = 0 To UBound(dasArray, 1) ' widArray count of items
ListBox1.AddItem l & ";" & dasArray(l)
Next l
...
mySrs.Format.Line.DashStyle = Me.ListBox1

I dropped msoLongDash because it does not have a numeric value. I tested in VBA immediate window:
?msoLongDash

The listbox could probably be populated by reference to a cell range that has the constant numbers and names, instead of VBA array looping.

2. Select Case structure that will take the descriptive text and return the numeric value (I know you wanted to avoid so this is option 2, not 1)
Dim AAA As Integer
Select Case Me.listboxname
Case "msoLineSolid"
AAA = msoLineSolid
...

[orange]

monvani,

Yes....this is excel.

This is an Access forum. Have you tried posting on another forum that is focused on Excel?
eg http://www.access-programmers.co.uk/...splay.php?f=55

[ssanfu]

<snip>
I dropped msoLongDash because it does not have a numeric value. I tested in VBA immediate window:
?msoLongDash
</snip>

I think this is a typo. Should be "msoLineLongDash"
Here is the line type enumerations:

MsoLineDashStyle Enumeration

Specifies the dash style for a line.

Name	Value	Description
msoLineDash	4	Line consists of dashes only.
msoLineDashDot	5	Line is a dash-dot pattern.
msoLineDashDotDot	6	Line is a dash-dot-dot pattern.
msoLineDashStyleMixed	-2	Not supported.
msoLineLongDash	7	Line consists of long dashes.
msoLineLongDashDot	8	Line is a long dash-dot pattern.
msoLineRoundDot	3	Line is made up of round dots.
msoLineSolid	1	Line is solid.
msoLineSquareDot	2	Line is made up of square dots.

Just a note: Since there are not declarations for "dasArray" and "AAA", probably do not have "Option Explicit" at the top of the module.
IMO, should always have the line "Option Explicit" at the top of every module.