3

VBA is the language, in which excel macros were written. Google sheets only supports Google apps script as a macro language. Is there a direct or automatic way to convert a VBA script to a Google Apps Script script without rewriting the code?

Here is a sample VBA script, that I am trying to convert:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngStart As Range, rngCrit As Range
    Set rngStart = Range("A3")
    Set rngCrit = Range("B1")
    On Error GoTo ExitPoint
    Application.EnableEvents = False
    If Not Intersect(Target, rngCrit) Is Nothing Then
        Range(rngStart, Cells(Rows.Count, rngStart.Column).End(xlUp)).ClearContents
        If Val(rngCrit) > 0 Then
            With rngStart.Resize(Val(rngCrit))
                .Value = "=ROW()-" & rngStart.Row - 1
                .Value = .Value
            End With
        End If
    End If
ExitPoint:
    Set rngStart = Nothing
    Set rngCrit = Nothing
    Application.EnableEvents = True
End Sub

What I've tried:

I've tried to substitute some of the commands with some of the equivalent Google Scripts. My issue is I am thinking too linear. I am trying to code the same exact way as VBA and just trying to switch keywords from VBA to ones in Google Script. I was hoping there was a direct method.

TheMaster
  • 45,448
  • 6
  • 62
  • 85
user1772434
  • 39
  • 1
  • 2
  • 1
    Well I've tried to substitute some of the commands with some of the equivalent Google Scripts. My issue is I am thinking too linear. I am trying to code the same exact way as VBA and just trying to switch keywords from VBA to ones in Google Script – user1772434 Oct 26 '12 at 13:30
  • Close voters, consider leaving this question open. This question is better than most vba -> apps script conversion questions and I plan to use this as a close duplicate of all others(see [linked questions](https://stackoverflow.com/questions/linked/13057411)). It won't be nice if this reference question is closed and there's a new easy method available in the future. – TheMaster Oct 24 '21 at 16:40

3 Answers3

1

There are a few examples of such "conversions" out there - but they are typically re-implementations, not conversions. It's a subtle difference, but important. While you could feasibly translate from one language to another, the real challenge is in the differences between the underlying object models and available methods in Excel/VBA vs. Google Spreadsheets / Apps Script / javascript.

Your best chance for success is to use the VBA Sub as a kind of specification; if you can clearly explain what functionality is implemented by a VBA Sub, you have the basis for a specification for a new function implemented in your google spreadsheet.

Mogsdad
  • 44,709
  • 21
  • 151
  • 275
1

You can convert VBA to Google script automatically by using GSpread.NET. It library allows read and write Google Spreadsheets by using Microsoft Excel API. examples: http://scand.com/products/gspread/tutorial.html

miro
  • 101
  • 2
1

Google officially recommends Macro convertor add on.

Notes:

  • It currently seems to need enterprise plus version of Google workspace.
  • There's a compatibility check and, for VBA APIs, where there are corresponding Apps Script APIs(see list), the conversion is automatic.
  • It still isn't a copy paste job for apis that are not directly available. You still should have some proficiency in excel vba and apps script to attempt conversion, where the compatibility report suggests Supported with workarounds or Needs more investigation
TheMaster
  • 45,448
  • 6
  • 62
  • 85