Page Summary

outlined_flag

• The Macro Converter automates most of the conversion process but requires manual adjustments for some APIs and other items.

• Additional Apps Script files are added to your project to define VBA constants, implement unconverted APIs, and resolve variants.

• You can fix most errors in theunimplemented_constructs.gs andvariant_resolution.gs files.

• Error types include unimplemented APIs, unimplemented language constructs, partially supported APIs, manual work needed, and intentional errors.

• Each error type has a description and guidance on how to find and fix it, including code examples for common scenarios.

The Macro Converter add-on automates most of the conversion process, but youmight need to make adjustments to some APIs and other items to finalize yourcode.

Use this guide to understand the Apps Script files (GS files) added to yourproject, interpret the different error types, and learn how to fix errors.

Understand Apps Script files added to your project

Additional GS files are added to your Apps Script project to help:

• Define VBA constants and values that don’t exist in Apps Script.
• Implement unconverted APIs.
• Resolve variants.

The following GS files are added to your Apps Script project:

• Library.gs
• Unimplemented_constructs.gs
• Variant_resolutions.gs

Library.gs

In general, you don’t need to modify anything in thelibrary.gs file.

Thelibrary.gs file defines functions and constants that were used in your VBAcode that don’t exist in Apps Script. This helps the new Apps Script code betterresemble your VBA code. Additionally, you don’t need to repeat definitions everytime functions or constants from thelibrary.gs file are used.

Unimplemented_constructs.gs

Theunimplemented_constructs.gs file addresses constructs or APIs that couldn'tbe converted by the Macro Converter. You likely need to modify this file to makeyour code work as intended.

Example:Window.Activate()

The following is an example of an unsupported API calledWindow.Activate().The Macro Converter creates a new Apps Script function with a similar name anddefines it in theunimplemented_constructs.gs file. Since the VBA functionisn’t supported, the new Apps Script function throws an exception.

The new function is added to the converted Apps Script code everywhere theoriginal API was used in the VBA code.

If you find a workaround to recreate the behavior of the original API, you onlyneed to update the definition of the function in theunimplemented_constructs.gsfile. Once the function is defined there, it applies everywhere the functionappears in your Apps Script project.

Here’s the example in code:

Original VBA code

Window.activate()

Converted Apps Script code, added in-line

_api_window_activate();

Function definition added to theunimplemented_constructs.gs file

/** * Could not convert window.activate API. Please add relevant code in the * following function to implement it. * This API has been used at the following locations in the VBA script. *     module1 : line 3 * * We couldn't find an equivalent API in Apps Script for this VBA API. Please * reconsider if this function call is critical, otherwise consider implementing * it in a different way. */function_api_window_activate(CallingObject){ThrowException("API window.activate not supported yet.");}

Variant_resolutions.gs

Thevariant_resolutions.gs file is added to your Apps Script project if anobject’s type can’t be determined. This can happen for multiple reasons, such asan API having multiple return types or the object is declared as a variantitself.

The Macro Converter adds a new function to this file called__handle_resolve_<api>()that replaces the API in question and helps determine the object type.

In some cases, you might need to update the__handle_resolve_<api>() functionto manually declare the object type. SeeUnsupported object type.

Example:name()

Many object types in VBA define aname() API. Usually, the Apps Scriptequivalent isgetName(), but not for every object type. Multiple alternativecases can occur:

• The object's equivalent API is called something different thangetName().
• The object doesn’t have an Apps Script API to get its name.
• There’s not an equivalent Apps Script object.

When the object type isn’t determined, the Macro Converter creates a newfunction called__handle_resolve_name in thevariant_resolutions.gs file.

Here’s the example in code:

Original VBA code

a = Selection.name

In this case, the APIname() is called on the current selection. The selectioncould be a Sheet object or a Shape object. If it’s a Sheet object, thetranslation isgetName(), but if it’s a Shape object, there is no equivalentin Apps Script.

Converted Apps Script code, added in-line

a = __handle_resolve_name({}, getActiveSelection(), {});

