CODEWITHSUNDEEP
vbaexcelpowerpoint
Brian
Brian

Reputation: 2108

Variable Not Defined Error Enigma

I am trying to automate PowerPoint from Excel. Everything worked fine. I have the following code block that worked perfectly:

Set MyTextbox = mySlide.Shapes.AddTextbox(1, Left:=myShape.Left - 3, Top:=42, Width:=myShape.Width + 6, Height:=10).TextFrame.TextRange
With MyTextbox
    .Text = Format(MyShapeDate, "m/yy")
    .Font.Size = 11
    .ParagraphFormat.Alignment = ppAlignCenter
    .Font.Bold = True
End With

I saved the code as an addin and tried to add a new tab and a button, unsuccessfully. So I brought the code back to the workbook. When I tried to run it again I get a variable not defined for ppAlignCenter. Would anyone know why this is happening or if it is a common error?

Upvotes: 2

Views: 403

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71227

It is a PowerPoint constant and I am using late binding. But it worked perfectly numerous times.

Then you were NOT late-binding.

Put yourself in VBA's shoes: you're compiling that project, and you come across ppAlignCenter as a token/identifier. If Option Explicit is turned on (it should!), and the PowerPoint object library isn't referenced, then ppAlignCenter is an undeclared variable, and you don't know what to do with it - so you throw your hands in the air and scream at the user:

Compile error: variable not defined.

If Option Explicit is not turned on, and PowerPoint isn't referenced, then you "declare" an uninitialized Variant variable on-the-spot, named ppAlignCenter, so that the assignment of MyTextBox.ParagraphFormat.Alignment can be compiled.

Because the variable isn't initialized, at runtime its value will just be 0 - and from there it's not clear what happens, because PpParagraphAlignment has no member with a value of 0, so it's possible that the MyTextBox.ParagraphFormat.Alignment assignment raises a runtime error as well, but that's entirely up to the Alignment property's implementation.

Now, if the PowerPoint library is referenced (early-bound), then ppAlignCenter resolves to that PpParagraphAlignment.ppAlignCenter enum value which you know is really just a 2, and so you can proceed to compile.

The PpParagraphAlignment enum is defined in the PowerPoint type library, so if you are late-binding to that library, you can't refer to its members by name, because VBA will try to resolve them at compile-time - and fail to do so.

So instead of this:

.ParagraphFormat.Alignment = ppAlignCenter

You need to do this:

.ParagraphFormat.Alignment = 2 'ppAlignCenter

But why would you late-bind to a type library you know you need to have anyway for the program to even hope to work at runtime?

Late-binding means you're working with Object variables instead of specific types - and that means runtime interface lookups and quite a bit of overhead that you don't need at all... and these lookups are going to fail anyway if the library isn't present. Not to mention, programming against Object means you don't get IntelliSense and need to quadruple-check everything you're doing, because the compiler isn't going to help you and the runtime will scream if you make a typo.

...that said, early-bound references are version-specific - so if you're distributing your code to users running different versions of the libraries you're referencing, late-binding will ensure everything works for everyone (provided your code isn't using API features that are version-specific). In that case the best compromise is to develop with early-bound references (so you get IntelliSense and autocompletion!), and then switch everything over to late-bound when you distribute it.

Upvotes: 8

Related Questions