question

SmirnovAnatolii-5328 avatar image
0 Votes"
SmirnovAnatolii-5328 asked kinuasa answered

How to convert VBA script to Office script?

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


office-vba-devoffice-scripts-excel-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

kinuasa avatar image
0 Votes"
kinuasa answered

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

https://docs.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)"));
 }


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.