The__handle_resolve_name() function below is added to thevariant_resolution.gs file to solve for different object types. The functionchecks the object type, then usesgetName() if it’s supported, or throws anerror ifgetName() isn’t supported.

Function definition added to thevariant_resolution.gs file

function__handle_resolve_name(ExecutionContext,CallingObject,params_map){varfound_api_variant=false;varreturn_value;if(String(CallingObject)=="Sheet"){if(!ExecutionContext.isLhs){return_value=CallingObject.getName();found_api_variant=true;}}if(CallingObjectinstanceofChartInSheet){if(!ExecutionContext.isLhs){return_value=CallingObject.getName();found_api_variant=true;}}if(!found_api_variant){ThrowException("API.name not supported yet.");}returnreturn_value;}

Find errors

When you run into an error in the converted Apps Script code, the messagespecifies the type of error and its location. The format of the error messagedepends on which Apps Script runtime you’re using.

If you’re in the default V8 runtime, you’ll see an error that looks like thefollowing:

_api_windows_active(unimplemented_constructs:2:3)

This means the error is located in theunimplemented_constructs.gs file at line2, character 3.

If you’re in the deprecated Rhino runtime, you’ll see an error that looks likethe following:

unimplemented_constructs:2 (_api_windows_active)

This means the error is located in theunimplemented_constructs.gs file atline 2.

Error Types

You can fix most of the errors you run into in theunimplemented_constructs.gsandvariant_resolution.gs files described above.

The types of errors you might run into include:

• Unimplemented API
• Unimplemented language construct
• Partially supported API
• Manual work needed
• Intentional Error

Unimplemented API

Anunimplemented API is an API that the Macro Converter can’t convert from VBAto Apps Script and there isn’t a known workaround for the API.

Unimplemented APIs are usually added as empty functions—sometimes with emptysignatures—to theunimplemented_constructs.gs file. If the object typecouldn’t be determined, the unimplemented API might be added to thevariant_resolution.gs file, instead.

In the compatibility report you generated before the conversion, this API islabeled asNeeds more investigation.

If you don’t fix this type of API in your VBA code before you convert your file,here’s how it appears in the Apps Script project:

/*** Could not convert. Please add relevant code in the following* function to implement it.* This API has been used at the following locations in the VBA script.* :* We couldn't find an equivalent API in Apps Script for this VBA API. Please* reconsider if this function call is critical, otherwise consider implementing* it in a different way.* @param param1 {}* @param param2 {}* ...* @return {}*/function_api_<API_name>(param1,param2,....){ThrowException("API not supported yet.");}

Fix unimplemented API errors

Define the unimplemented API with existing Apps Script APIs or JS libraries.To do this, follow these steps:

1. Open the converted Apps Script code at the location of the error. SeeFind errors.
2. Above the function, read the comment that was added. In some cases, thecomment suggests how to implement the API in Apps Script.
3. If you can’t find a way to implement the API in Apps Script, considerremoving it from your code.
4. If you can’t find a workaround or remove this API from your code and yourmacro throws this error, you can’t convert this macro.

Examples of unimplemented API errors

Here are examples of unimplemented API scenarios and how to fix them:

• There’s no equivalent Apps Script:Shows an indirect workaround forChart.Protect, an API that doesn’t exist inApps Script.
• An unknown object type: Shows how to handle an object type that’s avariable, and how to implement an unsupported object type that can berecreated in Apps Script.

Example 1: No equivalent Apps Script or unknown API

In this example,Chart.Protect wasn’t automatically converted because thereisn’t a way to protect a chart in Google Sheets.

/*** Could not convert chart.protect API. Please add relevant code in the following* function to implement it.** This API has been used at the following locations in the VBA script.*     sheet1 : line 3* You can use the following Apps Script APIs to convert it.** Comments : Auto conversion of Chart.Protect is not supported yet. If the API is* critical for the workflow the user can implement the unimplemented handler* method in the generated code, else comment out the throw statement.** @param {Object} CallingObject represents the parent object using which the API* has been called.* @param {string} Password* @param {boolean} DrawingObjects* @param {boolean} Contents* @param {boolean} Scenarios* @param {boolean} UserInterfaceOnly**/function_api_chart_protect(CallingObject,Password,DrawingObjects,Contents,Scenarios,UserInterfaceOnly){ThrowException('APIchart.protectnotsupportedyet.');}

