VBA Loop to Office.js Loop

This blog article shows you how to do the VBA Loop in Office JS. This article expects you to have basic VBA knowledge. I have a VBA to loop through the column A as follows:

Sub LoopA()

Range(“A1”).Select

While (ActiveCell.Value <> “”)

      Debug.Print ActiveCell.Value

      ActiveCell.Offset(1, 0).Select

Wend

End Sub

To convert to Office JS. Firstly, you need to have Office 365. Open Excel and go to Automate. Click on New Script

Paste the script into the script pane and click on Run.

function main(workbook: ExcelScript.Workbook) {

  const sheet = workbook.getActiveWorksheet();

  let range = sheet.getRange(“A1”);

  while (range.getValue() !== “”) {

    console.log(range.getValue());

    range = range.getOffsetRange(1, 0);

  }

}

Also: SQL Server generate SELECT script using Excel VBA

A few things to note:

• · We use getValue() instead of ActiveCell.Value.
• · Instead of Debug.Print, we use console.log.
• · The getOffsetRange method replaces ActiveCell.Offset.

I believe till now there is no Breakpoint feature for Office JS.

Reference: https://learn.microsoft.com/en-us/office/dev/scripts/overview/exce?WT.mc_id=DP-MVP-36769