applescript to resize font of all non-empty cells

[Jon Nall]

Hi all.
I spent about an hour tonight trying to write a small applescript that
will take each non-empty cell in an excel 2004 workbook, and change its
font size to it's current font size + 3. To get a list of cells, i
tried this:

tell application "Microsoft Excel"
repeat with c in cells whose value is not ""
set fSize to size of font object of c
set fSize to fSize + 3
set size of font object of c to fSize
end repeat
end tell

without very good results (the following from the Applescript Event Log):
tell application "Microsoft Excel"
count every cell whose value ≠ ""
0
end tell

I tried numerous other variations without much luck. This is my first
attempt at writing an applescript, so I'm sure I'm just doing something
stupid. I've consulted the Applescript Language Ref and the Excel 2004
Applescript Ref, but couldn't find a working algorithm.

I'd be very appreciative if someone could set me on the right path.

Thanks.
nall.

 

[Paul Berkowitz]

That's not the right tack.

First of all, are there lots of different font sizes used in various cells?
Is it really necessary to loop through every single cell?

If there's a standard font size for all cells in the worksheet, this could
probably just be set once.

There are 16777216 cells in every Excel worksheet! Even the 'whose' clause
to get every cell whose value is not "" - if that were even implemented for
a whole worksheet - would tax the memory of the computer.

Better - if it's really necessary to loop through all of them - or not - is
to start by getting the used range - the rectangular range containing just
the area with non-empty cells.

If all cells have the same font size, then it's by far easiest to do it all
at once:


tell application "Microsoft Excel"
set ur to used range of active sheet
set fSize to font size of font object of ur
set fSize to fSize + 3
set font size of font object of ur to fSize
end tell


This works. If even one cell's font size is different from the standard, it
won't work (fSize = 0). So do this repeat loop. Again, it's both simpler and
faster just to ignore (include) any cells within the used range that are
empty:

tell application "Microsoft Excel"
set ur to used range of active sheet
repeat with i from 1 to (count cells of ur)
set theCell to cell i of ur
set fSize to font size of font object of theCell
set fSize to fSize + 3
set font size of font object of theCell to fSize
end repeat
end tell


This works too. But it will work much faster (as will any Excel script, and
especially any one requiring a repeat loop) to set ;screen updating' to
false, and restore it at the end. You won't see anything happening until
it's over, but it will be faster:


tell application "Microsoft Excel"
set ur to used range of active sheet
set screen updating to false
repeat with i from 1 to (count cells of ur)
set theCell to cell i of ur
set fSize to font size of font object of theCell
set fSize to fSize + 3
set font size of font object of theCell to fSize
end repeat
set screen updating to true
end tell

I spent about an hour tonight trying to write a small applescript that
will take each non-empty cell in an excel 2004 workbook, and change its
font size to it's current font size + 3. To get a list of cells, i
tried this:

tell application "Microsoft Excel"
repeat with c in cells whose value is not ""
set fSize to size of font object of c
set fSize to fSize + 3
set size of font object of c to fSize
end repeat
end tell

without very good results (the following from the Applescript Event Log):
tell application "Microsoft Excel"
count every cell whose value ‚ ""
0
end tell

I tried numerous other variations without much luck. This is my first
attempt at writing an applescript, so I'm sure I'm just doing something
stupid. I've consulted the Applescript Language Ref and the Excel 2004
Applescript Ref, but couldn't find a working algorithm.

I'd be very appreciative if someone could set me on the right path.

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

 

[Jon Nall]

First of all, are there lots of different font sizes used in various cells?
Is it really necessary to loop through every single cell?

Yes, unfortunately the spreadsheet has lots of different font sizes, so
doing any sort of bulk operation is not possible.

This works. If even one cell's font size is different from the standard, it
won't work (fSize = 0). So do this repeat loop. Again, it's both simpler and
faster just to ignore (include) any cells within the used range that are
empty:

Ah! "used range" is the piece I was missing. Thanks so much.

Finally, could you explain why the following two snippets behave
differently? The second errors with:
Microsoft Excel got an error: every sheet doesn't understand the count message.

--Snippet #1
repeat with sIdx from 1 to (count sheets)
set ur to the used range of sheet sIdx
...
end repeat

--Snippet #2
repeat with s in sheets
set ur to the used range of s
...
end repeat

Thanks again.
nall.

 

[Paul Berkowitz]

Ah! "used range" is the piece I was missing. Thanks so much.

Yes, it's extremely useful. And note that you really have to specify 'of
active sheet' or some other sheet for it - 'active sheet' is not presumed if
you omit it, it errors instead. (I guess this is a minor bug, since 'of
active sheet' is presumed for every other range such as 'range "A1:K10"'
that doesn't mention a sheet. Mind you, there's nothing in the documentation
that says such a coercion is in place, but everyone has come to expect it.)

Finally, could you explain why the following two snippets behave
differently? The second errors with:
Microsoft Excel got an error: every sheet doesn't understand the count
message.

--Snippet #1
repeat with sIdx from 1 to (count sheets)
set ur to the used range of sheet sIdx
...
end repeat

--Snippet #2
repeat with s in sheets
set ur to the used range of s
...
end repeat

