Are VBA functions available to AppleScript once turned into an add-in?

[ecrichlow]

Okay,

So I've written this wonderful set of VBA code in PowerPoint that
does exactly what I need it to do. It runs a toolbar that I added and I
use a supplemental Cocoa application to call functions within the
PowerPoint VBA code (using Applescript) to perform some needed
functionality.

Everything works great.

So, final step, save the presentation with the VBA code as a
PowerPoint Add-In, and install it.

Some things stopped working.

Namely, places where I call VBA functions using Applescript: do
Visual Basic ("Call function"), don't work anymore. Applescript tells
me that "Call function" doesn't understand the "do Visual Basic"
message.

My best guess is that once I turn the presentation into an add-in,
it's no longer VBA code, and thus the "do Visual Basic" command in
Applescript is no longer applicable. Perhaps that or, the VBA code is
specific to the presentation it was authored in, but the add-in is
generic to the Application, and thus can apply to any presentation, and
perhaps "do Visual Basic" implicitly references a specific
presentation, so the add-in code is out of its range.

Either way, I need a way to call the functions I wrote from
Applescript (or by any other possible means) through a secondary
application.

Is it even possible?

....Thanx...
....Eric...

 

[Paul Berkowitz]

I don't quite understand what you're trying to do. How are you making it an
Add-In? If it is a VBA macro, are you doing the AppleScript parts as
MacScript commands?

If it's not a VBA macro, what is it? An AppleScript applet (application),
that launches on startup? PPT won't run a .scpt script document, so I
suppose it must be. Or is it your Cocoa app? You say you've got a Cocoa
application somewhere - is this it? Are you coding NSAppleScript within
Objective C? As text, or as a path to an applet or script file?

Are you sure you're using the correct quote escaping? What do mean by "Call
function" anyway? Do you mean you're calling another Sub? If that's what
this is about, I think you need to read

<http://www.word.mvps.org/MacWordNew/WordAppleScript.htm>

(In safari, refresh the page a few times.) Read the "Multiple Subs and
Functions": I think that's the problem you're hitting.


BTW, it seems awfully complex. Do you have Office 2004? Are you aware that
you could do everything in native AppleScript now, without do Visual Basic?
Or vice versa, could you really not do everything from a macro, with a few
MacScript commands where needed?

--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <http://macscripter.net/scriptbuilders/>

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.

 

[ecrichlow]

Okay,

I'm somewhat conflicted as to how much detail to give, to give you
the big picture versus clouding the issue with unnecessary detail.

Here's the brief big picture.

I'm working on a teaching tool, an authoring system that integrates
with PowerPoint. You can have a presentation done in PP, and on certain
slides you want to add a question. When you present the presentation,
and advance to one of those slides with a question attached, another
application launches its window over PP and allows participants
(students) to register their answers to the question via a remote based
voting system.

Now, the implementation.

Through PP's built-in VBA functionality, I create a custom toolbar
with buttons that allow the user to author, edit and delete questions.
(A question attached to a slide is represented by a graphic that I
place on the slide, and has some custom data associated with it.)

Now, when the user launches a slideshow with some of these
questions embedded, there needs to be a 2-way channel of communication
between the app that handles the voting on the questions and
PowerPoint.

PP doesn't have inter-process communication built in, so on that
end I just have the VBA code write certain files out as its means of
communicating to the other app.