Note: When you protect the data range, the document owner can still modify the range and the chart type can still be changed.

/*** Could not convert chart.protect API. Please add relevant code in the following* function to implement it.* This API has been used at the following locations in the VBA script.*     sheet1 : line 3** You can use the following Apps Script APIs to convert it.* Comments : Auto conversion of Chart.Protect is not supported yet. If the API* is critical for the workflow the user can implement the unimplemented handler* method in the generated code, else comment out the throw statement.** @param {Object} CallingObject represents the parent object using which the API* has been called.* @param {string} Password* @param {boolean} DrawingObjects* @param {boolean} Contents* @param {boolean} Scenarios* @param {boolean} UserInterfaceOnly*/function_api_chart_protect(CallingObject,Password,DrawingObjects,Contents,Scenarios,UserInterfaceOnly){varranges=CallingObject.getChart().getRanges();for(vari=0;i<ranges.length;i++){//Notethatthisdoesnotlocktherangeforthedocumentowner.ranges[i].protect();}}

When the object type is unknown, the unimplemented API error is added to thevariant_resolution.gs file. The following example expands on the VBAname()API example above. Seevariant_resolution.gs.

In this example, you’ll learn:

1. How thename() API is converted to a new function in thevariant_resolution.gs file.
2. How the new function is called in the converted code.
3. How to create a workaround forCommandBar, an unsupported object type,in Apps Script.

1. Since the converted code can’t determine the exact object type thatname()is called on, the Macro Converter creates a new function called__handle_resolve_name, shown below.

function__handle_resolve_name(ExecutionContext,CallingObject,params_map){varfound_api_variant=false;varreturn_value;if(String(CallingObject)=="Sheet"){if(!ExecutionContext.isLhs){return_value=CallingObject.getName();found_api_variant=true;}}if(CallingObjectinstanceofChartInSheet){if(!ExecutionContext.isLhs){return_value=CallingObject.getName();found_api_variant=true;}}if(!found_api_variant){ThrowException('API.name not supported yet.');}returnreturn_value;}

2. Suppose the VBA code defines aPrintName() function that calls thename()API. The VBA code is shown below:

‘Defining a function that prints the name of the object in parameterSub PrintName(obj as Variant)  Debug.Print obj.NameEnd Sub

function PrintName(obj) {  Logger.log(_handle_resolve_name(obj));}

3. Suppose your VBA code calls thePrintName() function on the object typeCommandBar. The VBA code is shown below:

PrintName Application.CommandBars.item("Standard")

• Application.CommandBars(): In VBA, this returns a list of allCommandBar objects.
• CommandBars.item(): In VBA, this returns a specificCommandBar object.

• _api_application_commandbars()
• _api_commandbars_item()

PrintName(_api_commandbars_item(_api_application_commandbars(),"Standard")))Here’showthenewfunctionsareaddedtotheunimplemented_construct.gsfile:function_api_application_commandbars(CallingObject){ThrowException('API application.commandbars not supported yet.');}function_api_commandbars_item(CallingObject,index){ThrowException('API commandbars.item not supported yet.');}

To get the new functions to work, take the following steps:

3.1 Define a new object type that creates the functionalities ofCommandBarsand a new collection ofCommandBars similar to what exists in VBA.

3.2 Add agetName() method for the new object type.

Steps 3.1 and 3.2 are shown in the code below. Menu objects are created as anew object type that mimics the behavior ofCommandBars.

//OurImplementationofCommandBarusingMenuobjects.functionCommandBar(name){this.name=name;//Createamenuobjecttorepresentthecommandbar.this.menu=SpreadsheetApp.getUi().createMenu(name);//Createmethodsforretrievingorupdatingthenameoftheobjectthis.getName=function(){returnthis.name;};this.updateName=function(name){this.name=name;};//========================================================================//ImplementothermethodsofCommandBarobjectsthatareusedinthescript.//=====================================================================returnthis;}//OurimplementationofthecollectionofCommandBarsthatexistsinVBAfunctionCommandBars(){this.commandBars=[];this.getCommandBar=function(name){for(vari=0;i<this.commandBars.length;i++){if(!this.commandBars[i].getName()==name){returnthis.commandBars[i];}}//NocommandBarwiththenameexists,createanewoneandreturn.varcommandBar=newCommandBar(name);this.commandBars.push(commandBar);returncommandBar;};returnthis;}//CreateaglobalobjectthatrepresentsCommandBarscollection.varGlobalCommandBars=newCommandBars();

3.3 Modify the__handle_resolve_name function in thevariant_resolution.gsfile to handle the new object type. Add a section to the function, as shownbelow:

function__handle_resolve_name(ExecutionContext,CallingObject,params_map){varfound_api_variant=false;varreturn_value;if(String(CallingObject)=="Sheet"){if(!ExecutionContext.isLhs){return_value=CallingObject.getName();found_api_variant=true;}}if(CallingObjectinstanceofChartInSheet){if(!ExecutionContext.isLhs){return_value=CallingObject.getName();found_api_variant=true;}}//Newsectionaddedbelow//========================================================================if(CallingObjectinstanceofCommandBar){objectExtend(params_map,{VALUETOSET:params_map.param0});if(ExecutionContext.isLhs){//Callthesettermethod.CallingObject.updateName(params_map.VALUETOSET);found_api_variant=true;}else{//Getteriscalled,returnthecommandbarname,return_value=CallingObject.getName();found_api_variant=true;}}//========================================================================//Newsectionaddedaboveif(!found_api_variant){ThrowException('API.name not supported yet.');}returnreturn_value;}

3.4 Define the two functions created in theunimplemented_constructs.gs file(_api_application_commandbars(),_api_commandbars_item()). This step makessure the original calls of the function work.

//This is straightforward based on the implementation of a CommandBar and the// CommandBars collection above:function_api_application_commandbars(CallingObject){returnGlobalCommandBars;}function_api_commandbars_item(CallingObject,index){returnCallingObject.getCommandBar(index);}

Unimplemented language constructs

Aconstruct is an element of the code language that controls execution flowor data display. For example, loops, labels, events, and gotos.Here’s alist of all VBA constructs.

Constructs that the Macro Converter can’t convert are consideredunimplementedlanguage constructs.

Where the Macro Converter determines that an unimplemented language constructexists, it inserts aTODO comment.

The following VBA constructs aren’t supported:

• AddressOf
• Declare
• DefType
• GoSub
• GoTo
• Implements
• Lset
• Open
• RaiseEvent
• Name
• Resume
• Rset
• TypeOf
• Class
• Class Modules

Fix unimplemented language construct errors

1. Update your code so that your logic doesn’t rely on the unsupported languageconstruct.
2. Open the converted Apps Script code at the location of the error. SeeFinderrors.
3. Based on the logic of the code, update it in a way that doesn’t require theunsupported language construct.
4. If you can’t find a way to rewrite your code without the unsupported languageconstruct, you can’t convert this macro.

Examples of unimplemented language construct errors

One of the most common unimplemented language constructs is aGoTo statement.You can replace some VBAGoTo statements with loops. Below are two examples ofusing loops instead ofGoTo statements.

Example 1: ReplaceGoTo withWhile Loop

Sub Test() a = 0 start: Debug.Print a While a< 100   a = a + 1   If a Mod 3 == 0     Goto start   End If WendEnd Sub

functiontest(){vara=0;start:do{console.log(a);while(a<100){a=a+1;if(a%3==0){continuestart;}}breakstart;}while(true);}

Example 2: Replace GoTo with For Loop

Sub Test() a = 0 For i = 1 to 100   For j = 1 to 10     a =a a + 1     If i + j > 50       GoTo endLoop     End If   Next j Next i endLoop: MsgBox aEnd Sub

functiontest(){vara=0;endLoop:for(vari=1;i<=100;i++){for(varj=0;j<=10;j++){If(i+j>50){breakendLoop;}}}Browser.msgBox(a);}breakstart;}while(true);}

