Applescript Array equivalent

Scripting Forums AppleScript | Mac OS X
,
#1

Hello,

I am a rank amateur and am making the transition from VBA for Excel to Applescript. I created a spreadsheet to figure out my payroll taxes in Excel 2004 and now must transition to AppleScript because Excel 2008 no longer uses VBA. So far I’ve made great progress, but I’m stuck on a simple array from the VBA version. Here is the module in VBA:

Function MonthlyAccrue(ThisMonth, SheetPosition)
Application.Volatile

Dim PayrollSheet As Object

For Each PayrollSheet In ActiveWorkbook.Sheets(Array("Payroll (1)", "Payroll (2)", "Payroll (3)", "Payroll (4)", _
    "Payroll (5)", "Payroll (6)", "Payroll (7)", "Payroll (8)", _
    "Payroll (9)", "Payroll (10)", "Payroll (11)", "Payroll (12)", _
    "Payroll (13)", "Payroll (14)", "Payroll (15)", "Payroll (16)", _
    "Payroll (17)", "Payroll (18)", "Payroll (19)", "Payroll (20)", _
    "Payroll (21)", "Payroll (22)", "Payroll (23)", "Payroll (24)"))
    If IsEmpty(PayrollSheet.Range("D42").Value) = False Then
        If Month(PayrollSheet.Range("D42").Value) = ThisMonth Then
        AccrueValue = AccrueValue + PayrollSheet.Range(SheetPosition)
        Else
            If Month(PayrollSheet.Range("D42").Value) > ThisMonth Then
            Exit For
            End If
        End If
    Else
    Exit For
    End If
Next
MonthlyAccrue = AccrueValue

End Function

What I am trying to figure out is how to make AppleScript search each of the 24 sheets of the workbook, test to see if the month of a particular sheet matches the month of the summary sheet, and if it does, add the value of a particular cell to an accrued value variable. Sorry to be posting such a pre-noobie question, but I’m not really a programmer…just a film music editor.

Thanks in advance,

Tom Carlson

#2

Hi Tom,

I haven’t tested it, but the AppleScript equivalent of the VBA macro
could look like this, in this case the array can be realized with the index variable


set thisMonth to month of (current date) as text
MonthlyAccrue(thisMonth, "A18")

on MonthlyAccrue(thisMonth, SheetPosition)
	set AccrueValue to ""
	tell application "Microsoft Excel"
		repeat with i from 1 to 24
			set oneSheet to ("Payroll (" & (i as text) & ")")
			if exists sheet oneSheet then
				tell sheet oneSheet
					if value of range "D42" is thisMonth then
						set AccrueValue to AccrueValue + (value of range SheetPosition)
					end if
				end tell
			end if
		end repeat
	end tell
	return AccrueValue
end MonthlyAccrue
#3

Thank you, Stefan. I will try that today. Is the “on MonthlyAccrue(thisMonth, SheetPosition)” statement the beginning of a subroutine? If so is that subroutine called above with “MonthlyAccrue(thisMonth, “A18”)”. Sorry for my ignorance about the basics. I’m just doing this because it’s more fun than going out and buying a pre-programmed accounting app.

Tom

#4

Yes and yes, I tried to make the script as close as the VBA original