Office-js-docs-pr: "A range is a group of contiguous cells in the workbook."

[beyphy]

[Enter feedback here]

I was reading this article when I ran into the quote "A range is a group of contiguous cells in the workbook." I thought that this was an odd statement considering that ranges don't need to be contiguous. As an example, the following VBA code for a non-contiguous range is valid:

Range("A1,B6,D4").Value = 5

Similarly, in the Excel worksheet, you could manually select individually by holding the control key. If you typed 5 in the active cell and hit ctrl+enter, it would assign the value to all of the selected cells. However, this does not seem to work with the JavaScript API.

I ran the code in the Basic API call (TypeScript) sample. This sample highlights the selected range yellow and writes the range's address to the console. However, it only works if you select a contiguous range (e.g. A1:A4, A1:D1, A1:D4, etc.) If you instead individually non-contiguous cells like I did above, you'll get a runtime error. Please see the error below:

```n {name: "RichApi.Error", code: "InvalidSelection", traceMessages: Array[0], innerError: null, debugInfo: Object…}
name: "RichApi.Error"

code: "InvalidSelection"

traceMessages: Array[0]

innerError: null

▶debugInfo: Object
code: "InvalidSelection"

message: "The current selection is invalid for this operation."

toString: function toString()

errorLocation: "Workbook.getSelectedRange"

statement: "var selectedRange = workbook.getSelectedRange();"

▶surroundingStatements: Array[9]
0: "var workbook = context.workbook;"

1: "// >>>>>"

2: "var selectedRange = workbook.getSelectedRange();"

3: "// <<<<<"

4: "// Instantiate {selectedRange}"

5: "var format = selectedRange.format;"

6: "var fill = format.fill;"

7: "fill.color = ...;"

8: "selectedRange.load(["address"]);"

▶fullStatements: Array[1]

httpStatusCode: 400

data: undefined```

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: 740e2391-1f3e-5c38-859a-2353c781d6ab
• Version Independent ID: e485961d-0ba8-eee3-b1a9-aa0912238de1
• Content: Excel JavaScript object model in Office Add-ins - Office Add-ins
• Content Source: docs/excel/excel-add-ins-core-concepts.md
• Product: excel
• Technology: add-ins
• GitHub Login: @o365devx
• Microsoft Alias: o365devx

[ElizabethSamuel-MSFT]

@beyphy Thank you for asking about this.

@alison-mk Can you take a look?

Thanks.

[Rick-Kirkham]

This might be relevant: https://docs.microsoft.com/en-us/office/dev/add-ins/excel/excel-add-ins-multiple-ranges

[alison-mk]

Hi @beyphy, thanks for submitting.

You're correct, VBA accepts a set of non-contiguous cells as a "range" while the Excel JavaScript API does not. This difference is by design in the Excel JavaScript API. Instead, with the Excel JavaScript API you can use the RangeAreas object (explained in the Work with multiple ranges simultaneously in Excel add-ins article mentioned by Rick Kirkham) for discontiguous groups of cells.

I'll close this issue but, but please feel free to let us know if you have any more questions.

Cheers,
Alison

[beyphy]

Okay great. Thanks for your assistance. I wasn't aware of the distinction in the Javascript API for contiguous and non-contiguous ranges. In the Basic API call (TypeScript) example that I used in ScriptLab, it looks like the error can be avoided by updating the getSelectedRange() method to the getSelectedRanges() method.