Script Hanging

Tom_Carlson · April 13, 2008, 8:52pm

Hello All,
First I apologize in advance for posting such a lengthy script here, but I’m having trouble figuring out why it’s hanging. You may skip this long first script and read down to the second script, which is just a small block. Here is the script as originally written, which works as it is supposed to:

global PayrollSheetNum
set PayrollSheetNum to 1
set MonthList to {January, February, March, April, May, June, July, August, September, October, November, December}

repeat with i from 1 to 12
	set AccrueMonth to item i of MonthList
	MonthlyZero(AccrueMonth)
end repeat

repeat with i from 1 to 12
	set AccrueMonth to item i of MonthList
	MonthlyAccrue(AccrueMonth)
end repeat

on MonthlyZero(AccrueMonth)
	tell application "Microsoft Excel"
		activate
		set AccrueSheet to ("" & (AccrueMonth as text) & "")
		set formula of range "C1, B14, B16, B18, B27, B29, B31, B35, B37" of sheet AccrueSheet to 0
	end tell
end MonthlyZero

on MonthlyAccrue(AccrueMonth)
	set AccrueWeeks to ""
	set AccrueBase to ""
	set AccrueVacation to ""
	set AccrueHoliday to ""
	set AccrueFedTax to ""
	set AccrueFICA to ""
	set AccrueMedicare to ""
	set AccrueStateTax to ""
	set AccrueSDI to ""
	tell application "Microsoft Excel"
		activate
		set AccrueSheet to ("" & (AccrueMonth as text) & "")
		repeat with i from PayrollSheetNum to 24
			set PayrollSheet to ("Payroll (" & (i as text) & ")")
			tell sheet PayrollSheet
				if (get value of range "D42") is not "" then
					set SheetMonth to month of (get value of range "D42")
					if SheetMonth is equal to AccrueMonth then
						set AccrueWeeks to AccrueWeeks + (get value of range "C1")
						set AccrueBase to AccrueBase + (get value of range "B14")
						set AccrueVacation to AccrueVacation + (get value of range "B16")
						set AccrueHoliday to AccrueHoliday + (get value of range "B18")
						set AccrueFedTax to AccrueFedTax + (get value of range "B28")
						set AccrueFICA to AccrueFICA + (get value of range "B30")
						set AccrueMedicare to AccrueMedicare + (get value of range "B32")
						set AccrueStateTax to AccrueStateTax + (get value of range "B34")
						set AccrueSDI to AccrueSDI + (get value of range "B36")
						tell sheet AccrueSheet
							set formula of range "C1" of sheet AccrueSheet to AccrueWeeks
							set formula of range "B14" of sheet AccrueSheet to AccrueBase
							set formula of range "B16" of sheet AccrueSheet to AccrueVacation
							set formula of range "B18" of sheet AccrueSheet to AccrueHoliday
							set formula of range "B27" of sheet AccrueSheet to AccrueFedTax
							set formula of range "B29" of sheet AccrueSheet to AccrueFICA * 2
							set formula of range "B31" of sheet AccrueSheet to AccrueMedicare * 2
							set formula of range "B35" of sheet AccrueSheet to AccrueStateTax
							set formula of range "B37" of sheet AccrueSheet to AccrueSDI
						end tell
					else
						if SheetMonth is greater than AccrueMonth then
							exit repeat
						end if
					end if
				else
					exit repeat
				end if
			end tell
		end repeat
		set PayrollSheetNum to i
	end tell
end MonthlyAccrue

Now, I am trying to cause the script to exit the repeat if it encounters an empty date cell (“D42”) in the payroll sheet during the repeat that calls MonthlyAccrue(AccrueMonth). Here is the block that changes in the new script:

tell application "Microsoft Excel"
	activate
	repeat with i from 1 to 12
		set AccrueMonth to item i of MonthList
		set PayrollSheet to ("Payroll (" & (PayrollSheetNum as text) & ")")
		if (get value of range "D42") is not "" then
			run MonthlyAccrue(AccrueMonth)
		else
			exit repeat
		end if
	end repeat
end tell

