XL97: Error When Passing an Array Value Between Macros

Retired KB Content Disclaimer

View products that this article applies to.

This article was previously published under Q158746

SYMPTOMS

When you run a Visual Basic for Applications macro in Microsoft Excel 97, you may receive the following error message:

Run-time error '10':
This array is fixed or temporarily locked.

Back to the top

CAUSE

This behavior will occur if the following conditions are all true:

You create two macros, where one macro passes a variable to the other macro.

-and-
The variable that is passed to the other macro is an element of an array.

-and-
The second macro accepts the variable by reference, not by value.

-and-
The second macro uses the ReDim method to redimension the array.

This behavior is by design of Microsoft Excel 97.

NOTE: This behavior is a change from earlier versions of Microsoft Excel.

Back to the top

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs.
If you have limited programming experience, you may want to contact a Microsoft Certified Partner or Microsoft Advisory Services. For more information, visit these Microsoft Web sites:

Microsoft Certified Partners - https://partner.microsoft.com/global/30000104 (https://partner.microsoft.com/global/30000104)

Microsoft Advisory Services - http://support.microsoft.com/gp/advisoryservice (http://support.microsoft.com/gp/advisoryservice)

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS (http://support.microsoft.com/default.aspx?scid=fh;en-us;cntactms) The following workarounds assume that you created a Visual Basic module with the following code:

   Dim MyArray() As Integer

   Sub Macro1()     'The first macro
       ReDim MyArray(10)
       Macro2 MyArray(1)
   End Sub

   Sub Macro2(X)    'The second macro
       ReDim MyArray(20)
   End Sub

The array MyArray is initialized at the module level; so, it is available to all macros within the module. The first macro redimensions the array, and then passes an element of the array to the second macro. The second macro refers to the element in the array by reference, not by value.

NOTE: If you run Macro1, you receive the error message.

To prevent the problem from occurring, use either of the following methods.

Back to the top

Method 1

In the second macro, use the keyword ByVal to force the macro to refer to the element in the array by value, not by reference. For example, change the second macro to the following:

   Sub Macro2(ByVal X)    'Note the "ByVal" keyword.
       ReDim MyArray(20)
   End Sub

Back to the top

Method 2

Use an intermediate variable to pass the value from one macro to the other. For example, change the first macro to the following:

   Sub Macro1()
       ReDim MyArray(10)
       Z = MyArray(1)     'Set the intermediate variable value.
       Macro2 Z           'Run the second macro and pass the value.
   End Sub

By using an intermediate variable to send the value to the second macro, you prevent the second macro from referring to locations within the array's memory. You can then redimension the array without receiving an error message.

Back to the top

MORE INFORMATION

When you pass a variable from one macro to another macro in Microsoft Excel, the actual value of the variable is not passed between macros. Instead, a reference to the value's location in memory is passed; this is called passing a value by reference. You can also pass a value by value by using the keyword ByVal.

If you pass an element of an array from one macro to another by reference, and then attempt to redimension the array by using the ReDim method, you receive the error message in this article. This behavior occurs because the array's size is locked when a macro refers to it. Only when the reference is released can the array be redimensioned.

Note that this limitation does not apply to earlier versions of Microsoft Excel.

Back to the top

APPLIES TO

Microsoft Excel 97 Standard Edition

Back to the top

kbdtacode kberrmsg kbprb kbprogramming KB158746

Back to the top

Retired KB Content Disclaimer

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Back to the top