Can VBA Be Used In Google Sheets? -

If you are someone that is used to programming with VBA in Excel you may be wondering whether it is possible to use VBA in Google Sheets as well. Are the commands the same or are there significant differences?

Unfortunately, google sheets does not use VBA, however, it is possible to program in Google Sheets using an alternative language called Google Apps Script which can be accessed by going to the extensions tab in google sheets and selecting Apps Script.

What Is Google App Script?

Google Apps script plays a similar role to VBA within the suite of Google products. It can be used to automate Google sheets, Google docs, Gmail, and a range of other products as well as automating 3rd party products. It has a syntax that is similar to Javascript in many respects and has been described by Google as being;

“ a JavaScript cloud scripting language that provides an easy way to automate tasks across Google products and third-party services and can also be used to build web applications”

Within these packages google app scripts has the capacity to create menus, sidebars, and message boxes just like VBA. Additionally, it is also capable of creating add-ons and automating more advanced Google products such as YouTube, Google Adsense, and Google Analytics. The key advantage of Google Apps is that there is no need to install anything on a PC, it can be executed on the Google server.

Google app scripts also have the capacity to be triggered when a specific event occurs, at a specific time, or at specific intervals which is similar to VBA in that respect.

However, the google app script does have the limitation that it cannot run scripts for longer than 6 minutes. Additionally, it cannot send more than 2000 emails a day and most Google products have some limitations associated with them. However, for most applications, this is not a significant problem.

Why Do Excel And Google Sheets Use Different Languages?

There are several reasons why the two applications use different languages. The first and most significant is that Visual Basic for Applications is a proprietary language that has been discontinued by Microsoft in 2008 though it is still extremely widely used among excel users.

The second significant reason is that Google App Script is a language that is designed to work on the server side of the interface which means that the script is actually run on Google servers whereas VBA is designed to work on the user side within the local files.

This has some disadvantages when it comes to file sharing. Excel sheets that are co-authored do not have the capacity to have VBA altered once they have been shared though they can still run the macros. However, even here there are some limitations because the app version of Excel will not run xactive controls such as buttons and drop-down lists which in some cases can be a significant limitation.

What Happens If You Import An Excel Sheet Containing VBA Into Google Sheets?

It is possible to import Excel sheets that contain VBA into Google Sheets, however, the functionality of the VBA code will be lost and you will only see the data from Excel appearing in the Google sheet. Conversely, if you export data from Google Sheets containing Google App Script the data will be able to be transferred but you will lose the functionality of the code in the background.

Is VBA Likely To Disappear Soon From Excel?

Given that VBA is so widely used by many Excel users it seems unlikely that VBA will disappear for quite some time, however, it is likely that Microsoft may introduce a second language that is more suited to online applications next to VBA at some point.

However, it is already possible to automate Excel using languages to some degree such as python and SQL which can be used to call in information from a database. So in short I think the VBA will be around for quite some time but probably not forever.

Comparing The Differences Between VBA code And Google APP Script

The structures of the code and Syntax between VBA and Google App Script are fundamentally different. To illustrate this I provided examples of code in both VBA and Google Apps Script for several of the most common functions that you may need if you are manipulating data within a Google spreadsheet.

How To Display A Message Box

VBA

Sub Hello_World   
                                              
Dim my_message As String                              
my_message = “Hello World”		   
Msgbox my_message
SpreadsheetApp.getUi().alert(my_message);

End Sub

Google Apps Script

function helloWorld() {
  let my_message='Hello World';
  Browser.msgBox(my_message);
}

What the Code Does: Displays a message box that says hello world

How To Enter Text Into A Cell

VBA

Sub Entering_Info_Into_cell

Worksheets(“sheet1”).range(“A1”) = “Text”
Worksheets(“sheet1”).range(“A2”) = 1

End Sub

Google Apps Script

function Entering_Info_Into_cell1() {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getSheetByName('Sheet1');
  sheet.getRange("A1").setValue("Text");
  sheet.getRange("A2").setValue(1);
}

What the Code Does: Places text in cell A1 and a number in cell A2

How To Complete A Calculation

VBA

Sub Entering_Info_Into_cell                 
                                          
Worksheets(“sheet1”).range(“A1”) = 1 + 1
Worksheets(“sheet1”).range(“A2”) = 1 +_
Worksheets(“sheet1”).range(“A1”)

End Sub

Google Apps Script

function Entering_Info_Into_cell() {  
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getSheetByName('Sheet1');
  sheet.getRange("A1").setValue(1+1);
  sheet.getRange("A2").setValue(sheet.getRange("A1").getValue() + 1);  
}

What The Code Does: Adds 2 numbers together in cell A1 and then in A2 it adds the value of cell A1 to a number

How To Create A Loop

VBA

Sub Creating_A_Loop

Dim Row As Integer

For Row 1 To 10 Step 1

Worksheets(“sheet1”).range(“A” & Row) = Row

Next Row

End Sub

Google Apps Script

function creating_a_loop() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');
  for (let i=1; i<11; i++) {
    sheet.getRange("A" + i).setValue(i);
  }
}

What The Code Does: Places the values 1 to 10 in cells A1 to A10

To Get User Input

VBA

Sub Entering_Info_Into_cell

Dim myValue As Variant

myValue = InputBox("Give me some input")
Worksheets(“sheet1”).range(“A1”)  = myValue

End Sub

Google Apps Script

function Entering_Info_Into_cell() {
  const myValue = Browser.inputBox('Give me some input');
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');
  sheet.getRange("A1").setValue(myValue);
}