Fix errors in your converted code

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

Use this guide to understand the Apps Script files (GS files) added to your project, 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 the library.gs file.

The library.gs file defines functions and constants that were used in your VBA code that don’t exist in Apps Script. This helps the new Apps Script code better resemble your VBA code. Additionally, you don’t need to repeat definitions every time functions or constants from the library.gs file are used.

`Unimplemented_constructs.gs`

The unimplemented_constructs.gs file addresses constructs or APIs that couldn't be converted by the Macro Converter. You likely need to modify this file to make your code work as intended.

Example: `Window.Activate()`

The following is an example of an unsupported API called Window.Activate() . The Macro Converter creates a new Apps Script function with a similar name and defines it in the unimplemented_constructs.gs file. Since the VBA function isn’t supported, the new Apps Script function throws an exception.

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

If you find a workaround to recreate the behavior of the original API, you only need to update the definition of the function in the unimplemented_constructs.gs file. Once the function is defined there, it applies everywhere the function appears 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 the unimplemented_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`

The variant_resolutions.gs file is added to your Apps Script project if an object’s type can’t be determined. This can happen for multiple reasons, such as an API having multiple return types or the object is declared as a variant itself.

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>() function to manually declare the object type. See Unsupported object type .

Example: `name()`

Many object types in VBA define a name() API. Usually, the Apps Script equivalent is getName() , but not for every object type. Multiple alternative cases can occur:

The object's equivalent API is called something different than getName() .
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 new function called __handle_resolve_name in the variant_resolutions.gs file.

Here’s the example in code:

Original VBA code

a = Selection.name

In this case, the API name() is called on the current selection. The selection could be a Sheet object or a Shape object. If it’s a Sheet object, the translation is getName() , but if it’s a Shape object, there is no equivalent in Apps Script.

Converted Apps Script code, added in-line

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

The __handle_resolve_name() function below is added to the variant_resolution.gs file to solve for different object types. The function checks the object type, then uses getName() if it’s supported, or throws an error if getName() isn’t supported.

Function definition added to the variant_resolution.gs file

 function 
  
 __handle_resolve_name 
 ( 
 ExecutionContext 
 , 
  
 CallingObject 
 , 
  
 params_map 
 ) 
  
 { 
  
 var 
  
 found_api_variant 
  
 = 
  
 false 
 ; 
  
 var 
  
 return_value 
 ; 
  
 if 
  
 ( 
 String 
 ( 
 CallingObject 
 ) 
  
 == 
  
 "Sheet" 
 ) 
  
 { 
  
 if 
  
 ( 
 ! 
 ExecutionContext 
 . 
 isLhs 
 ) 
  
 { 
  
 return_value 
  
 = 
  
 CallingObject 
 . 
 getName 
 (); 
  
 found_api_variant 
  
 = 
  
 true 
 ; 
  
 } 
  
 } 
  
 if 
  
 ( 
 CallingObject 
  
 instanceof 
  
 ChartInSheet 
 ) 
  
 { 
  
 if 
  
 ( 
 ! 
 ExecutionContext 
 . 
 isLhs 
 ) 
  
 { 
  
 return_value 
  
 = 
  
 CallingObject 
 . 
 getName 
 (); 
  
 found_api_variant 
  
 = 
  
 true 
 ; 
  
 } 
  
 } 
  
 if 
  
 ( 
 ! 
 found_api_variant 
 ) 
  
 { 
  
 ThrowException 
 ( 
 "API .name not supported yet." 
 
 ); 
  
 } 
  
 return 
  
 return_value 
 ; 
 }

Find errors

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

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

 _api_windows_active 
  
 ( 
 unimplemented_constructs 
 : 
 2 
 : 
 3 
 )

This means the error is located in the unimplemented_constructs.gs file at line 2, character 3.

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

unimplemented_constructs:2 (_api_windows_active)

This means the error is located in the unimplemented_constructs.gs file at line 2.

Error Types

You can fix most of the errors you run into in the unimplemented_constructs.gs and variant_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

An unimplemented API is an API that the Macro Converter can’t convert from VBA to Apps Script and there isn’t a known workaround for the API.

Unimplemented APIs are usually added as empty functions—sometimes with empty signatures—to the unimplemented_constructs.gs file. If the object type couldn’t be determined, the unimplemented API might be added to the variant_resolution.gs file, instead.

In the compatibility report you generated before the conversion, this API is labeled as Needs 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:

Open the converted Apps Script code at the location of the error. See Find errors .
Above the function, read the comment that was added. In some cases, the comment suggests how to implement the API in Apps Script.
If you can’t find a way to implement the API in Apps Script, consider removing it from your code.
If you can’t find a workaround or remove this API from your code and your macro 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 for Chart.Protect , an API that doesn’t exist in Apps Script.
An unknown object type : Shows how to handle an object type that’s a variable, and how to implement an unsupported object type that can be recreated in Apps Script.

Example 1: No equivalent Apps Script or unknown API

In this example, Chart.Protect wasn’t automatically converted because there isn’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 
 ( 
 ' 
 API 
  
 chart 
 . 
 protect 
  
 not 
  
 supported 
  
 yet 
 . 
 ' 
 ); 
 }

Even though you can’t protect a chart, you can protect the data range of the chart so that the data can’t be changed.

A sample implementation of protecting the range is shown below:

 /** 
 * 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 
 ) 
  
 { 
 var 
  
 ranges 
  
 = 
  
 CallingObject 
 . 
 getChart 
 (). 
 getRanges 
 (); 
 for 
  
 ( 
 var 
  
 i 
  
 = 
  
 0 
 ; 
  
 i 
  
 < 
  
 ranges 
 . 
 length 
 ; 
  
 i 
 ++ 
 ) 
  
 { 
  
 // 
  
 Note 
  
 that 
  
 this 
  
 does 
  
 not 
  
 lock 
  
 the 
  
 range 
  
 for 
  
 the 
  
 document 
  
 owner 
 . 
  
 ranges 
 [ 
 i 
 ] 
 . 
 protect 
 (); 
 } 
 }

Example 2: Unsupported object type

When the object type is unknown, the unimplemented API error is added to the variant_resolution.gs file. The following example expands on the VBA name() API example above. See variant_resolution.gs .

In this example, you’ll learn:

How the name() API is converted to a new function in the variant_resolution.gs file .
How the new function is called in the converted code .
How to create a workaround for CommandBar , an unsupported object type, in Apps Script .

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

 function 
  
 __handle_resolve_name 
 ( 
 ExecutionContext 
 , 
  
 CallingObject 
 , 
  
 params_map 
 ) 
  
 { 
  
 var 
  
 found_api_variant 
  
 = 
  
 false 
 ; 
  
 var 
  
 return_value 
 ; 
  
 if 
  
 ( 
 String 
 ( 
 CallingObject 
 ) 
  
 == 
  
 "Sheet" 
 ) 
  
 { 
  
 if 
  
 ( 
 ! 
 ExecutionContext 
 . 
 isLhs 
 ) 
  
 { 
  
 return_value 
  
 = 
  
 CallingObject 
 . 
 getName 
 (); 
  
 found_api_variant 
  
 = 
  
 true 
 ; 
  
 } 
  
 } 
  
 if 
  
 ( 
 CallingObject 
  
 instanceof 
  
 ChartInSheet 
 ) 
  
 { 
  
 if 
  
 ( 
 ! 
 ExecutionContext 
 . 
 isLhs 
 ) 
  
 { 
  
 return_value 
  
 = 
  
 CallingObject 
 . 
 getName 
 (); 
  
 found_api_variant 
  
 = 
  
 true 
 ; 
  
 } 
  
 } 
  
 if 
  
 ( 
 ! 
 found_api_variant 
 ) 
  
 { 
  
 ThrowException 
 ( 
 'API .name not supported yet.' 
 
 ); 
  
 } 
  
 return 
  
 return_value 
 ; 
 }

2. Suppose the VBA code defines a PrintName() function that calls the name() API. The VBA code is shown below:

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

Since `name()` is called on an object that’s a variable, the converted code doesn’t know the object type at the time of conversion. The converted Apps Script code will call the `__handle_resolve_name` function:

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

3. Suppose your VBA code calls the PrintName() function on the object type CommandBar . The VBA code is shown below:

PrintName Application.CommandBars.item("Standard")

CommandBar isn’t supported in Apps Script and as a result, the two methods used in the VBA code above are also not supported.

Application.CommandBars() : In VBA, this returns a list of all CommandBar objects.
CommandBars.item() : In VBA, this returns a specific CommandBar object.

Because this object type isn’t supported in Apps Script, the converted code creates the following functions in the `unimplemented_constructs.gs` file that you need to define.

_api_application_commandbars()
_api_commandbars_item()

The functions are called in the converted code as shown below:

 PrintName 
 ( 
 _api_commandbars_item 
 ( 
 _api_application_commandbars 
 (), 
  
 "Standard" 
 ))) 
 Here 
 ’ 
 s 
  
 how 
  
 the 
  
 new 
  
 functions 
  
 are 
  
 added 
  
 to 
  
 the 
  
 unimplemented_construct 
 . 
 gs 
  
 file 
 : 
 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 of CommandBars and a new collection of CommandBars similar to what exists in VBA.

3.2 Add a getName() method for the new object type.

Steps 3.1 and 3.2 are shown in the code below. Menu objects are created as a new object type that mimics the behavior of CommandBars .

 // 
  
 Our 
  
 Implementation 
  
 of 
  
 CommandBar 
  
 using 
  
 Menu 
  
 objects 
 . 
 function 
  
 CommandBar 
 ( 
 name 
 ) 
  
 { 
  
 this 
 . 
 name 
  
 = 
  
 name 
 ; 
  
 // 
  
 Create 
  
 a 
  
 menu 
  
 object 
  
 to 
  
 represent 
  
 the 
  
 commandbar 
 . 
  
 this 
 . 
 menu 
  
 = 
  
 SpreadsheetApp 
 . 
 getUi 
 (). 
 createMenu 
 ( 
 name 
 ); 
  
 // 
  
 Create 
  
 methods 
  
 for 
  
 retrieving 
  
 or 
  
 updating 
  
 the 
  
 name 
  
 of 
  
 the 
  
 object 
  
 this 
 . 
 getName 
  
 = 
  
 function 
 () 
  
 { 
  
 return 
  
 this 
 . 
 name 
 ; 
  
 } 
 ; 
  
 this 
 . 
 updateName 
  
 = 
  
 function 
 ( 
 name 
 ) 
  
 { 
  
 this 
 . 
 name 
  
 = 
  
 name 
 ; 
  
 } 
 ; 
  
 // 
  
 ======================================================================== 
  
 // 
  
 Implement 
  
 other 
  
 methods 
  
 of 
  
 CommandBar 
  
 objects 
  
 that 
  
 are 
  
 used 
  
 in 
  
 the 
  
 script 
 . 
  
 // 
  
 ===================================================================== 
  
 return 
  
 this 
 ; 
 } 
 // 
  
 Our 
  
 implementation 
  
 of 
  
 the 
  
 collection 
  
 of 
  
 CommandBars 
  
 that 
  
 exists 
  
 in 
  
 VBA 
 function 
  
 CommandBars 
 () 
  
 { 
  
 this 
 . 
 commandBars 
  
 = 
  
 [] 
 ; 
  
 this 
 . 
 getCommandBar 
  
 = 
  
 function 
 ( 
 name 
 ) 
  
 { 
  
 for 
  
 ( 
 var 
  
 i 
  
 = 
  
 0 
 ; 
  
 i 
  
 < 
  
 this 
 . 
 commandBars 
 . 
 length 
 ; 
  
 i 
 ++ 
 ) 
  
 { 
  
 if 
  
 ( 
 ! 
 this 
 . 
 commandBars 
 [ 
 i 
 ] 
 . 
 getName 
 () 
  
 == 
  
 name 
 ) 
  
 { 
  
 return 
  
 this 
 . 
 commandBars 
 [ 
 i 
 ] 
 ; 
  
 } 
  
 } 
  
 // 
  
 No 
  
 commandBar 
  
 with 
  
 the 
  
 name 
  
 exists 
 , 
  
 create 
  
 a 
  
 new 
  
 one 
  
 and 
  
 return 
 . 
  
 var 
  
 commandBar 
  
 = 
  
 new 
  
 CommandBar 
 ( 
 name 
 ); 
  
 this 
 . 
 commandBars 
 . 
 push 
 ( 
 commandBar 
 ); 
  
 return 
  
 commandBar 
 ; 
  
 } 
 ; 
  
 return 
  
 this 
 ; 
 } 
 // 
  
 Create 
  
 a 
  
 global 
  
 object 
  
 that 
  
 represents 
  
 CommandBars 
  
 collection 
 . 
 var 
  
 GlobalCommandBars 
  
 = 
  
 new 
  
 CommandBars 
 ();

3.3 Modify the __handle_resolve_name function in the variant_resolution.gs file to handle the new object type. Add a section to the function, as shown below:

 function 
  
 __handle_resolve_name 
 ( 
 ExecutionContext 
 , 
  
 CallingObject 
 , 
  
 params_map 
 ) 
  
 { 
  
 var 
  
 found_api_variant 
  
 = 
  
 false 
 ; 
  
 var 
  
 return_value 
 ; 
  
 if 
  
 ( 
 String 
 ( 
 CallingObject 
 ) 
  
 == 
  
 "Sheet" 
 ) 
  
 { 
  
 if 
  
 ( 
 ! 
 ExecutionContext 
 . 
 isLhs 
 ) 
  
 { 
  
 return_value 
  
 = 
  
 CallingObject 
 . 
 getName 
 (); 
  
 found_api_variant 
  
 = 
  
 true 
 ; 
  
 } 
  
 } 
  
 if 
  
 ( 
 CallingObject 
  
 instanceof 
  
 ChartInSheet 
 ) 
  
 { 
  
 if 
  
 ( 
 ! 
 ExecutionContext 
 . 
 isLhs 
 ) 
  
 { 
  
 return_value 
  
 = 
  
 CallingObject 
 . 
 getName 
 (); 
  
 found_api_variant 
  
 = 
  
 true 
 ; 
  
 } 
  
 } 
  
 // 
  
 New 
  
 section 
  
 added 
  
 below 
  
 // 
  
 ======================================================================== 
  
 if 
  
 ( 
 CallingObject 
  
 instanceof 
  
 CommandBar 
 ) 
  
 { 
  
 objectExtend 
 ( 
 params_map 
 , 
  
 { 
 VALUETOSET 
 : 
  
 params_map 
 . 
 param0 
 }); 
  
 if 
  
 ( 
 ExecutionContext 
 . 
 isLhs 
 ) 
  
 { 
  
 // 
  
 Call 
  
 the 
  
 setter 
  
 method 
 . 
  
 CallingObject 
 . 
 updateName 
 ( 
 params_map 
 . 
 VALUETOSET 
 ); 
  
 found_api_variant 
  
 = 
  
 true 
 ; 
  
 } 
  
 else 
  
 { 
  
 // 
  
 Getter 
  
 is 
  
 called 
 , 
  
 return 
  
 the 
  
 commandbar 
  
 name 
 , 
  
 return_value 
  
 = 
  
 CallingObject 
 . 
 getName 
 (); 
  
 found_api_variant 
  
 = 
  
 true 
 ; 
  
 } 
  
 } 
  
 // 
  
 ======================================================================== 
  
 // 
  
 New 
  
 section 
  
 added 
  
 above 
  
 if 
  
 ( 
 ! 
 found_api_variant 
 ) 
  
 { 
  
 ThrowException 
 ( 
 'API .name not supported yet.' 
 
 ); 
  
 } 
  
 return 
  
 return_value 
 ; 
 }

3.4 Define the two functions created in the unimplemented_constructs.gs file ( _api_application_commandbars() , _api_commandbars_item() ). This step makes sure 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 
 ) 
  
 { 
  
 return 
  
 GlobalCommandBars 
 ; 
 } 
 function 
  
 _api_commandbars_item 
 ( 
 CallingObject 
 , 
  
 index 
 ) 
  
 { 
  
 return 
  
 CallingObject 
 . 
 getCommandBar 
 ( 
 index 
 ); 
 }

Unimplemented language constructs

A construct is an element of the code language that controls execution flow or data display. For example, loops, labels, events, and gotos. Here’s a list of all VBA constructs .

Constructs that the Macro Converter can’t convert are considered unimplemented language constructs .

Where the Macro Converter determines that an unimplemented language construct exists, it inserts a TODO comment.

The following VBA constructs aren’t supported:

Fix unimplemented language construct errors

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

Examples of unimplemented language construct errors

One of the most common unimplemented language constructs is a GoTo statement. You can replace some VBA GoTo statements with loops. Below are two examples of using loops instead of GoTo statements.

Example 1: Replace `GoTo` with `While Loop`

Original VBA code

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

Equivalent Apps Script code

 function 
  
 test 
 () 
  
 { 
  
 var 
  
 a 
  
 = 
  
 0 
 ; 
  
 start 
 : 
  
 do 
  
 { 
  
 console 
 . 
 log 
 ( 
 a 
 ); 
  
 while 
  
 ( 
 a 
  
 < 
  
 100 
 ) 
  
 { 
  
 a 
  
 = 
  
 a 
  
 + 
  
 1 
 ; 
  
 if 
  
 ( 
 a 
  
 % 
  
 3 
  
 == 
  
 0 
 ) 
  
 { 
  
 continue 
  
 start 
 ; 
  
 } 
  
 } 
  
 break 
  
 start 
 ; 
  
 } 
  
 while 
  
 ( 
 true 
 ); 
 }

Example 2: Replace GoTo with For Loop

Original VBA code

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 a
End Sub

Equivalent Apps Script code

 function 
  
 test 
 () 
  
 { 
  
 var 
  
 a 
  
 = 
  
 0 
 ; 
  
 endLoop 
 : 
  
 for 
  
 ( 
 var 
  
 i 
  
 = 
  
 1 
 ; 
  
 i 
  
 <= 
  
 100 
 ; 
  
 i 
 ++ 
 ) 
  
 { 
  
 for 
  
 ( 
 var 
  
 j 
  
 = 
  
 0 
 ; 
  
 j 
  
 <= 
 10 
 ; 
  
 j 
 ++ 
 ) 
  
 { 
  
 If 
  
 ( 
 i 
  
 + 
  
 j 
  
 > 
  
 50 
 ) 
  
 { 
  
 break 
  
 endLoop 
 ; 
  
 } 
  
 } 
  
 } 
  
 Browser 
 . 
 msgBox 
 ( 
 a 
 ); 
 } 
  
 break 
  
 start 
 ; 
  
 } 
  
 while 
  
 ( 
 true 
 ); 
 }

Partially supported API

For Partially supported APIs , some input parameters are supported in Apps Script and some aren’t.

For example, the VBA API legend_position is used to define the legend in an Excel 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. The following values are not supported:

xlLegendPositionCorner
xlLegendPositionCustom

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

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

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

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

 /** 
 * 
  
 Throw 
  
 error 
  
 message 
  
 for 
  
 unsupported 
  
 legend 
  
 position 
 . 
 * 
  
 The 
  
 VBA 
  
 API 
  
 Legend 
 . 
 Position 
  
 which 
  
 can 
  
 take 
  
 values 
  
 xlLegendPositionTop 
 , 
 * 
  
 xlLegendPositionLeft 
 , 
  
 xlLegendPositionBottom 
 , 
  
 xlLegendPositionRight 
 , 
 * 
  
 xlLegendPositionCorner 
 , 
  
 xlLegendPositionCustom 
 . 
  
 It 
  
 is 
  
 partially 
  
 supported 
  
 in 
 * 
  
 Apps 
  
 Scripts 
  
 that 
  
 supports 
  
 only 
  
 a 
  
 subset 
  
 of 
  
 the 
  
 values 
  
 ( 
 does 
  
 not 
  
 support 
 * 
  
 xlLegendPositionCorner 
  
 and 
  
 xlLegendPositionCustom 
 ). 
 * 
  
 @ 
 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. 
 throw 
  
 new 
  
 Error 
 ( 
  
 'Google Sheets does not support legend position: ' 
  
 + 
  
 position 
 ); 
 }

Fix partially supported API errors

Define the _handle_<API_name>_error function to replace the unsupported values with an acceptable workaround for your needs.

Open the converted Apps Script code at the location of the error. See Find errors .
Read the comment above the function to understand which values are supported and which aren’t.
For the unsupported values, determine which supported values can act as a suitable replacement.
Update the function _handle_<API_name>_error to return a supported value instead.
If you can’t find a way to replace the unsupported value, you can’t convert this macro.

Example of a partially supported API error

The following example expands on the VBA API legend_position mentioned above. See Partially 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 the unimplemented_constructs.gs file:

 /** 
 * 
  
 Throw 
  
 error 
  
 message 
  
 for 
  
 unsupported 
  
 legend 
  
 position 
 . 
 * 
  
 The 
  
 VBA 
  
 API 
  
 Legend 
 . 
 Position 
  
 which 
  
 can 
  
 take 
  
 values 
  
 xlLegendPositionTop 
 , 
 * 
  
 xlLegendPositionLeft 
 , 
  
 xlLegendPositionBottom 
 , 
  
 xlLegendPositionRight 
 , 
 * 
  
 xlLegendPositionCorner 
 , 
  
 xlLegendPositionCustom 
 . 
  
 It 
  
 is 
  
 partially 
  
 supported 
  
 in 
 * 
  
 Apps 
  
 Scripts 
  
 that 
  
 supports 
  
 only 
  
 a 
  
 subset 
  
 of 
  
 the 
  
 values 
  
 ( 
 does 
  
 not 
  
 support 
 * 
  
 xlLegendPositionCorner 
  
 and 
  
 xlLegendPositionCustom 
 ). 
 * 
  
 @ 
 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. 
 throw 
  
 new 
  
 Error 
 ( 
  
 '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 of API is labeled as Supported 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. See Find 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 fix them:

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

Example 1: `Autocorrect.Addreplacement`

In the following example, the VBA API Autocorrect.Addreplacement can be converted, but it needs a workaround. The Macro Converter suggests how to implement 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 
 ( 
 ' 
 API 
  
 autocorrect 
 . 
 addreplacement 
  
 not 
  
 supported 
  
 yet 
 . 
 ' 
 ); 
 }

The implementation of the Autocorrect.Addreplacement API is shown below:

 var 
  
 AUTO_CORRECTIONS 
  
 = 
  
 "AUTO_CORRECTIONS" 
 ; 
 // 
  
 Need 
  
 to 
  
 get 
  
 the 
  
 autocorrections 
  
 set 
  
 in 
  
 previous 
  
 sessions 
  
 and 
  
 use 
  
 them 
 . 
 var 
  
 savedAutoCorrections 
  
 = 
  
 PropertiesService 
 . 
 getDocumentProperties 
 (). 
 getProperty 
 ( 
 AUTO_CORRECTIONS 
 ); 
 var 
  
 autoCorrections 
  
 = 
  
 savedAutoCorrections 
  
 ? 
  
 JSON 
 . 
 parse 
 ( 
 savedAutoCorrections 
 ) 
  
 : 
  
 {} 
 ; 
 function 
  
 onEdit 
 ( 
 e 
 ) 
  
 { 
 autoCorrect 
 ( 
 e 
 . 
 range 
 ); 
 } 
 function 
  
 autoCorrect 
 ( 
 range 
 ) 
  
 { 
 for 
  
 ( 
 key 
  
 in 
  
 autoCorrections 
 ) 
  
 { 
 // 
  
 Replace 
  
 each 
  
 word 
  
 that 
  
 needs 
  
 to 
  
 be 
  
 auto 
 - 
 corrected 
  
 with 
  
 their 
  
 replacements 
 . 
 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 
 ; 
 // 
  
 Store 
  
 the 
  
 updated 
  
 autoCorrections 
  
 in 
  
 the 
  
 properties 
  
 so 
  
 that 
  
 future 
  
 executions 
  
 use 
  
 the 
  
 correction 
 . 
 PropertiesService 
 . 
 getDocumentProperties 
 (). 
 setProperty 
 ( 
 AUTO_CORRECTIONS 
 , 
  
 JSON 
 . 
 stringify 
 ( 
 autoCorrections 
 )); 
 }

Example 2: `Workbook.open()`

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

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

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

The below shows how the Macro Converter replaces Workbook.open() with Apps Script everywhere Workbook.open() is used to open File 1:

 var 
  
 spreadSheetId 
  
 = 
  
 _handle_mso_excel_get_google_spreadsheet_id 
 ( 
 "C:\Data 
 \a 
 bc.xlsx" 
 ); 
 var 
  
 spreadSheet 
  
 = 
  
 SpreadsheetApp 
 . 
 openById 
 ( 
 spreadSheetId 
 );

The below error is added to the unimplemented_constructs.gs file in the Apps Script project:

 /** 
 * 
  
 Method 
  
 to 
  
 return 
  
 the 
  
 spreadsheet 
  
 id 
  
 manually 
 . 
 * 
 * 
  
 @ 
 param 
  
 { 
 string 
 } 
  
 FileName 
  
 ID 
  
 of 
  
 the 
  
 spreadsheet 
  
 to 
  
 be 
  
 opened 
 . 
 * 
  
 @ 
 return 
  
 { 
 string 
 } 
  
 return 
  
 the 
  
 spreadsheet 
  
 id 
 . 
 */ 
 function 
  
 _handle_mso_excel_get_google_spreadsheet_id 
 ( 
 FileName 
 ) 
  
 { 
  
 // 
  
 Upload 
  
 the 
  
 Excel 
  
 files 
  
 being 
  
 opened 
  
 by 
  
 the 
  
 API 
  
 to 
  
 Google 
  
 Drive 
  
 and 
  
 convert 
  
 // 
  
 them 
  
 to 
  
 Google 
  
 Sheets 
 . 
  
 // 
  
 Determine 
  
 the 
  
 spreadsheet 
  
 ID 
  
 of 
  
 the 
  
 Google 
  
 Sheets 
  
 file 
  
 created 
 . 
  
 // 
  
 Implement 
  
 this 
  
 method 
  
 to 
  
 return 
  
 the 
  
 corresponding 
  
 spreadsheet 
  
 ID 
  
 when 
  
 given 
  
 // 
 the 
  
 original 
  
 file 
  
 path 
  
 as 
  
 parameter 
 . 
  
 throw 
  
 new 
  
 Error 
 ( 
 'Please return the spreadsheet ID corresponding to filename: ' 
  
 + 
  
 FileName 
 ); 
  
 return 
  
 ' ' 
 
 ; 
 }

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

The corresponding Google Spreadsheet IDs are bolded below:

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

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

 /** 
 * 
  
 Method 
  
 to 
  
 return 
  
 the 
  
 spreadsheet 
  
 id 
  
 manually 
 . 
 * 
 * 
  
 @ 
 param 
  
 { 
 string 
 } 
  
 FileName 
  
 ID 
  
 of 
  
 the 
  
 spreadsheet 
  
 to 
  
 be 
  
 opened 
 . 
 * 
  
 @ 
 return 
  
 { 
 string 
 } 
  
 return 
  
 the 
  
 spreadsheet 
  
 id 
 . 
 */ 
 function 
  
 _handle_mso_excel_get_google_spreadsheet_id 
 ( 
 FileName 
 ) 
  
 { 
  
 // 
  
 Upload 
  
 the 
  
 Excel 
  
 files 
  
 being 
  
 opened 
  
 by 
  
 the 
  
 API 
  
 to 
  
 Google 
  
 Drive 
  
 and 
  
 convert 
  
 // 
 them 
  
 to 
  
 Google 
  
 Sheets 
 . 
  
 // 
  
 Determine 
  
 the 
  
 spreadsheet 
  
 ID 
  
 of 
  
 the 
  
 Google 
  
 Sheets 
  
 file 
  
 created 
 . 
  
 // 
  
 Implement 
  
 this 
  
 method 
  
 to 
  
 return 
  
 the 
  
 corresponding 
  
 spreadsheet 
  
 ID 
  
 when 
  
 given 
  
 // 
 the 
  
 original 
  
 file 
  
 path 
  
 as 
  
 parameter 
  
 if 
  
 ( 
 Filename 
 . 
 indexOf 
 ( 
 "abc.xlsx" 
 ) 
  
 >= 
  
 0 
 ) 
  
 { 
  
 return 
  
 "abc123Abc123Abc123abc" 
 ; 
  
 } 
  
 else 
  
 if 
  
 ( 
 Filename 
 . 
 indexOf 
 ( 
 "xyz.xlsx" 
 ) 
  
 >= 
  
 0 
 ) 
  
 { 
  
 return 
  
 "xyz456Xyz456xYz456xyZ" 
 ; 
  
 }

Intentional error

Intentional errors are added to your converted code to mimic the error behavior of 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 code throws an exception. In Apps Script, the code returns undefined.

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

This example is shown in the code below:

Original VBA code

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

Converted Apps Script code (before exception error is added)

 var 
  
 arr 
 ; 
 arr 
  
 = 
  
 [ 
 "apple" 
 , 
  
 "orange" 
 ]; 
 Browser 
 . 
 msgBox 
 ( 
 arr 
 [ 
 5 
 ]); 
 Will 
  
 return 
  
 this 
  
 value 
  
 and 
  
 not 
  
 throw 
  
 an 
  
 error 
 : 
 undefined

Apps Script code added to throw the exception error

 /** 
 * Extend the regular JS array to support VB style indexing with a get method. 
 * @returns{*} value at the index 
 */ 
 Array 
 . 
 prototype 
 . 
 get 
  
 = 
  
 function 
 () 
  
 { 
  
 var 
  
 curr_res 
  
 = 
  
 this 
 ; 
  
 for 
  
 ( 
 var 
  
 i 
  
 = 
  
 0 
 ; 
  
 i 
  
 < 
  
 arguments 
 . 
 length 
 ; 
  
 i 
 ++ 
 ) 
  
 { 
  
 if 
  
 ( 
 ! 
 Array 
 . 
 isArray 
 ( 
 curr_res 
 ) 
  
 || 
  
 curr_res 
 . 
 length 
  
 < 
  
 arguments 
 [ 
 i 
 ] 
 ) 
  
 { 
  
 throw 
  
 new 
  
 Error 
 ( 
 ‘ 
 Converted 
  
 VBA 
  
 Error 
  
 ( 
 Intentional 
  
 Error 
 ) 
 : 
  
 Subscript 
  
 out 
  
 of 
  
 range 
 ’ 
 ); 
  
 } 
  
 curr_res 
  
 = 
  
 curr_res 
 [ 
 arguments[i 
 ] 
 ] 
 ; 
  
 } 
  
 return 
  
 curr_res 
 ; 
 } 
 ; 
 var 
  
 arr 
 ; 
 arr 
  
 = 
  
 [ 
 "apple", "orange" 
 ] 
 ; 
 Browser 
 . 
 msgBox 
 ( 
 arr 
 . 
 get 
 ( 
 5 
 ));

Fix errors in your converted code Stay organized with collections Save and categorize content based on your preferences.

Understand Apps Script files added to your project

Library.gs

Unimplemented_constructs.gs

Example: Window.Activate()

Variant_resolutions.gs

Example: name()

Find errors

Error Types

Unimplemented API

Fix unimplemented API errors

Examples of unimplemented API errors

Example 1: No equivalent Apps Script or unknown API

Example 2: Unsupported object type

Unimplemented language constructs

Fix unimplemented language construct errors

Examples of unimplemented language construct errors

Example 1: Replace GoTo with While Loop

Example 2: Replace GoTo with For Loop

Partially supported API

Fix partially supported API errors

Example of a partially supported API error

Manual work needed

Fix manual work needed errors

Examples of Manual work needed errors

Example 1: Autocorrect.Addreplacement

Example 2: Workbook.open()

Intentional error

Example of an intentional error

Related articles

Fix errors in your converted code

`Library.gs`

`Unimplemented_constructs.gs`

Example: `Window.Activate()`

`Variant_resolutions.gs`

Example: `name()`

Example 1: Replace `GoTo` with `While Loop`

Example 1: `Autocorrect.Addreplacement`

Example 2: `Workbook.open()`