How can I invert my selection in Microsoft Excel using AppleScript? (selection itself, not properties of selection)

Solution 1:

Finally, I have a solution. Huge thanks to Monomeeth for the VBA version that set me on the right path. (And making it possible to do this under Windows too)

Works similarly to Monomeeth's version. It has a range considered for performance reasons. Set it by changing this line accordingly:

set range_considered to range "A1:Z100" of active sheet

Here it is:

-- use "intersect" to test whether the considered area overlaps with the selected area
-- use union to append non-selected cells to a new range

tell application "Microsoft Excel"
    set screen updating to false -- optimize performance
    --tell active sheet of active workbook
    set range_considered to range "A1:Z100" of active sheet
    set range_selected to selection
    set range_new_selection to "Nothing"

    -- setup ref vars for selection
    tell selection to set {range_selected_row_index, range_selected_column_index, range_selected_row_count, range_selected_column_count} to {get first row index of selection, get first column index of selection, get count rows of selection, get count columns of selection}
    -- setup ref vars for considered
    tell selection to set {range_considered_row_index, range_considered_column_index, range_considered_row_count, range_considered_column_count} to {get first row index of range_considered, get first column index of range_considered, get count rows of range_considered, get count columns of range_considered}

    -- go column by column and iterate each row in each column
    repeat with considered_col_step from range_considered_column_index to range_considered_column_count
        repeat with considered_row_step from range_considered_row_index to range_considered_row_count
            set range_this_cell to range (get address row considered_row_step of column considered_col_step of active sheet) of active sheet
            log range_selected
            try
                set range_test to intersect range1 range_this_cell range2 range_selected -- test for intersection with selected
                (*use try to detect error. Will fail if cell is within selection*)
                log "TRUE Intersected"
            on error
                (*Create or append to range_new_selection*)
                log "FALSE intersected"
                if range_new_selection is "Nothing" then
                    set range_new_selection to range_this_cell
                else
                    set range_new_selection to union range1 range_new_selection range2 range_this_cell
                end if
            end try
        end repeat
    end repeat
    try
        select range_new_selection
    on error
        log "Could not select new range."
    end try
    set screen updating to true -- optimize performance
end tell

Solution 2:

I've been working on this question for a while and, hitting a brick wall, decided to try and get it to work using Visual Basic instead. My hope is that doing this will make it easier to get the code right within AppleScript, so I'm sharing the Visual Basic code in case it helps someone else to get an AppleScript version working - not that I've given up on this myself yet! :)

Sub InvertSheet()

Set s1 = Selection
Set s2 = Range("A1:Z100")
Set sinv = Nothing

For Each s In s2
If Intersect(s, s1) Is Nothing Then
If sinv Is Nothing Then
Set sinv = s
Else
Set sinv = Union(sinv, s)
End If
End If
Next

If sinv Is Nothing Then
Else
sinv.Select
End If
End Sub

NOTES:

• I did come across a problem - namely that I couldn't find a way to get this to apply to the entire sheet without it causing Excel to hang for extremely long periods of time (probably because it has to check 1,048,576 rows and 16,384 columns) to do so.
• To get around this problem I used Set s2 = Range("A1:Z100") to limit the area of the sheet that the code applies to to the range of A1:Z100. However, this can be changed to suit a larger area if needed.
• I imagine trying to cover the whole sheet will be a problem within AppleSCript as well.