Personally, I have forsaken all use, ever, of that form of repeat loop (#2),
since too often you come to grief. Most often in AppleScript it's because
'repeat with s in someList' _does not evaluate the value of s_. s is 'item x
of someList', which in one particular circumstance - with the equality
operator '=' and its wordy synonyms ('is equal to', 'is') - will always be
false compared to any actual value. (If you insist on using that form, you
can force the evaluation by using the explicit 'get s' or by 'contents of s'
..)

This is different - it's evidently a bug. I'll report it. I know that the
implementation of plural objects as a list is something that had to be
specially effected for Office AppleScript. In VBA (and presumably in the OLE
Automation Object Model that both VBA and AppleScript hook into) there's
something called a 'Collection Object' - not a list (or array) - for plural
objects. The implementation, or coercion, is OK in normal circumstances:

set ss to every sheet of active workbook
count ss

also the implicit coercion to the same thing:

set ss to sheets
count ss


and even

count sheets

without even needing the explicit 'get' [ count (get sheets) ].

So it's a bug that someone forgot to set some evaluation for the standard
'repeat with s in every sheet' loop here.

However, you'll note that this works:

repeat with s in (get sheets)
set ur to the used range of s
...
end repeat


In Office AppleScript (and even more in Apple's OS X apps, too), it's
always, always worth throwing in an explicit 'get' when you get an error
like that.

I'll report the bug. But there are lots and lots of workarounds.

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

 

[Tushar Mehta]

Yes, it's extremely useful. And note that you really have to specify 'of
active sheet' or some other sheet for it - 'active sheet' is not presumed if
you omit it, it errors instead. (I guess this is a minor bug, since 'of
active sheet' is presumed for every other range such as 'range "A1:K10"'
that doesn't mention a sheet. Mind you, there's nothing in the documentation
that says such a coercion is in place, but everyone has come to expect it.)

Interestingly enough, even in VB(A) on the Wintel platform UsedRange
requires a explicit worksheet object. No default is provided.

In the VBE Immediate window, the foll. results in the error "Object
required"
?usedrange.address

whereas the foll. works
?activesheet.usedrange.address

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Ah! "used range" is the piece I was missing. Thanks so much.

Yes, it's extremely useful. And note that you really have to specify 'of
active sheet' or some other sheet for it - 'active sheet' is not presumed if
you omit it, it errors instead. (I guess this is a minor bug, since 'of
active sheet' is presumed for every other range such as 'range "A1:K10"'
that doesn't mention a sheet. Mind you, there's nothing in the documentation
that says such a coercion is in place, but everyone has come to expect it.)

Finally, could you explain why the following two snippets behave
differently? The second errors with:
Microsoft Excel got an error: every sheet doesn't understand the count
message.

--Snippet #1
repeat with sIdx from 1 to (count sheets)
set ur to the used range of sheet sIdx
...
end repeat

--Snippet #2
repeat with s in sheets
set ur to the used range of s
...
end repeat

Personally, I have forsaken all use, ever, of that form of repeat loop (#2),
since too often you come to grief. Most often in AppleScript it's because
'repeat with s in someList' _does not evaluate the value of s_. s is 'item x
of someList', which in one particular circumstance - with the equality
operator '=' and its wordy synonyms ('is equal to', 'is') - will always be
false compared to any actual value. (If you insist on using that form, you
can force the evaluation by using the explicit 'get s' or by 'contents of s'
.)

This is different - it's evidently a bug. I'll report it. I know that the
implementation of plural objects as a list is something that had to be
specially effected for Office AppleScript. In VBA (and presumably in the OLE
Automation Object Model that both VBA and AppleScript hook into) there's
something called a 'Collection Object' - not a list (or array) - for plural
objects. The implementation, or coercion, is OK in normal circumstances:

set ss to every sheet of active workbook
count ss

also the implicit coercion to the same thing:

set ss to sheets
count ss


and even

count sheets

without even needing the explicit 'get' [ count (get sheets) ].

So it's a bug that someone forgot to set some evaluation for the standard
'repeat with s in every sheet' loop here.

However, you'll note that this works:

repeat with s in (get sheets)
set ur to the used range of s
...
end repeat


In Office AppleScript (and even more in Apple's OS X apps, too), it's
always, always worth throwing in an explicit 'get' when you get an error
like that.

I'll report the bug. But there are lots and lots of workarounds.

 

[Paul Berkowitz]

Interestingly enough, even in VB(A) on the Wintel platform UsedRange
requires a explicit worksheet object. No default is provided.

In the VBE Immediate window, the foll. results in the error "Object
required"
?usedrange.address

whereas the foll. works
?activesheet.usedrange.address

Thanks, Tushar. That's reassuring. AppleScript in Office 2004 is meant to
mirror its VBA 5 (and replace it in later versions), since it hooks directly
into the same OLE Automation object model, but some "teething" problems with
this first version have exposed bugs that need to be fixed. Interesting that
VBA has this same requirement. You never know - they might actually "fix" it
for AppleScript next time.

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

 

[Tushar Mehta]

Thanks, Tushar. That's reassuring. AppleScript in Office 2004 is meant to
mirror its VBA 5 (and replace it in later versions), since it hooks directly
into the same OLE Automation object model, but some "teething" problems with
this first version have exposed bugs that need to be fixed. Interesting that
VBA has this same requirement. You never know - they might actually "fix" it
for AppleScript next time.

You are welcome. I wonder what this VBA/AppleScript decision means in terms
of maintaining compatibility between WinOffice and MacOffice? Well, that
and the many changes introduced by Office 2007 including RibbonX
programmability using callback functions written in VB(A). But, then, the
(in)compatibility groundwork was laid long ago when Mac and Win Office
diverged on the VB 5/6 issue and the ActiveX forms controls and the...

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions