Excel VBA Macro in AppleScript

Scripting Forums AppleScript | Mac OS X
#1

With the strong possibility of Microsoft dropping VBA Macros in their upcoming Office 2008 for the Mac (rumors go both ways), I have decided to convert all my VBA Macros to AppleScript. With this in mind:

  1. in my instance, I have hundreds of Cells in my Excel Worksheet, many of which have different Macro calls; e.g.,
    =IF(NOT(ISBLANK(Billed_by_Provider)), ‘Personal Macro Workbook’!TotalOwedBill(Billed_by_Provider,Accept_Prime_Assignment,Allowed_by_Prime), “”)

and the VBA Macro function looks like:

Function TotalOwedBill(Bill As Range, AcceptPrime As Range, PrimeAllowed As Range)

Dim CallerRow As Long
Dim cThisBill As Range                 ' contains Currency
Dim cAcceptPrime As Range        ' contains Integer
Dim cPrimeAllowed As Range       ' contains Currency

CallerRow = Application.Caller.Row

' Set stores the actual Cell/Range in the variable.
' Without Set, the  value of its contents is stored.

Set cThisBill = Cells(CallerRow, Bill.Column)
Set cAcceptPrime = Cells(CallerRow, AcceptPrime.Column)
Set cPrimeAllowed = Cells(CallerRow, PrimeAllowed.Column)

' ArgumentsBlank looks at the contents of the passed Cell/Range via
'     arg = argList(i)
' which does not use Set

If (ArgumentsBlank(cThisBill, cAcceptPrime, cPrimeAllowed)) Then
    TotalOwedBill = xError
ElseIf (cPrimeAllowed.Value < 0) Then
    TotalOwedBill = 0
ElseIf (cAcceptPrime.Value = gAcceptPrime) Then
    TotalOwedBill = cPrimeAllowed.Value
ElseIf (cAcceptPrime.Value = gNotAcceptPrimeFees) Then
    TotalOwedBill = WorksheetFunction.Min(cThisBill.Value, cPrimeAllowed.Value * 1.15)
ElseIf (cAcceptPrime.Value = gNotAcceptPrimeAtAll) Then
    TotalOwedBill = cThisBill.Value
Else
    TotalOwedBill = cThisBill.Value
End If

’ TotalOwedBill = cThisBill.Value ’ initially used for testing

End Function

So far, the corresponding AppleScript code looks like:


on TotalOwedBill(Bill, AcceptPrime, PrimeAllowed)
	
	-- Dim CallerRow As Long
	-- Dim cThisBill As Range        ' contains Currency
	-- Dim cAcceptPrime As Range     ' contains Integer
	-- Dim cPrimeAllowed As Range    ' contains Currency
	
	(*
        haven't solved the transfer of these yet
	set CallerRow to Application.Caller.Row
       
	set cThisBill to Cells(CallerRow, Bill.Column)
	set cAcceptPrime to Cells(CallerRow, AcceptPrime.Column)
	set cPrimeAllowed to Cells(CallerRow, PrimeAllowed.Column)
	*)
		
	if (my ArgumentsBlank(cThisBill, cAcceptPrime, cPrimeAllowed)) then
		return xError
	else if (cPrimeAllowed < 0) then
		return 0
	else if (cAcceptPrime = gAcceptPrime) then
		return cPrimeAllowed
	else if (cAcceptPrime = gNotAcceptPrimeFees) then
		return Min(cThisBill, cPrimeAllowed * 1.15)
	else if (cAcceptPrime = gNotAcceptPrimeAtAll) then
		return cThisBill
	else
		return cThisBill
	end if
	
end TotalOwedBill

So, here are the questions:

  1. what is the AS equivalent of Application.Caller.Row

  2. how about the calls to parm.Column

  3. Finally, and most important, how do I attach the AppleScript file to my Excel Worksheet such that the individual Cells that call specific on functions within that AppleScript file know where to look for these functions.

Thanks bunches for taking the time to read this SOS.

John Love

#2

P.S.

Does anyone know if there is a hard copy of “Excel2004AppleScriptRef.pdf”. This thing is about 350 pages long. Thanks in advance.

#3

Hi John,

To start I don’t script any of the Office products much, so some of my answers may be a tad off. That said I would recommend you check out the MacTech: VBA to AppleScript Transition Guide.

As for your questions I think one unfortunate statement will answer them all.

There is no way to run Auto Macros (or internal Macros). That said AppleScripts will be launched either from the OS Scripts Menu or Saved and ran as application. So you will not be able to refer to a cell as the script caller since the cell cant call the script in the first place. So cells will have to be hard coded in or determined through some other method.

Hope that helps.

@Adam - I justiced your name as a guide reviewer =)

#4

You may find some useful information here.

#5

Thanks, guys for your prompt reply. Unfortunately, the reply clearly states that individual Cells cannot call specific AppleScript functions … so, in my case, conversion from VBA to AppleScript is useless.

So, when Office 08 comes along, Office 04 will remain on my drive, operating in Rosetta.