Everything else in the script is identical. When I run the new script to test it, I now get the message “Microsoft Excel got an error: Can’t continue MonthlyAccrue.” One big difference is that I added a “tell application “Microsoft Exel” activate” statement. Does this mean that the call to the MonthlyAccrue subroutine is somehow being interpreted as a command to Excel instead? Do I need to end the “Microsoft Excel” tell earlier? I’m stumped.

Thanks in advance for any help. Sorry again for the lengthy script.

Tom Carlson

StefanK · April 13, 2008, 9:06pm

Hi,

the command run starts a script, it is not used to call a handler.
A handler is called without any keyword, but there is one exception:
Is the handler call within an application tell block, the handler must be called with

my handler()

because handlers belong to AppleScript itself

Tom_Carlson · April 13, 2008, 9:37pm

Thank you Stefan. That clarifies it and it works! I find that I need to select the first sheet for the script to work, because Excel needs to be told explicitly what the sheet is that needs to be looked at. This works if I use “select”, but the problem is that then I watch the script selecting various sheets as it performs it’s calculations. I tried “activate” in the script as seen below but got the message “Can’t get get value of range “D42”. Access not allowed.” Here is the script that doesn’t work:

tell application "Microsoft Excel"
	activate
	repeat with i from 1 to 12
		set AccrueMonth to item i of MonthList
		set PayrollSheet to ("Payroll (" & (PayrollSheetNum as text) & ")")
		activate sheet PayrollSheet
		if (get value of range "D42") of active sheet is not "" then
			my MonthlyAccrue(AccrueMonth)
		else
			exit repeat
		end if
	end repeat
end tell

However, if I run the script as:

tell application "Microsoft Excel"
	activate
	repeat with i from 1 to 12
		set AccrueMonth to item i of MonthList
		set PayrollSheet to ("Payroll (" & (PayrollSheetNum as text) & ")")
		select sheet PayrollSheet
		if (get value of range "D42") is not "" then
			my MonthlyAccrue(AccrueMonth)
		else
			exit repeat
		end if
	end repeat
end tell

it works, although I have to watch the process as it does it’s selections.

Any thoughts?

Tom

StefanK · April 13, 2008, 9:43pm

activate affects only applications, nothing else.
It brings the targeted application to the front.

To “select” a specific sheet, reference it!


tell application "Microsoft Excel"
	-- activate
	repeat with i from 1 to 12
		set AccrueMonth to item i of MonthList
		set PayrollSheet to ("Payroll (" & (PayrollSheetNum as text) & ")")
		if (get value of range "D42" of sheet PayrollSheet) is not "" then
			my MonthlyAccrue(AccrueMonth)
		else
			exit repeat
		end if
	end repeat
end tell

Tom_Carlson · April 13, 2008, 10:10pm

Thank you so much for all of your help, Stefan. The script is now complete. Here it is:

global PayrollSheetNum
set PayrollSheetNum to 1
set MonthList to {January, February, March, April, May, June, July, August, September, October, November, December}
repeat with i from 1 to 12
	set AccrueMonth to item i of MonthList
	MonthlyZero(AccrueMonth)
end repeat

tell application "Microsoft Excel"
	activate
	repeat with i from 1 to 12
		set AccrueMonth to item i of MonthList
		set PayrollSheet to ("Payroll (" & (PayrollSheetNum as text) & ")")
		if (get value of range "D42" of sheet PayrollSheet) is not "" then
			my MonthlyAccrue(AccrueMonth)
		else
			exit repeat
		end if
	end repeat
end tell

on MonthlyZero(AccrueMonth)
	tell application "Microsoft Excel"
		activate
		set AccrueSheet to ("" & (AccrueMonth as text) & "")
		set formula of range "C1, B14, B16, B18, B27, B29, B31, B35, B37" of sheet AccrueSheet to 0
	end tell
end MonthlyZero

