Excel X passing vars VBA/AS and AS/VBA

[Mark Anderson]

Hi,

I'm using Excel X and am having difficulty in finding simple clear
instructions on returning values from VBA to AS - or vice versa. I need
to iterate a range an use each cell value to write to another app.
Either I need a VBA routing to pass the cell value to an AS script and
receive a variable back from the script (as result of a call to another
app) as this must be written into a different column/same row of the
source XL sheet. Alternatively I can start in AS, call Excel, get the
VBA to pass out the cell value and then I return the result value.

Either way, I'm floundering for examples.

Normally with an AS script or handler, I'd pass in a variable with
global (or wider scope than the target script). Here I need the script
to expressly return a value to VBA. This exchange process will iterate
about 3,000-4,000 times when run.

Any pointers?

TIA

Mark

 

[Fredrik Wahlgren]

Hi,

I'm using Excel X and am having difficulty in finding simple clear
instructions on returning values from VBA to AS - or vice versa. I need
to iterate a range an use each cell value to write to another app.
Either I need a VBA routing to pass the cell value to an AS script and
receive a variable back from the script (as result of a call to another
app) as this must be written into a different column/same row of the
source XL sheet. Alternatively I can start in AS, call Excel, get the
VBA to pass out the cell value and then I return the result value.

Either way, I'm floundering for examples.

Normally with an AS script or handler, I'd pass in a variable with
global (or wider scope than the target script). Here I need the script
to expressly return a value to VBA. This exchange process will iterate
about 3,000-4,000 times when run.

Any pointers?

TIA

Mark

I think you can find the answer here:

http://word.mvps.org/MacWordNew/WordAppleScript.htm

Browse to the end where it says " Returning a Result from VBA to
AppleScript"

/ Fredrik

 

[Mark Anderson]

message
[snip]

I think you can find the answer here:

http://word.mvps.org/MacWordNew/WordAppleScript.htm

Browse to the end where it says " Returning a Result from VBA to
AppleScript"

/ Fredrik

Excellent. thanks. Just what I needed. Sad there's no VBA -> AS
direct way to pass variables but the above gives me alternates. Still
open to pointers to other pages/resources if they're out there.

Regards

Mark

 

[Paul Berkowitz]

Excellent. thanks. Just what I needed. Sad there's no VBA -> AS
direct way to pass variables but the above gives me alternates. Still
open to pointers to other pages/resources if they're out there.

Upgrade to 2004. Excel 2004 AppleScript does everything that a VBA
macro can, but returns results to AS. So convert your macro to AS 2004. Run
it from the system script menu - or as an applet - ad return values whenever
you want.

--
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.

 

[Mark Anderson]

Upgrade to 2004. Excel 2004 AppleScript does everything that a VBA
macro can, but returns results to AS. So convert your macro to AS 2004. Run
it from the system script menu - or as an applet - ad return values whenever
you want.

Thanks. Noted, but for now I'm stuck with Office X.

This is proof of concept work - if the client has Office 2004 I guess
I'll upgrade though I note the transition - in AS terms - is not
trivial. I'm X platform and so use office on the PC for Office-type
work. This issue is about migrating legacy record data from an XLS to a
database. The latter is x-platform and for other reasons if I do the
work, I'll probably do it all on Windoze though the potential client is
entirely Mac-based so I must consider a Mac workflow. None of which
invalidates your helpful info!

Mark