?
Solved

Set Object Properties to a Chart from VBA

Posted on 2009-12-31
11
Medium Priority
?
458 Views
Last Modified: 2013-11-25
Hello,

I use Excel formulas and named ranges to control the look of resulting charts.  Something like this;

    With Selection
        Select Case Range("Pref1DataLineType")
            Case "xlLinear"
                .Type = xlLinear
            Case "xlExponential"
                .Type = xlExponential
            End Select
    End With

And that works, however, I would much to prefer to reduce the amount of VBA logic involved.  But I just can't get this concept to work;

    With Selection
        .Type = Range("Pref1DataLineType")
    End With
   
What am I missing?  

Thanks,
Mike.  
0
Comment
Question by:Michael Hale
  • 5
  • 2
  • 2
  • +1
11 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26156444
Try:

    With Selection
        .Type = Range("Pref1DataLineType").Value
    End With

Kevin
0
 

Author Comment

by:Michael Hale
ID: 26156638
Kevin,  

Thank you for that.  I should have mentioned that Trim and .Value were the first things I tried.  The value being pushed into the chart is ignored, unless I use my earlier method.  

Mike.  
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 1000 total points
ID: 26159282
Oh nuts! I can't believe I posted what I did!!! I'm such an idiot some times!

The only values that the Type property accepts are enumerated integers:

Public Enum XlTrendlineType
   xlExponential = 5
   xlLinear = -4132
   xlLogarithmic = -4133
   xlMovingAvg = 6
   xlPolynomial = 3
   xlPower = 4
End Enum

This means you HAVE to translate the text value to the enumerated value. There is no other way to do it.

Kevin
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 26159421
Hello Mike,

There is a way, but it's a little convoluted.  It uses the TypeLib Information library, TLBINF32.dll.  It is part of a
standard install of VB6, and Microsoft used to make it available for a free download, but I am not finding it
any more at the link below.  There are...other ways...to get the DLL, but be advised that getting from any
source rather than Microsoft is legally dubious.

Assuming you have the DLL registered, you could add the function below to your project:

Function GetEnum(EnumItem As Variant, EnumName As String, TypeLibFile As String) As String
   
    ' Function based on code found at:
    ' http://www.jasonbock.net/JB/FindingEnumerationNames.aspx
   
    ' Modified by Patrick Matthews to:
    ' 1) Use late binding
    ' 2) Force text compare mode instead of binary compare mode
    ' 3) Allow for 'find membername given value' or 'find value given membername'
    ' 4) Provide more descriptive error results
   
    ' For this function to work, the component TLBINF32.dll must be installed; it is normally
    ' part of Visual Studio / Visual Basic, and not necessarily VBA
    ' You can download TLBINF32.dll at http://support.microsoft.com/kb/224331
       
    ' For the TypeLibFile argument, pass either a DLL, OLB, or EXE file name.
   
    ' GetEnum(-4162, "XlDirection", "Excel.exe") returns "xlUp"
    ' GetEnum("XlUp", "XlDirection", "Excel.exe") returns -4162
    ' GetEnum("foo", "XlDirection", "Excel.exe") returns "Error: Member not found"
    ' GetEnum(97, "XlDirection", "Excel.exe") returns "Error: Member not found"
    ' GetEnum(-4162, "XlDirexxxxx", "Excel.exe") returns "Error: Enumeration not found"
    ' GetEnum("XlUp", "XlDirection", "Excellll.exe") returns "Error: Type library not found"
   
    On Error GoTo Error_GetEnum
   
    Dim oTLIApp As Object 'TLI.TLIApplication
    Dim oTLI As Object 'TypeLibInfo
    Dim oTL As Object 'TypeInfo
    Dim oTLMember As Object 'MemberInfo
   
    Const TKIND_ENUM As Long = 0 'This is an enumerated constant in TLI
   
    GetEnum = "???"
   
    Set oTLIApp = CreateObject("TLI.TLIApplication")
    Set oTLI = oTLIApp.TypeLibInfoFromFile(TypeLibFile)
   
    GetEnum = "Error: Enumeration not found"
   
    For Each oTL In oTLI.TypeInfos
        If oTL.TypeKind = TKIND_ENUM And StrComp(oTL.Name, EnumName, vbTextCompare) = 0 Then
            '  Now we have to search each member to find the correct value.
            For Each oTLMember In oTL.Members
                If IsNumeric(EnumItem) Then
                    EnumItem = CLng(EnumItem)
                    If oTLMember.Value = EnumItem Then
                        GetEnum = oTLMember.Name
                        GoTo CleanUp
                    End If
                Else
                    If StrComp(EnumItem, oTLMember.Name, vbTextCompare) = 0 Then
                        GetEnum = oTLMember.Value
                        GoTo CleanUp
                    End If
                End If
                GetEnum = "Error: Member not found"
            Next oTLMember
        End If
    Next oTL
       
CleanUp:
   
    Set oTLMember = Nothing
    Set oTL = Nothing
    Set oTLI = Nothing
    Set oTLIApp = Nothing
   
    Exit Function
   
Error_GetEnum:
   
    GetEnum = "Error: Type library not found"
    GoTo CleanUp
   
End Function



Your code then becomes:

Selection.Type = GetEnum(Range("Pref1DataLineType"), "XlTrendlineType", "Excel.exe")

Regards,

Patrick
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26159472
Uh, dude, I know you are an MVP and really super smart and all that but the Asker said:

   "And that works, however, I would much to prefer to reduce the amount of VBA logic involved."

:-)
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 26159613
Only small minds let the actual question get in the way of a convoluted answer :)
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26159627
:-)
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 26170286
If the cell containing the constant name is being populated in code, then just change that code to use the values rather than the name.
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26176053
What Rory is suggesting is to use the enumeration values instead of strings in the cell "Pref1DataLineType".

So put into the cell -4132 instead of "xlLinear" and 5 instead of "xlExponential". Then you can use the cell contents as I first suggested:

   .Type = Range("Pref1DataLineType").Value

The problem is that, if you are presenting a readable list to the user from which to select, you still have to do the translation somewhere so, at the end of the day, what's the point.

:-)

Kevin
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 26176158
If you were presenting a lit to the user, you would presumably not use the enum constant names either!! :)
0
 

Author Closing Comment

by:Michael Hale
ID: 31671707
Worked like a charm.  
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question