When the other app needs to communicate with PowerPoint, it
generates an AppleScript message (it's a Cocoa app). The AppleScript
message uses the "do Visual Basic" that PowerPoint supports to call a
Sub or Function in the VBA code.

Now, if I manually run this VBA code from PowerPoint's VBA Editor,
everything works fine. The Cocoa app can run the VBA functions in
PowerPoint.

Here's the problem.

We don't want the user to have to use our presentation with the VBA
code as some kind of template, and have to manually run the VBA every
time they want to use the functionality.

So, I create a PowerPoint Add-in from the presentation. I can then
install that Add-in, and it runs every time I start PowerPoint. My
custom toolbar gets created, and I can author questions and add them to
the presentation.

The problem is that, once it's an Add-in, I lose the ability to
call the VBA Subs and Functions from AppleScript.

When my code is run manually from the Visual Basic Editor, I can go
to Script Editor and say:

tell application "Microsoft PowerPoint"
activate
do Visual basic "Call Function"
end tell

But when my code is running as an Add-in, that script won't work.
It tells me that "Call Function" doesn't understand the "do Visual
Basic" message.

So my question is whether or not it's possible to call VBA
functions in an Add-in? Is it just that the syntax to do so changes
from when I run it manually, or is it impossible?

....Eric...

 

[Paul Berkowitz]

So, I create a PowerPoint Add-in from the presentation. I can then
install that Add-in, and it runs every time I start PowerPoint. My
custom toolbar gets created, and I can author questions and add them to
the presentation.

The problem is that, once it's an Add-in, I lose the ability to
call the VBA Subs and Functions from AppleScript.

When my code is run manually from the Visual Basic Editor, I can go
to Script Editor and say:

tell application "Microsoft PowerPoint"
activate
do Visual basic "Call Function"
end tell

But when my code is running as an Add-in, that script won't work.
It tells me that "Call Function" doesn't understand the "do Visual
Basic" message.

So my question is whether or not it's possible to call VBA
functions in an Add-in? Is it just that the syntax to do so changes
from when I run it manually, or is it impossible?

You need to tell the script which macro the Subs and Functions are in. It
doesn't look like you read that article I pointed you to. The same applies
to PPT as to Word, suitably adapted. Read the "Calling Installed Macros"
section of the article. (You'll need to Refresh the page a few times in
Safari to see it.) You'll need to use this format:


   tell application "Microsoft PowerPoint"

        --do Visual Basic "Presentations.Open \"Macintosh Hard
Disk:Applications:Microsoft Office X:StartupowerPoint:My
Presentation.ppt\""

         do Visual Basic "Application.Run \"Module1.MyFunction\""

   end tell


You shouldn't really need the Presentations.Open line since the Add-In is
open already (whether you have it in Office:Startup as assumed above, or in
Add-Ins folder). But you do need the Application.Run and Module1. locator
when running a macro from AppleScript. Note the comment later in the
article:


€ Any of the variations of VBA's Run Method, as outlined in the
Visual Basic Help, should be available with Œdo Visual Basic¹, but note that
the third version given in the VB Help:

            Application.Run "'My Document.doc'!ThisModule.ThisProcedure"

doesn't seem to work in Word Mac VBA, at least not Word X, and so it doesn't
work with do Visual Basic either.
'

I haven't tested this in PowerPoint, so I'm not sure whether it's true there
too. It would be best if you could include the Presentation name followed by
the exclamation point, in case there are any other presentations open.
Here's what the VB Help says about the Run Method in PowerPoint:

------------------

Run Method

Application object: Runs a Visual Basic procedure.
SlideShowSettings object: Runs a slide show of the specified presentation.
Returns a SlideShowWindow object.

Syntax 1
expression.Run(MacroName, safeArrayOfParams)

Syntax 2
expression.Run

expression Required. An expression that returns an Application object
(Syntax 1) or a SlideShowSettings object (Syntax 2).

MacroName Required String. The name of the procedure to be run. The string
can contain the following: a loaded presentation or add-in file name
followed by an exclamation point (!), a valid module name followed by a
period (.), and the procedure name. For example, the following is a valid
MacroName value: "MyPres.ppt!Module1.Test."
safeArrayOfParams Required Variant. The argument to be passed to the
procedure. You cannot specify an object for this argument, and you cannot
use named arguments with this method. Arguments must be passed by position.

Remarks
To run a custom slide show, set the RangeType property to
ppShowNamedSlideShow, and set the SlideShowName property to the name of the
custom show you want to run.

--------------


You'll have to try out a few ways. If there's never going to be more than
this one loaded add-in open, then I'm pretty sure you can manage without the
full

do Visual Basic "Application.Run \"'My Presentation
Add-In.ppt'!Module1.MyFunction\""

if there are problems with that, and


do Visual Basic "Application.Run \"Module1.MyFunction\""


should do the trick. But the shortcut you can use in the VB Editor omitting
the "Application.Run \"Module1. " parts of teh command won't work from
AppleScript.


--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <http://macscripter.net/scriptbuilders/>

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.

 

[ecrichlow]

Thanks for the detailed response.

I briefly scanned the article you pointed me to, but didn't think I
saw anything that mirrored my situation. I'll read it in more depth.

In the meantime, I seem to have found a usable workaround.

I added invisible buttons to the toolbar I created, and used the
"Execute" command in Applescript to "press" those buttons, and set the
on click action of the buttons to the names of the subs/functions I had
been trying to call using "do Visual Basic", thus executing the desired
sub or function. The only downside is the inability to pass arguments.
....Eric...