Friday, November 1st, 2013

Convert Hex to VBA colors

Unbelievable! Hex colors don’t work in VBA without conversion!

It is totally unbelievable to me but today I wanted to put an ActiveX option button on an Excel worksheet. I could set the BackStyle to transparent so I can see the color in the worksheet through it but every time I clicked the option button it showed up white – and UGLY.

I managed to get the hex color of the worksheet – I got the RGB from Excel then put it in PaintShop Pro to get the hex value. So R242, G157, B 0 became #F29D00 hex.

At this point I was feeling rightly proud of my color conversion skills until I tried to set the  Backcolor property to this value. It WAS BLUE!? not orange… WTF?

Seems like VBA color isn’t the same as Hex colors … it is nearly the same but not quite. You need to switch the R and B values with each other. So my orange #F29D00 becomes #009DF2 for VBA – of course you need to then wrap it in the other little bits so it looks like this: &H009DF2& and all is hunky dory. Paste that into the Properties box and VBA makes it into a nice long integer all by itself – that bit at least is simple.

So now you know how to convert hex colors to VBA colors.

I just have to ask the burning question – WHY? Why isn’t good ol’ RGB good enough for Microsoft – why is it BGR?  And just which programming genius dreamed that one up?

 

Helen Bradley

Labels: , , , , ,

Categories:office, VBA

posted by Helen Bradley @ 9:42 am2 Comments links to this post

Friday, July 26th, 2013

PowerPoint VBA – adding shapes to slides programatically

I’ve been working on a project which involves adding shapes to a PowerPoint slide using VBA.

One big big problem with PowerPoint is that there is no longer any macro recorder. This means you can’t get information about methods and properties by recording the steps you perform to, for example, add a shape to a slide. In other programs you can get a lot of  useful information from recorded macros – in PowerPoint – nada!

So, if you, like me are struggling to make sense of a language that uses such wonderfully nonsensical properties like TextFrame.TextRange then here is the benefit of my research.

Here, in no particular order, is a grab bag of MSDN articles and references for adding and formatting (and adding text to) shapes in PowerPoint using VBA. Enjoy! and if you have any additional useful resources, please add them to the comments to keep us all from going crazy!

Constants to use with the SchemeColor property

http://msdn.microsoft.com/en-us/library/office/bb230611(v=office.12).aspx

ColorFormat object

Properties you can use to color an object – eg its fill, font, outline and so on:
http://msdn.microsoft.com/en-us/library/office/bb265494(v=office.12).aspx

TextRange.font property

How to configure a font for a shape:
http://msdn.microsoft.com/en-us/library/office/ff744240.aspx

LineFormat object

How to configure the line and arrowhead for a line or shape with a border
http://msdn.microsoft.com/en-us/library/office/aa220968(v=office.11).aspx

Shapes Object

How to create a shape on a PowerPoint slide
http://msdn.microsoft.com/en-us/library/office/ff746621.aspx

Shape.height property

Measured in (Oh so helpful) points (72 to the inch)
http://msdn.microsoft.com/en-us/library/office/ff744642.aspx

Shapes members

Some things you can add to a slide:
http://msdn.microsoft.com/en-us/library/office/ff745286.aspx

Shapes.AddShape method

How to add a shape to a PowerPoint slide:
http://msdn.microsoft.com/en-us/library/office/ff744336.aspx

An explanation of working with text in a shape

aka Microsoft’s attempt to explain why you need to use TextFrame.TextRange to add text to a shape!:
http://msdn.microsoft.com/en-us/library/office/aa198526(v=office.10).aspx

The MsoAutoShapeType Enumeration

ie how you can find what a shape is called so you can add it to a slide:
http://msdn.microsoft.com/en-us/library/aa432469(v=office.12).aspx

ParagraphFormat.Alignment property

How to align text in a shape in PowerPoint – :
http://msdn.microsoft.com/en-us/library/office/ff744029.aspx

Info on the TextFrame.TextRange property in PowerPoint:

http://msdn.microsoft.com/en-us/library/office/ff744793.aspx

Info about the TextFrame members in PowerPoint:

http://msdn.microsoft.com/en-us/library/office/ff745830.aspx

Helen Bradley

Labels: , , , , , ,

Categories:office

posted by Helen Bradley @ 4:41 pmNo Comments links to this post

Monday, August 2nd, 2010

Line feed in VBA textbox

When you need to create a line feed (new line) in a VBA text box you can do so in the string that you’re using to assemble your message using any one of a  number of methods.

You can use Chr(13) which is an old style character conversion of ASCII character 13 which is the carriage return and line feed character. Or you can use vbNewLine or even vbCrLf.

