Forum Discussion

ItsNursinTime's avatar
ItsNursinTime
Copper Contributor
Feb 13, 2024

VBA to Office Script Help

Hello,    I have a VBA written that performs my task as intended. However this xlsm file needs to be sharable and edited by multiple people over Teams. As you all know, VBAs wont work using the bro...
excel
Excel for web
Macros and VBA
office 365
Office Scripts
jeffrey_mccoy's avatar
jeffrey_mccoy
Copper Contributor
May 25, 2024

NikolinoDE 

 

I need some help rewriting this Excel VB script to an Office Script please.  The VB scripts checks for duplicate cells in the columns specified, and then merges them and centers them into 1 large merged cell that takes up multiple rows, while preserving the individual cell information in columns B - P that are not specified in the routine.  I appreciate any help you can provide.  thank you!!

 

Sub MergeCells()
   'set your data rows here
   Dim Rows As Integer: Rows = 500

   Dim First As Integer: First = 1
   Dim Last As Integer: Last = 0
   Dim Rng As Range

   Application.DisplayAlerts = False
   With ActiveSheet
      For i = 1 To Rows + 1
         If .Range("A" & i).Value <> .Range("A" & First).Value Then
            If i - 1 > First Then
               Last = i - 1

               Set Rng = .Range("A" & First, "A" & Last)
               Rng.MergeCells = True
               Set Rng = .Range("E" & First, "E" & Last)
               Rng.MergeCells = True
               Set Rng = .Range("F" & First, "F" & Last)
               Rng.MergeCells = True
               Set Rng = .Range("G" & First, "G" & Last)
               Rng.MergeCells = True
               Set Rng = .Range("H" & First, "H" & Last)
               Rng.MergeCells = True
               Set Rng = .Range("M" & First, "M" & Last)
               Rng.MergeCells = True
               Set Rng = .Range("N" & First, "N" & Last)
               Rng.MergeCells = True
               Set Rng = .Range("O" & First, "O" & Last)
               Rng.MergeCells = True

            End If

            First = i
            Last = 0
         End If
      Next i
   End With
   Application.DisplayAlerts = True
End Sub

NikolinoDE's avatar
NikolinoDE
Platinum Contributor
May 26, 2024

jeffrey_mccoy 

Office Scripts for Excel on the web are similar to VBA but have some key differences in syntax and functionality. Here is how you can rewrite your VBA script to an Office Script to merge and center cells based on the specified columns:

Office Script

Code is untested please backup your file.

function main(workbook: ExcelScript.Workbook) {
  let sheet = workbook.getActiveWorksheet();
  let rowCount = 500; // Set your data rows here
  let first = 0;
  let last = 0;

  for (let i = 0; i <= rowCount; i++) {
    let currentCellValue = sheet.getRange(`A${i+1}`).getValue();
    let firstCellValue = sheet.getRange(`A${first+1}`).getValue();

    if (currentCellValue !== firstCellValue) {
      if (i - 1 > first) {
        last = i - 1;
        mergeRange(sheet, first, last, "A");
        mergeRange(sheet, first, last, "E");
        mergeRange(sheet, first, last, "F");
        mergeRange(sheet, first, last, "G");
        mergeRange(sheet, first, last, "H");
        mergeRange(sheet, first, last, "M");
        mergeRange(sheet, first, last, "N");
        mergeRange(sheet, first, last, "O");
      }
      first = i;
      last = 0;
    }
  }
}

function mergeRange(sheet: ExcelScript.Worksheet, first: number, last: number, column: string) {
  let rangeAddress = `${column}${first + 1}:${column}${last + 1}`;
  let range = sheet.getRange(rangeAddress);
  range.merge(true);
  range.getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);
  range.getFormat().setVerticalAlignment(ExcelScript.VerticalAlignment.center);
}

Explanation

  1. Setting Up the Script: The script starts by defining the worksheet and the number of rows to process.
  2. Loop Through Rows: It loops through the rows to find duplicates in column A.
  3. Merge and Center Cells: When a duplicate is found, the script merges and centers the cells in the specified columns (A, E, F, G, H, M, N, O).
  4. Helper Function: The mergeRange function handles the merging and centering of a specified range within a column.

Running the Script

  1. Open Excel Online: Open your workbook in Excel Online.
  2. Access Office Scripts: Go to the "Automate" tab and select "Code Editor."
  3. Create New Script: Paste the script above into a new script and save it.
  4. Run the Script: Execute the script to perform the merge and center operation.

This Office Script achieves the same functionality as your original VBA script but is adapted to work in the Excel Online environment using TypeScript.

NOTE: My knowledge about Office Script is limited so I entered your question in the AI. The text and the steps are the result of the AI. Maybe it will help you further in your project, if not please just ignore it.

Resources