Partially supported API

ForPartially supported APIs, some input parameters are supported in AppsScript and some aren’t.

For example, the VBA APIlegend_position is used to define the legend in anExcel graph. It supports multiple types of input values, including:

• xlLegendPositionBottom: Puts the legend at the bottom of the chart.
• xlLegendPositionCorner: Puts the legend at the corner of the chart.
• xlLegendPositionCustom: Puts the legend at custom positions on the chart.

Apps Script has an equivalent code that supports only some of those values. Thefollowing values are not supported:

• xlLegendPositionCorner
• xlLegendPositionCustom

To flag unsupported values of partially supported APIs in your converted code,a validating condition is added to thelibrary.gs file that checks for thosevalues. For example:

if (position == xlLegendPositionCorner ||     position == xlLegendPositionCustom) {   position = _handle_legend_position_error(position);}

If the validating condition finds one of the unsupported values, an errorhandler function,_handle_<API_name>_error, is created in theunimplemented_constructs.gs file.

The function throws a user error and won’t replace the value with a supportedvalue. For example:

/***Throwerrormessageforunsupportedlegendposition.*TheVBAAPILegend.PositionwhichcantakevaluesxlLegendPositionTop,*xlLegendPositionLeft,xlLegendPositionBottom,xlLegendPositionRight,*xlLegendPositionCorner,xlLegendPositionCustom.Itispartiallysupportedin*AppsScriptsthatsupportsonlyasubsetofthevalues(doesnotsupport*xlLegendPositionCornerandxlLegendPositionCustom).*@param{string}position*/function_handle_legend_position_error(position){// Please comment the throw statement and return a supported position value// instead.// Values that are supported here are xlLegendPositionTop,// xlLegendPositionLeft, xlLegendPositionBottom, xlLegendPositionRight.thrownewError('Google Sheets does not support legend position: '+position);}

Fix partially supported API errors

Define the_handle_<API_name>_error function to replace the unsupported valueswith an acceptable workaround for your needs.

1. Open the converted Apps Script code at the location of the error. SeeFind errors.
2. Read the comment above the function to understand which values are supportedand which aren’t.
3. For the unsupported values, determine which supported values can act as asuitable replacement.
4. Update the function_handle_<API_name>_error to return a supported valueinstead.
5. If you can’t find a way to replace the unsupported value, you can’t convertthis macro.

Example of a partially supported API error

The following example expands on the VBA APIlegend_position mentioned above.SeePartially supported API.

Below is an example of original VBA code that uses an unsupported value,xlLegendPositionCustom.

Charts(1).Legend.Position = xlLegendPositionCustom

The Macro Converter adds the below function to theunimplemented_constructs.gsfile:

/***Throwerrormessageforunsupportedlegendposition.*TheVBAAPILegend.PositionwhichcantakevaluesxlLegendPositionTop,*xlLegendPositionLeft,xlLegendPositionBottom,xlLegendPositionRight,*xlLegendPositionCorner,xlLegendPositionCustom.Itispartiallysupportedin*AppsScriptsthatsupportsonlyasubsetofthevalues(doesnotsupport*xlLegendPositionCornerandxlLegendPositionCustom).*@param{string}position*/function_handle_legend_position_error(position){// Please comment the throw statement and return a supported position value// instead.// Values that are supported here are xlLegendPositionTop,// xlLegendPositionLeft, xlLegendPositionBottom, xlLegendPositionRight.thrownewError('Google Sheets does not support legend position: '+position);}

Manual work needed

Manual work needed means that the VBA API can be converted into Apps Script,but it needs a workaround.

In the compatibility report you generated before the conversion, this type ofAPI is labeled asSupported with workarounds.

If you don’t fix this type of API in your VBA code before you convert your file,here’s how it appears in the Apps Script project:

/*** Could not convert API. Please add relevant code in the following* function to implement it.* This API has been used at the following locations in the VBA script.* :** You can use the following Apps Script APIs to convert it.* Apps Script APIs :* Apps Script documentation links :** @param param1 {}* @param param2 {}* ...* @return {}*/function_api_<API_name>(param1,param2,....){ThrowException("API not supported yet.");}

Fix manual work needed errors

Implement a workaround for the API to get the API to work as intended.1. Open the converted Apps Script code at the location of the error. SeeFind errors.1. Read the comment above the function to understand which APIs can be used for a workaround.1. If you can’t find a suitable workaround, consider removing the API from your code.1. If you can’t find a workaround or remove this API from your code and your macro throws an error, you can’t convert this macro.

Examples of Manual work needed errors

Here are examples of APIs that throw Manual work needed errors and how to fixthem:

• Implement a workaround for Autocorrect.Addreplacement.
• Implement a workaround for workbook.open(). Thisexample shows how to open files in Google Drive with Apps Script.

Example 1:Autocorrect.Addreplacement

In the following example, the VBA APIAutocorrect.Addreplacement can beconverted, but it needs a workaround. The Macro Converter suggests how toimplement the function in the code comments.

/*** Could not convert autocorrect.addreplacement API. Please add relevant code in* the following function to implement it.* This API has been used at the following locations in the VBA script.*     sheet1 : line 3* You can use the following Apps Script APIs to convert it.* Apps Script APIs : FindReplaceRequest , onEdit* Apps Script documentation links :* https://developers.google.com/apps-script/reference/script/spreadsheet-trigger-builder#onedit* https://developers.google.com/sheets/api/eap/reference/rest/v4/spreadsheets/request?hl=en#findreplacerequest* Comments : AutoCorrect.AddReplacement was not converted, but there is an* equivalent option you can implement manually. Use onEdit and FindReplaceRequest* APIs instead, see https://developers.google.com/apps-script/reference/script/spreadsheet-trigger-builder#onedit* and https://developers.google.com/sheets/api/eap/reference/rest/v4/spreadsheets/request?hl=en#findreplacerequest.* For more information on API manual implementation, see* https://developers.google.com/apps-script/guides/macro-converter/fix-conversion-errors.* @param {Object} CallingObject represents the parent object using which the API* has been called.* @param {string} What* @param {string} Replacement* @return {string}*/function_api_autocorrect_addreplacement(CallingObject,What,Replacement){ThrowException('APIautocorrect.addreplacementnotsupportedyet.');}

The implementation of theAutocorrect.Addreplacement API is shown below:

varAUTO_CORRECTIONS="AUTO_CORRECTIONS";//Needtogettheautocorrectionssetinprevioussessionsandusethem.varsavedAutoCorrections=PropertiesService.getDocumentProperties().getProperty(AUTO_CORRECTIONS);varautoCorrections=savedAutoCorrections?JSON.parse(savedAutoCorrections):{};functiononEdit(e){autoCorrect(e.range);}functionautoCorrect(range){for(keyinautoCorrections){//Replaceeachwordthatneedstobeauto-correctedwiththeirreplacements.range.createTextFinder(key).matchCase(true).matchEntireCell(false).matchFormulaText(false).useRegularExpression(false).replaceAllWith(autoCorrections[key]);}}/*** Could not convert autocorrect.addreplacement API. Please add relevant code in* the following function to implement it.* This API has been used at the following locations in the VBA script.* sheet1 : line 3** You can use the following Apps Script APIs to convert it.* Apps Script APIs : createTextFinder , onEdit* Apps Script documentation links : https://developers.google.com/apps-script/reference/script/spreadsheet-trigger-builder#onedit ,createTextFinder* Comments : AutoCorrect.AddReplacement was not converted, but there is an* equivalent option you can implement manually. Use onEdit and FindReplaceRequest* APIs instead, see https://developers.google.com/apps-script/reference/script/spreadsheet-trigger-builder#onedit* and createTextFinder. For more information on API manual implementation, see* https://developers.google.com/apps-script/guides/macro-converter/fix-conversion-errors.** @param {Object} CallingObject represents the parent object using which the API has been called.* @param {string} What* @param {string} Replacement** @return {string}*/function_api_autocorrect_addreplacement(CallingObject,What,Replacement){autoCorrections[What]=Replacement;//StoretheupdatedautoCorrectionsinthepropertiessothatfutureexecutionsusethecorrection.PropertiesService.getDocumentProperties().setProperty(AUTO_CORRECTIONS,JSON.stringify(autoCorrections));}

Example 2:Workbook.open()

The VBA APIworkbook.open() opens a local file based on a file path.

Suppose there are two files being opened byworkbook.open() in the VBA code:

• File 1:C:\Data\abc.xlsx
• File 2:C:\Data\xyz.xlsx

The below shows how the Macro Converter replacesWorkbook.open() with AppsScript everywhereWorkbook.open() is used to open File 1:

varspreadSheetId=_handle_mso_excel_get_google_spreadsheet_id("C:\Data\abc.xlsx");varspreadSheet=SpreadsheetApp.openById(spreadSheetId);

/***Methodtoreturnthespreadsheetidmanually.**@param{string}FileNameIDofthespreadsheettobeopened.*@return{string}returnthespreadsheetid.*/function_handle_mso_excel_get_google_spreadsheet_id(FileName){//UploadtheExcelfilesbeingopenedbytheAPItoGoogleDriveandconvert//themtoGoogleSheets.//DeterminethespreadsheetIDoftheGoogleSheetsfilecreated.//ImplementthismethodtoreturnthecorrespondingspreadsheetIDwhengiven//theoriginalfilepathasparameter.thrownewError('Please return the spreadsheet ID corresponding to filename: '+FileName);return'';}

As instructed by the comments in the sample above, you need to convert thetarget files to Google Sheets files on Google Drive.

The corresponding Google Spreadsheet IDs are bolded below:

• File #1:C:\Data\abc.xlsx becomeshttps://docs.google.com/spreadsheets/d/abc123Abc123Abc123abc
• File #2:C:\Data\abc.xlsx becomeshttps://docs.google.com/spreadsheets/d/xyz456Xyz456xYz456xyZ

Then, modify the code in the Apps Script function to open the files by ID, asshown below:

/***Methodtoreturnthespreadsheetidmanually.**@param{string}FileNameIDofthespreadsheettobeopened.*@return{string}returnthespreadsheetid.*/function_handle_mso_excel_get_google_spreadsheet_id(FileName){//UploadtheExcelfilesbeingopenedbytheAPItoGoogleDriveandconvert//themtoGoogleSheets.//DeterminethespreadsheetIDoftheGoogleSheetsfilecreated.//ImplementthismethodtoreturnthecorrespondingspreadsheetIDwhengiven//theoriginalfilepathasparameterif(Filename.indexOf("abc.xlsx")>=0){return"abc123Abc123Abc123abc";}elseif(Filename.indexOf("xyz.xlsx")>=0){return"xyz456Xyz456xYz456xyZ";}

Intentional error

Intentional errors are added to your converted code to mimic the error behaviorof your original VBA code. You don’t need to modify these errors.

Example of an intentional error

If you try to access an element beyond the bounds of an array in VBA, the codethrows an exception. In Apps Script, the code returns undefined.

To avoid unexpected results, the Macro Converter adds Apps Script code thatthrows an exception if you try to access elements beyond the bounds of an array.

This example is shown in the code below:

Dim arrarr = Array("apple", "orange")MsgBox arr(5)Will throw the following error:Subscript out of range

vararr;arr=["apple","orange"];Browser.msgBox(arr[5]);Willreturnthisvalueandnotthrowanerror:undefined

/*** Extend the regular JS array to support VB style indexing with a get method.* @returns{*} value at the index*/Array.prototype.get=function(){varcurr_res=this;for(vari=0;i<arguments.length;i++){if(!Array.isArray(curr_res)||curr_res.length<arguments[i]){thrownewError(‘ConvertedVBAError(IntentionalError):Subscriptoutofrange’);}curr_res=curr_res[arguments[i]];}returncurr_res;};vararr;arr=["apple", "orange"];Browser.msgBox(arr.get(5));

Related articles

• Macro Converter add-on overview
• Determine if VBA macros are compatible
• Convert VBA macros to Apps Script
• Address common issues
• Watch Macro Converter tutorials
• List of compatible VBA APIs