How to convert VBA script to Office script?

Question

Hi all, i was working with VBA scripts many years but i really dont get how can i run my scripts into office 365.
Specially i did i simple script to count cells in rage in multiply sheets. see below. is there some way how to convert that to java? Office script is it Java right?

Sub SumValueAcrossTabs()
Dim i As Integer
Dim ws_num As Integer
Dim ws As Worksheet
Dim SumRg As Range
Dim x As Long
x = 0
ws_num = ThisWorkbook.Worksheets.Count
For i = 1 To ws_num
ThisWorkbook.Worksheets(i).Activate
Set SumRg = ActiveSheet.Range("Q4:Q400")
Range("W1").Value = WorksheetFunction.Count(SumRg)
Next
For i = 1 To ws_num
x = x + Sheets(i).Range("W1").Value
Next i
Sheet1.Range("C10").Value2 = x
End Sub

Answer

Hi. Office Scripts is TypeScript-based script, not Java.
First of all, I recommend that you refer to the following Overview.

https://learn.microsoft.com/en-us/office/dev/scripts/overview/excel?WT.mc_id=M365-MVP-4029057

There is no way to convert VBA-code to Office Scripts-code, so you will need to write code yourself.
Below is a simple sample code.

function main(workbook: ExcelScript.Workbook)  
{  
  workbook.getWorksheets().map(sheet => sheet.getRange("W1").setValue("=SUM(Q4:Q400)"));  
}

Answer

I was working with VBA scripts, but I really don't understand how I can run my VBA anymore and how to convert it to my scripts in Office 365. Is there any way to convert this to Java? Office Script is in Java, right?

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim valorAlterado As Variant

    Dim colunaPesquisa As Range

    Dim celulaEncontrada As Range

    Dim linhaAlterada As Range

    Dim linhaEncontrada As Range

    Dim coluna As Integer

    

    ' Verifica se a alteração foi feita na coluna "D"

    If Not Intersect(Target, Me.Range("D:D")) Is Nothing Then

        Application.EnableEvents = False ' Desabilita os eventos para evitar chamadas recursivas

        

        ' Obtém o valor alterado na coluna "D"

        valorAlterado = Target.Value

        Set linhaAlterada = Target.EntireRow

        

        ' Define a coluna de pesquisa como coluna "O"

        Set colunaPesquisa = Me.Range("O:O")

        

        ' Procura pelo valor na coluna de pesquisa

        Set celulaEncontrada = colunaPesquisa.Find(valorAlterado, LookIn:=xlValues)

        

        ' Se o valor for encontrado, inverte as colunas da "N" à "AI" entre as linhas

        If Not celulaEncontrada Is Nothing Then

            Set linhaEncontrada = celulaEncontrada.EntireRow

            

            ' Inverte as colunas da "N" à "AI" entre as linhas

            For coluna = 14 To 35 ' Colunas "N" a "AI"

                Dim valorTemp As Variant

                valorTemp = linhaAlterada.Cells(1, coluna).Value

                linhaAlterada.Cells(1, coluna).Value = linhaEncontrada.Cells(1, coluna).Value

                linhaEncontrada.Cells(1, coluna).Value = valorTemp

            Next coluna

        End If

        

        Application.EnableEvents = True ' Reabilita os eventos

    End If

End Sub

How to convert VBA script to Office script?

2 answers