Generating Test Data with AppleScript

[JE McGimpsey]

I've long had a VBA routine to generate test data for a range with both
Mac and Windows XL. One of my frustrations with XL08 used to be that
generating simple fills was time consuming.

However, I've been using an AppleScript routine to generate that data
for a while now, and I thought I'd share it.

Basically: The routine looks at the selected range and fills it with the
numbers from 1 to n (where n is the number of cells selected). I've
included the command to randomize the data - comment out or delete that
line of AS to fill in a range in numeric order (by rows).

I've used a stripped down version of my ListObject - removing error
checking since it's not required in this instance. I'm not an
AppleScript maven, and the randomization and list-handling techniques
aren't the most efficient, but filling a 100-cell range takes less than
a second and a 1000 cell range less than 3, so I haven't spent any time
optimizing it.

I've got it saved in my Excel Script Items folder, with a keyboard
shortcut assigned.

Hope this might be useful:

on run
tell application "Microsoft Excel"
set theRange to selection
tell theRange
set theRows to count of rows
set theColumns to count of columns
set theArray to my MakeNumberList(theRows * theColumns)
-- comment or delete the next line to get numeric order list
tell theArray to RandomizeList()
set value to theArray's Return2DList(theRows, theColumns)
end tell
end tell
end run

on MakeNumberList(theLength)
script ListObject
property theList : {}
to Initiate()
repeat with i from 1 to theLength
copy i to the end of theList
end repeat
end Initiate
to RandomizeList()
repeat with theIndex from (count of theList) to 2 by -1
set theSwitch to (random number from 1 to theIndex)
tell theList
set temp to item theSwitch
set item theSwitch to item theIndex
set item theIndex to temp
end tell
end repeat
end RandomizeList
to Return2DList(theRows, theColumns)
set theIndex to 1
set the2DList to {}
tell the2DList
repeat with theRow from 1 to theRows
copy {} to the end
repeat with theColumn from 1 to theColumns
copy item theIndex of theList to the end of item theRow
set theIndex to theIndex + 1
end repeat
end repeat
end tell
return the2DList
end Return2DList
to ReturnItem(itemNumber)
return item itemNumber of theList
end ReturnItem
to ReturnList()
return theList
end ReturnList
to ReverseList()
set theList to the reverse of theList
end ReverseList
end script
tell ListObject to Initiate()
return ListObject
end MakeNumberList