Re: Defining an excel function with applescript
Re: Defining an excel function with applescript
- Subject: Re: Defining an excel function with applescript
- From: Stan Cleveland <email@hidden>
- Date: Wed, 30 Jan 2008 15:05:53 -0800
- Thread-topic: Defining an excel function with applescript
On 1/17/08 5:13 AM, David Erb wrote:
> I have an excel file that uses visual basic to define a function in excel. It
> allows someone to count a range of numbers based on the background color of
> the cell.
>
> The visual basic looks like this:
>
> Function CountByColor(InRange As Range, _
> WhatColorIndex As Integer, _
> Optional OfText As Boolean = False) As Long
> '
> ' This function return the number of cells in InRange with
> ' a background color, or if OfText is True a font color,
> ' equal to WhatColorIndex.
> '
> Dim Rng As Range
> Application.Volatile True
>
> For Each Rng In InRange.Cells
> If OfText = True Then
> CountByColor = CountByColor - _
> (Rng.Font.ColorIndex = WhatColorIndex)
> Else
> CountByColor = CountByColor - _
> (Rng.Interior.ColorIndex = WhatColorIndex)
> End If
> Next Rng
>
> End Function
>
> You can then call this function from a worksheet cell with a formula like
> "=COUNTBYCOLOR (A1:A10, 3, FALSE)"
>
> With Excel 2008 for Mac eliminating visual basic, I'm wondering if the above
> macro can be used with applescript somehow. Or is there another way to count a
> series of numbers based on the background shading, using only what is
> available with applescript?
This should do the job (watch out for text wrap):
-- set desired parameters
set theRange to "A1:G3"
set whatColorIndex to 2 -- white
set ofText to true
set shadedCount to countShadedCells(theRange, whatColorIndex, ofText)
on countShadedCells(theRange, whatColorIndex, ofText)
set shadedCount to 0
tell application "Microsoft Excel"
set theRange to range theRange
set numCells to count cells of theRange
repeat with i from 1 to numCells
if ofText then
set fontColor to font color index of font object of cell i of theRange
if fontColor is whatColorIndex then
set shadedCount to shadedCount + 1
end if
else -- of bkgd
set bkgdColor to color index of interior object of cell i of theRange
if bkgdColor is whatColorIndex then
set shadedCount to shadedCount + 1
end if
end if
end repeat
end tell
return shadedCount
end countShadedCells
Sorry to be so slow in responding, but have been extra busy lately. Hope
it's not too late and will work for your project.
Stan C.
_______________________________________________
Do not post admin requests to the list. They will be ignored.
AppleScript-Users mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
Archives: http://lists.apple.com/archives/applescript-users
This email sent to email@hidden