Results 1 to 10 of 10
  1. #1
    monvani is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Posts
    6

    String to Reserved

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

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

    Code:
    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


  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,511
    color is NOT a string, dont define AAA as string, dont assign the value as string...its numeric.

    AAA = msoThemeColorAccent2

  3. #3
    monvani is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Posts
    6
    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...

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,752
    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?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    monvani is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Posts
    6
    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.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,752
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    monvani is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Posts
    6
    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...

    Code:
    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

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,752
    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
    ...
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,707
    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

  10. #10
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by June7 View Post
    <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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Reserved Word 'REPORT$'
    By JeffChr in forum Access
    Replies: 4
    Last Post: 02-12-2014, 06:56 AM
  2. Reserved Error (|)
    By RGRAVLIN_BOS in forum Access
    Replies: 4
    Last Post: 06-14-2012, 07:09 AM
  3. Reserved error (-1104)?
    By jlclark4 in forum Access
    Replies: 5
    Last Post: 02-03-2012, 10:22 AM
  4. Reserved word
    By squirrly in forum Access
    Replies: 6
    Last Post: 09-20-2011, 02:43 PM
  5. Reserved Error -1517
    By Rick West in forum Queries
    Replies: 5
    Last Post: 12-10-2009, 02:12 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums