View Full Version : [AS3] sending VBA values to Flash

11-28-2010, 10:42 AM

I am working on VBA application in Excel in which I embed Flash application (using Shockwave Flash Object). I would like to synchronize them - When user will fill in some text box in VBA, this value should be sent to Flash app and be assigned to flash variable.(Flash movie should be updated by VBA commands)
In AS2 it could be easy done using SetVariable command.
In AS3 it seems it doesnt work any more. I heard that ExternalInterface class could be helpful.
Did anyone have similiar problem or can advise me which way should I try?

Thanks a lot,

11-28-2010, 06:03 PM
ExternalInterface is what you want.

You have to register a callback function in Flash that can be called from VBA. Then you have to call that function using the ShockwaveFlash objects's CallFunction method from the VBA side.

You register the function you want to call like this..

function FlashFunction(o:Object)
var r:String = "";

// display the arguments we were passed

for (var v in o)
r += v + ":" + o[v] + "\r";

trace(r); // sent from VB

return "FlashFunction was called"; // this goes back to VB

if (ExternalInterface.available)
ExternalInterface.addCallback('FlashFunction', FlashFunction);

The call from the VBA side looks like this...

Dim x As String
Dim r As String

x = ""
x = x & "<invoke name=""FlashFunction"" returntype=""xml"">"
x = x & "<arguments>"
x = x & "<object>"
x = x & "<property id='arg1'><string>value1</string></property>"
x = x & "<property id='arg2'><string>value2</string></property>"
x = x & "</object>"
x = x & "</arguments>"
x = x & "</invoke>"

r = swocx.CallFunction(x)

11-28-2010, 07:02 PM
Hmmm it seems to be little harder now :-)

Northcode thanks already for a tip - but could you guide me a bit more in what you wrote as I dont understand all?

AS code:
the variable to which the data from VBA will be assigned is "r", and when the function will be correctly called, somewhere in VBA should appear "FlashFunction was called", right?

VBA code:
As I can see variables from VBA will be sent to flash as xml where invoke name "FlashFunction" is the first argument of ExternalInterface.addCallback('FlashFunction', FlashFunction);

I dont really get which values represents these two arguments arg1 and arg2 and where should they be in VBA
x = x & "<property id='arg1'><string>value1</string></property>"
x = x & "<property id='arg2'><string>value2</string></property>"

This VBA code should be assigned to some VBA button?

11-28-2010, 07:38 PM
Basically what you're doing is wrapping up a Flash function call in XML and passing it to Flash.

There are no "variables" as far as VBA is concerned, it's just a string that happens to contain XML and the XML only has meaning to Flash. You can put whatever you want in x, but only properly formmatted XML will be acceptable on the Flash side.

The arg1 and arg2 in the XML is just something I made up, you can pass as many variables (properties) as you like and they can be any type that ExternalInterface supports.

The r variable is the result of the CallFunction or whatever is returned from the FlashFunction in the AS code, in this case we're passing back "FlashFunction was called".

Are you doing this from Word or Excel?

11-28-2010, 08:08 PM
Hmmm.. I just tried a quick example in Word for you and I got an error when I try to do the CallFunction from VB into the FlashFunction. I've done this in VB before so I know it works so something odd is happening. I'll check it out and let you know what I find.

11-28-2010, 08:24 PM
Ok brighter to me now. Im doing this from Excel.

I got the same error in line: r = swocx.CallFunction(x)
"Method 'CallFunction' of object 'IShockwaveFLash' failed"

So swocx.CallFunction(x) makes that the "x" (which contains some string data formatted in XML) goes to FlashFunction in AS as an object and then is assigned to the r variable in flash? And the r in VBA as well gets a new value r = "FlashFunction was called"?

So if I will do a simple operation in VBA MsgBox r then I should have MsgBox "FlashFunction was called". So it works both sides.

11-28-2010, 08:30 PM
If I make the call from the Flash side out to VBA, that works and I can send data back to the Flash function. It just seems quite unhappy with making calls from VBA directly into Flash. I'll keep looking at this.

11-30-2010, 07:22 PM
Northcode, do you have any idea how to solve this?

I tried but nothing works. I couldn't find any detailed example on the net of ExternalInterface in VBA. With AS2 there is no problem, but I dont want to change my Flash app to AS2 and write in previous ActionScript.

There is one solution: Export excel to XML file and than load this XML file to swf. I tried this and it works somehow, but I need the swf file to be inside Excel (Shockwave Flash Object). Also I dont want to play with this big XML file exported from Excel, when you can reach swf directly from VBA.

Sorry for molesting you, but till now you're the only person I know that knows this stuff VBA-AS. :-)

11-30-2010, 10:48 PM
One thing you could do is make periodic calls from the embedded SWF out to VBA and return the values you need from the VBA function. It's basically a polling solution so it's not optimal, but it's functional and I can give you a working example.

I'm guessing VBA's support for COM isn't quite up to snuff (hard to believe) or there's something about CalFunction that I'm not aware of, which is more likely but not much more since I've done this from VB, JavaScript and C++ without any trouble.

12-01-2010, 12:59 PM
Even if it's polling solution, when the solution works without problems then it is good solution :). You've said that you have a working example. It would be really great if you could send it.
My email: konrad_debski[at]wp.pl

12-01-2010, 01:49 PM
Add a reference to ShockwaveFlash to your project and then put this code in a new UserForm in VBA.

Dim WithEvents swocx As ShockwaveFlash

Dim counter As Long

Public Sub swocx_FlashCall(ByVal request As String)

counter = counter + 1

' the ActionSCript call in Flash looks like this:
' var r:String = ExternalInterface.call("VBFunction", "pi", 3.1415927);

' the request we get is XML in a string that looks like this:
' <invoke name="VBFunction" returntype="xml"><arguments><string>pi</string><number>3.1415927</number></arguments></invoke>

' this is what we're sending back, our counter in XML format (that's why it's in a string)
swocx.SetReturnValue "<number>" & CStr(counter) & "</number>"
End Sub

Public Sub swocx_FSCommand(ByVal command As String, ByVal args As String)

MsgBox "command:" & command & vbCrLf & "arguments:" & args, vbInformation, "FSCommand"

End Sub

Private Sub UserForm_Initialize()

counter = 0

Set swocx = Controls.Add("ShockwaveFlash.ShockwaveFlash", "swocx")
swocx.Move 10, 10, 550, 400
swocx.Visible = True
swocx.Movie = "c:\test.swf"

End Sub

Create a new FLA in C:\ called test.fla, put this code in it, add a text field called ressult_txt and publish it to C:\test.swf (where the VBA code is expecting it).

import flash.external.ExternalInterface;

var myTimer:Timer = new Timer(500);
myTimer.addEventListener(TimerEvent.TIMER, timerListener)

function timerListener(event:TimerEvent):void
var r:String = ExternalInterface.call("VBFunction", "pi", 3.1415927);
if (r != null)
result_txt.text = r;

Then start your VBA project and you'll see Flash displaying the value of the VBA counter updated every 500ms.

That's it.

12-06-2010, 08:39 PM
At first it didnt work for me, as I realised something was wrong with Flash - It started to export swf files without any actionscript code inside... Strange things but I reinstalled it and tried on more time - now it works perfectly!

I can insert some data in VBA textbox and these data can be transported to Flash easily. It works as I wanted to work :-). Now I need to think which way to go, this one or with exporting XML file from excel and loading it to flash.

Anyway, thanks a lot Northcode, you solved my problem :).