VBA Excel Compatibility

  1. 6 weeks ago

    Hi,

    Perhaps someone can help me with this topic. I'm trying to create shapes in Excel from RS/Xojo. I'm getting some compatibility problems because not all shapes properties that I use in VBA Excel are being shown in Xojo. For example,

    dim xls as new ExcelApplication
    dim ws as ExcelWorksheet
    dim so as ExcelShape
    
    
    xls.Visible=true
    xls.Workbooks.Add
    ws=xls.Sheets(1)
    so=ws.Shapes.AddShape(1,0,0,100,100)
    
    so.Name="MyShape"
    so.TextFrame.Characters.Text="Hello world"
    so.TextFrame.Characters.Font.Size=9
    
    //Font color 
    so.Fill.ForeColor.RGB=RGB(188,105,113)   //* Don't works
    //-->VBA code
    //so.TextFrame2.TextRange.Font.Fill.ForeColor.RGB=RGB(188,105,113)
    
    //Shape backgroung color
    so.Fill.BackColor.RGB=RGB(188,105,113)   //* Don't works
    //-->VBA code
    //so.Fill.ForeColor.RGB
    
    //Shape border color
    so.Line.ForeColor.RGB=RGB(254,86,33)  //* Don't works
    so.Fill.Transparency=0.2
    

    The code to change the color or apply some style on the shape don't works. I don't know if this is a Xojo limitation or I must use other complementary code to get my goal.

    Does anyone know how is that Xojo works with VBA ? I mean, the integration depend on the VBA library? Or uses directly the Excel objects model ..?

    Any suggestion will be appreciated.

  2. Eugene D

    Feb 17 Pre-Release Testers, Xojo Pro Canada scispec.ca

    @Elvis A The code to change the color or apply some style on the shape don't works. I don't know if this is a Xojo limitation or I must use other complementary code to get my goal.

    Hello Elvis,

    Microsoft Excel uses a colour index instead of RGB values. VBA code converts the RGB(188,105,113) value to the colour index value 7432636. This is explained in the I Wish I Knew How to... Program Excel 2016 with Xojo in Windows book in Example 3-8. A Google search may show a converter that does this for you, and I couldn't easily find one at the moment.

    Here is the modified code that should work with colour index numbers instead of RGB values for Microsoft Excel:

      dim xls as new ExcelApplication
      dim ws as ExcelWorksheet
      dim so as ExcelShape
      
      xls.Visible=true
      xls.Workbooks.Add
      ws=xls.Sheets(1)
      so=ws.Shapes.AddShape(1,0,0,100,100)
      
      so.Name="MyShape"
      so.TextFrame.Characters.Text="Hello world"
      so.TextFrame.Characters.Font.Size=9
      
      //Font color 
      so.Fill.ForeColor.RGB= 7432636 //RGB(188,105,113)   //Converted 
      //-->VBA code
      //so.TextFrame2.TextRange.Font.Fill.ForeColor.RGB=RGB(188,105,113)
      
      //Shape backgroung color
      so.Fill.BackColor.RGB=7432636 //RGB(188,105,113)   //Converted 
      //-->VBA code
      //so.Fill.ForeColor.RGB
      
      //Shape border color
      so.Line.ForeColor.RGB=2184958 //RGB(254,86,33)  //Converted
      so.Fill.Transparency=0.2

    This worked on Windows 10, Microsoft Excel 2016, with Xojo 2016 r4.1.

    Happy to help :)

    Eugene

  3. Jeff T

    Feb 17 Midlands of England, Europe

    (Blue * 256 * 256) plus (green * 256) plus red

  4. Hi Eugene, Jeff

    Thanks so much for your repply. I have created a routine to convert from RGB to color index according to your suggestion.

    I will test the .exe with Excel 2007 and W7.

    Elvis

  5. 5 weeks ago

    Hi,

    In general, all works perfectly in W7 with Office 2007, but, the code to change the font color of the shape does not works or is not correct.

    dim so as ExcelShape
    
    //Font color (RS/Xojo)
     so.Fill.ForeColor.RGB= 7432636
    
    //VBA code
    //so.TextFrame2.TextRange.Font.Fill.ForeColor.RGB=RGB(188,105,113)

    The Fill.ForeColor property, change the background color but not the font color. What's the way to get it in Xojo like in VBA?

    Thanks in advance for any suggestion.

  6. Dave S

    Feb 21 San Diego, California USA

    FYI.... the VBA color index is the same as Xojo &C datatype, just reversed

    7432636 is &H7169BC
    and RGB(188,105,113) is &cBC6971

    so your conversion would be simple

  7. Hi Dave,

    Thanks for your reply. The problem is that the code

    so.TextFrame2.TextRange.Font.Fill.ForeColor.RGB=RGB(188,105,113)

    aparently does not have equivalent in Xojo, or at least I can't find it. The ExcelShape in Xojo exposes the property TextFrame but not the TextRange.Font.Fill.ForeColor or Font.Fill.ForeColor

    I'm understand the concept of RGB and the conversion like you say , but what I need is the correct property from Xojo perspective to change the color of shape's font. I don't know how do this ...

  8. Dave S

    Feb 21 San Diego, California USA
    Edited 5 weeks ago by Dave S

    Look at TextArea StyleRuns, SelStart, SelLength, SelBold, SelTextColor

  9. Dave,

    Unfortunatelly, none of the properties like that, are being exposed into ExcelShape object. I'm trying with
    so.TextFrame.Characters.Font.ColorIndex=7432636, but an error is shown as OLE Exception ...

  10. Dave S

    Feb 21 San Diego, California USA

    Are you writing code in VBA? or in XOJO? the syntax is different, and if you are translating from one to the other you need to learn how to map functionality between the two......

    Good Luck... I'm out of this chat

  11. Dave,

    The code was wrote in Xojo. As you can see starting the thread, all code is exposed in Xojo, not VBA. Just I trying ot get the way to change the font color of the shape created from Xojo.

    Thanks you so much for your time. I will continue to try to solve this.

  12. Edited 5 weeks ago by Neil B

    try:

    so.TextFrame.Characters.Font.Color = 7432636

    This worked for me (from Xojo).

  13. Hi Neil,

    Really thanks ! The code works perfectly, but I want to clarify a little detail with the Xojo code. When I'm typing the code, Xojo shows after so.TextFrame.Characters.Font. the property .Color_ and not .Color

    When I tried to use .Color_ I got an error. Your solution is perfect.

  14. @Elvis A Xojo shows after so.TextFrame.Characters.Font. the property .Color_ and not .Color

    Not sure why that is. Converting VBA code to work in Xojo can be hit and miss.

or Sign Up to reply!