VBA Excel Compatibility

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.

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:

[code] 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
[/code]

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

Happy to help :slight_smile:

Eugene

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

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

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.

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

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 …

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

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 …

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

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.

try:

so.TextFrame.Characters.Font.Color = 7432636

This worked for me (from Xojo).

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.

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