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 browser or teams version of excel. I have been unable to find away reliable way to get the excel document to open in the native desktop app for all users who wish to edit the document, so now I am here hoping that an office script can be written which completes the same task as the VBA thus making it unnecessary for the user to have to open the excel file in a desktop app. 

 

Alternatively, if there is an office script that could be created that would, upon opening of the teams/web browser excel open the document in the native app as well, that would solve the issue also. 

 

Please take a look and let me know if it is even possible, thank you: 

 

 

 

 

 

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sID As String, oTab As ListObject, rngVis As Range
    Const KEYWORD = "Discontinued"
    Const UID = "Unique ID"
    Const DEST_SHT = "Archived_CVL"
    With Target
        If .CountLarge = 1 Then
            If .Column = 9 And .Row > 3 Then
                If (Not .ListObject Is Nothing) And StrComp(KEYWORD, .Value, vbTextCompare) = 0 Then
                    Set oTab = .ListObject
                    sID = Me.Cells(.Row, 1).Value
                    'Debug.Print sID
                    If oTab.AutoFilter.FilterMode Then oTab.AutoFilter.ShowAllData
                    oTab.Range.AutoFilter Field:=1, Criteria1:=sID
                    On Error Resume Next
                    Set rngVis = oTab.DataBodyRange.SpecialCells(xlCellTypeVisible)
                    On Error GoTo 0
                    If Not rngVis Is Nothing Then
                        With Sheets(DEST_SHT)
                            rngVis.Copy .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
                        End With
                        Application.EnableEvents = False
                        oTab.AutoFilter.ShowAllData
                        rngVis.Delete
                        Application.EnableEvents = True
                    End If
                End If
            End If
        End If
    End With
End Sub

 

 

 

 

 

Thanks again

 

excel
Excel for web
Macros and VBA
office 365
Office Scripts
  • NikolinoDE's avatar
    NikolinoDE
    Gold 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
        Gold 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