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
NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Feb 14, 2024

ItsNursinTime 

To convert your VBA code to an Office Script that can be used in Excel Online or in the Teams version of Excel, we'll need to rewrite the code using the syntax and features supported by Office Scripts. Unfortunately, not all VBA functionality is available in Office Scripts, so we may need to make some adjustments to your code.

Below is a version of your VBA code converted to an Office Script. This script will be triggered whenever a change is made in the worksheet, similar to the Worksheet_Change event in VBA:

Javascript code is untested, please backup your file.

function main(workbook: ExcelScript.Workbook, sheet: ExcelScript.Worksheet, changedRange: ExcelScript.Range) {
  const KEYWORD: string = "Discontinued";
  const UID: string = "Unique ID";
  const DEST_SHT: string = "Archived_CVL";

  let targetCell = changedRange.getResizedRange(0, -8);
  let targetValue = targetCell.getValue();

  if (targetValue && typeof targetValue === 'string' && targetCell.getColumn() === 9 && targetCell.getRow() > 3 && targetValue.toLowerCase() === KEYWORD.toLowerCase()) {
    let listObject = sheet.getListObjects()[0];
    let sID = sheet.getRange(targetCell.getRow(), 1).getValue();

    if (listObject) {
      listObject.getAutoFilter().clear();
      listObject.getAutoFilter().applyFilter(1, sID);

      let visibleRange = listObject.getRange().getSpecialCells(ExcelScript.SpecialCellType.visible);
      if (visibleRange) {
        let destSheet = workbook.getWorksheet(DEST_SHT);
        let destRange = destSheet.getRange(destSheet.getUsedRange().getRowCount() + 1, 1);
        visibleRange.copyTo(destRange, ExcelScript.CopyToType.all, false);
        visibleRange.delete(ExcelScript.DeleteShiftDirection.up);
      }
    }
  }
}

 

Here are some key differences and considerations:

  1. We use TypeScript syntax for Office Scripts, which is similar to JavaScript.
  2. We use the main function as the entry point for the script.
  3. We use the getResizedRange method to adjust the target range by moving 8 columns to the left.
  4. We use methods like getValue, getRange, getListObjects, getAutoFilter, and getSpecialCells to manipulate the worksheet data and objects.
  5. We use toLowerCase() to perform case-insensitive string comparison.

Please note that Office Scripts are still evolving, and not all VBA functionality is available. You may need to adapt your code further or explore alternative solutions depending on your requirements and the features available in Office Scripts. The text, steps and the script was created with the help of AI.

Formularbeginn

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

 

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

  • 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