0
votes

I made an Excel spreadsheet with VBA code (macro) and saved it as "MyFile.xlsm". Later I had to upload it on Google Sheets and that's when I faced an issue: my macro was not running.

When I googled it to try to solve the problem, the suggestion was to go to my Google sheet, select Tools --> Macros --> Record macro (or Import). My issue is that I don't have that option in my Tools tab (picture attached). Anyone else having the same problem? If so, do you have an alternative?

enter image description here

2

2 Answers

3
votes

As the screenshot shows the .XLSM label your Excel spreadsheet is being edited using Google Sheets without converting it to the Google Sheets format. In this case some features aren't available like the Script Editor and the Macro recorder as it depends on the Script Editor.

In order to have all the Google Sheets features you have to convert the your Excel file to Google Sheets format but bear in mind that there are some Excel features (including formula functions ) that aren't supported by Google Sheets. Also if you convert the Excel file to Google Sheets the macros will not be converted from VBA to Google Apps Script, you have to convert them.

For more details checkout the official resources liked below.

If you have a Google Workspace account that supports Macro Converter add-on you might use it but bear in mind that the conversion is not something simple as, among other things, it'ss required "to have some proficiency" both with Excel and Google Sheets spreadsheet and with scripting with VBA and Google Apps Script

References

Resources

Switching to Sheets from Microsoft Excel

0
votes

Issue:

I am afraid your are confusing with (GAS). Google Sheets and Excel are two completely different platforms which may look alike but they are a lot different.

Unfortunately you can't use your excel Macros in GAS. If you want to use Macros in a spreadsheet file then you need to create a GAS script. Create a new spreadsheet file, copy the data from the excel file to this spreadsheet file and then go to Tools => Script editor to create a GAS script that does what your VBA would do.

Workarounds:

  • Learn GAS! Here is a basic introduction regarding Google Apps Scirpt.

  • Another idea to create Macros without any prior GAS knowledge is to record a macro in a google spreadsheet.

  • Apparently there is this Macro Converter which is an Add-on that you can install on your spreadsheet that can convert your VBA code to GAS code but it is limited to Enterprise customers and some APIs might not be available. Personally, I haven't used it, but if you want to avoid learning GAS, then I guess it could be a viable solution.

Personally, I can only recommend my first suggestion which is to learn GAS. This will allow you to create optimal solutions tailored to your specific requirements.