on MonthlyAccrue(AccrueMonth)
	set AccrueWeeks to ""
	set AccrueBase to ""
	set AccrueVacation to ""
	set AccrueHoliday to ""
	set AccrueFedTax to ""
	set AccrueFICA to ""
	set AccrueMedicare to ""
	set AccrueStateTax to ""
	set AccrueSDI to ""
	tell application "Microsoft Excel"
		activate
		set AccrueSheet to ("" & (AccrueMonth as text) & "")
		repeat with i from PayrollSheetNum to 24
			set PayrollSheet to ("Payroll (" & (i as text) & ")")
			tell sheet PayrollSheet
				if (get value of range "D42") is not "" then
					set SheetMonth to month of (get value of range "D42")
					if SheetMonth is equal to AccrueMonth then
						set AccrueWeeks to AccrueWeeks + (get value of range "C1")
						set AccrueBase to AccrueBase + (get value of range "B14")
						set AccrueVacation to AccrueVacation + (get value of range "B16")
						set AccrueHoliday to AccrueHoliday + (get value of range "B18")
						set AccrueFedTax to AccrueFedTax + (get value of range "B28")
						set AccrueFICA to AccrueFICA + (get value of range "B30")
						set AccrueMedicare to AccrueMedicare + (get value of range "B32")
						set AccrueStateTax to AccrueStateTax + (get value of range "B34")
						set AccrueSDI to AccrueSDI + (get value of range "B36")
						tell sheet AccrueSheet
							set formula of range "C1" of sheet AccrueSheet to AccrueWeeks
							set formula of range "B14" of sheet AccrueSheet to AccrueBase
							set formula of range "B16" of sheet AccrueSheet to AccrueVacation
							set formula of range "B18" of sheet AccrueSheet to AccrueHoliday
							set formula of range "B27" of sheet AccrueSheet to AccrueFedTax
							set formula of range "B29" of sheet AccrueSheet to AccrueFICA * 2
							set formula of range "B31" of sheet AccrueSheet to AccrueMedicare * 2
							set formula of range "B35" of sheet AccrueSheet to AccrueStateTax
							set formula of range "B37" of sheet AccrueSheet to AccrueSDI
						end tell
					else
						if SheetMonth is greater than AccrueMonth then
							exit repeat
						end if
					end if
				else
					exit repeat
				end if
			end tell
		end repeat
		set PayrollSheetNum to i
	end tell
end MonthlyAccrue

It does the math correctly and runs no matter what page I have selected in Excel, so I think it’s ready to use for my accounting. I do have one question, though. It seems to take about 8 seconds to run through it’s paces, which I’m assuming may be partly the overhead of Excel. I can literally watch it going through each of it’s iterations. Do you see anything in my script that is unnecessarily verbose or repetitive? Anything that would be done faster by using a list? The speed is really not an issue, other than I’m enjoying solving a puzzle of how to do this in the most efficient manner. 8 seconds out of my day once a week or so is not a big deal.

Thanks again for all of your help. Do you and the other power-helpers on this board do this for a living?

Tom

Tom_Carlson · April 14, 2008, 5:20pm

I did move the block where the formula is entered into the cells in MonthlyAccrue to a spot after the end of the repeat inside of that subroutine. That shaved about 3 seconds off of the time it takes to run.

end repeat
		if AccrueWeeks is greater than 0 then
			set AccrueSheet to ("" & (AccrueMonth as text) & "")
			set formula of range "C1" of worksheet AccrueSheet to AccrueWeeks
			set formula of range "B14" of worksheet AccrueSheet to AccrueBase
			set formula of range "B16" of worksheet AccrueSheet to AccrueVacation
			set formula of range "B18" of worksheet AccrueSheet to AccrueHoliday
			set formula of range "B27" of worksheet AccrueSheet to AccrueFedTax
			set formula of range "B29" of worksheet AccrueSheet to AccrueFICA * 2
			set formula of range "B31" of worksheet AccrueSheet to AccrueMedicare * 2
			set formula of range "B35" of worksheet AccrueSheet to AccrueStateTax
			set formula of range "B37" of worksheet AccrueSheet to AccrueSDI
		end if
		set PayrollSheetNum to i
	end tell
end MonthlyAccrue

Now it does all of the math first and then enters the result once at the end of the calculations. Other than that, does anyone see any other ways to speed things up?

Tom