Select and set values

In Visual Basic for Applications (VBA), ranges are specified using the Range property applied to a Worksheet (or to another Range), or Range's Cells property applied to a Range. The Cells property is the default property for the Range object, so it generally is not explicitly called by name. You might see just parentheses with two numbers representing the row and column of the cell respectively.

There is no "cells" property of a range or worksheet in AppleScript. Just use the appropriate number to get the cell (column number) of row (row number), as in the second line of the next AppleScript snippet. That is, where in VBA you write .Cells(10, 6) or just .(10, 6), in AppleScript you write cell 6 of row 10.

Ranges can be selected and set directly.

Range("A1").Select
ActiveWorksheet.Cells(10, 6).Select
Set rMyRange = Range("B10:C14")

becomes:

tell application "Microsoft Excel"
	select range "A1"
	select (cell 6 of row 10 of active sheet)
	set myRange to range "B10:C14"
end tell

You can use the get resize command in the Table Suite (where most commands acting on ranges are to be found), where VBA uses the .Resize property (or method) to expand or choose a range.

Range("A1").Resize(10, 10).Select 'Selects A1:J10

becomes:

select (get resize range "A1" row size 10 column size 10) 
--selects A1:J10

Set a non-contiguous range to a single value

For information about how to set the values of a range to a list of lists (rows), see Select and set values, height, and width. VBA is also full of shortcuts, such as the following:

Range("A1:B5,G9,A16:D19") = 5

The Range address describes a non-contiguous range that consists of the rectangle containing 10 cells (two columns of five rows) withinA1:B5, the single cell G9 off on its own, and the discrete rectangle A16:D19 containing 16 cells (four columns of four rows). The statement is a sort of shortcut (you can't actually set such a range to the integer 5) that sets the value of every cell in the range to 5. It works.

Again, it works because VBA has default properties that don't need to be spelled out explicitly if you're in a hurry. Cells is the default property of Range, and Value is the default property of Cell.

At first glance, it doesn't look as if you can do this in AppleScript. But you can — as long as you state the properties explicitly, since there are no default properties in Microsoft Excel AppleScript:

set value of every cell of range "A1:B5,G9,A16:D19" to 5

This is pure AppleScript: no shortcut required. It sets the value of every cell of that non-contiguous range to 5, all in one go, just as the VBA version does.

Your feedback was not submitted because the network connection was lost. Verify that you have an Internet connection, and then try submitting your feedback.

If you are still unsuccessful, the feedback server might be temporarily disconnected. Try submitting your feedback later.

Developers

Select and set values

Set a non-contiguous range to a single value