But, try as you might, all you will get in your textbox is a silly paragraph marker and not a new line if you don’t set your textbox up as a multiline text box in its Properties.  Don’t say I didn’t warn you!

Helen Bradley

Labels: , , ,

Categories:Uncategorized

posted by Helen Bradley @ 1:35 pm6 Comments links to this post

Tuesday, February 2nd, 2010

Excel VBA: What Sheet is that?

When you’re working in Microsoft Excel Visual Basic for Applications, you may need to refer to a worksheet by name. This can be confusing because the sheet names are not necessarily what are displayed in the sheet tabs at the bottom of the screen.

There is only one way to know exactly what a sheet’s name is so you can refer to it by that name regardless of what the tab says. That is to view the Visual Basic Editor and select your project in the Project Explorer. Here you will see each sheet listed by name with the sheet tab name in brackets after it.

If you want to refer to a sheet by name in your VBA code use the sheet name at the left (not the one in brackets) in the Project Explorer.

This will ensure that you always use the exact sheet that you expect to be using in your Visual Basic application.

For more tips and tricks and helpful Office columns, visit Projectwoman.com

Helen Bradley

Labels: , ,

Categories:Uncategorized

posted by Helen Bradley @ 10:22 pm1 Comment links to this post

Wednesday, July 29th, 2009

Excel macro – Format By Contents

You can do so much with Excel macros – they can be so powerful.

Here is a macro that formats a cell depending on its contents when you type something in it.

If you type a number, or a formula that returns a number, it is formatted one way, if you type a date it is formatted another way and if you type a word it is formatted a different way.

The macro uses the OnEntry event which fires whenever something is entered into a cell. If you attach the macro to an Auto_Open macro you’ll ensure it is run whenever the workbook is opened.

To create the macro, choose Tools > Macro > Visual Basic Editor and, choose Insert > Module to add a module to the current worksheet. Type the code into the dialog.

Sub Auto_Open()
ActiveSheet.OnEntry = “formatCell”
End Sub

Sub formatCell()
If IsNumeric(ActiveCell) Then
ActiveCell.Font.Name = “Verdana”
ActiveCell.Font.Size = 12
ActiveCell.Font.ColorIndex = 46
ElseIf IsDate(ActiveCell) Then
ActiveCell.Font.Name = “Verdana”
ActiveCell.Font.Size = 10
ActiveCell.Font.ColorIndex = 50
Else
ActiveCell.Font.Name = “Times New Roman”
ActiveCell.Font.Size = 12
ActiveCell.Font.ColorIndex = 5
End If
End Sub

Sub Auto_Close()
ActiveSheet.OnEntry = “”
End Sub

Back in Excel choose Tools > Macro > Auto_Open to run the macro the first time to test it. Provided you have Excel configured to run macros, it will run automatically every time you open the workbook in future.

To learn more about Auto_open, AutoOpen and other fun macro naming conventions in VBA, visit this blog post:

What’s in a name? Auto_Open or AutoOpen What’s in a name? Auto_Open or AutoOpen
http://www.projectwoman.com/labels/Auto_Open.html

For more tips and tricks and helpful Office columns, visit Projectwoman.com

Helen Bradley

Labels: , , , , ,

Categories:Uncategorized

posted by Helen Bradley @ 7:12 pmNo Comments links to this post

Friday, March 21st, 2008

What’s in a name? Auto_Open or AutoOpen

Sometimes you wonder if the folks up at Redmond are laughing at us behind our backs. Really, do they deliberately set out to confuse us or are they just that plain disorganised?

Today my quandary involves Auto_Open and AutoOpen. These are two special macro names. The first, Auto_Open is Excel’s special named macro that runs automatically when the workbook containing it is opened. AutoOpen is the Word equivalent. It makes no sense that one has an underscore and the other doesn’t – it just makes life for us VBA folk a little more confusing than it should be.

The other macros Auto_Close and AutoClose work the same way, Auto_Close is the Excel macro name – call a macro by this name and save it in your workbook and it will run whenever you close the workbook. In Word, the name is AutoClose.

To add to the confusion, PowerPoint doesn’t support either of the naming conventions, in fact, you can’t create auto running macros in PowerPoint the same way you do in Word and Excel. The workaround is cumbersome, you need to create a PowerPoint add-in that includes the Auto_Open subroutine. Load the Add-in and PowerPoint will run the code in Auto_Open it loads and ditto for subroutine called Auto_close – it runs when the add-in is unloaded – which happens automatically when you exit PowerPoint. Learn more about how to do this in this KnowledgeBase article.

Thanks Redmond, we are now officially confused!

For more tips and tricks and helpful Office columns, visit Projectwoman.com

Helen Bradley

Labels: , , , , , , ,

Categories:Uncategorized

posted by Helen Bradley @ 3:59 amNo Comments links to this post