Article ID: 182239
Article Last Modified on 9/11/2002
APPLIES TO
- Microsoft Excel 98 for Macintosh
This article was previously published under Q182239
SYMPTOMS
When you run a Visual Basic for Applications macro in Microsoft Excel 98
Macintosh Edition, you may receive the following error message:
Run-time error '10':
This array is fixed or temporarily locked
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.
WORKAROUND
Microsoft provides examples of Visual Basic for Applications procedures 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. The Visual Basic procedures in this article are
provided 'as is' and Microsoft does not guarantee that they can be used in
all situations. While Microsoft support professionals can help explain the
functionality of a particular macro, they will not modify these examples to
provide added functionality, nor will they help you construct macros to
meet your specific needs. If you have limited programming experience, you
may want to consult one of the Microsoft Solution Providers. Solution
Providers offer a wide range of fee-based services, including creating
custom macros. For more information about Microsoft Solution Providers,
call Microsoft Customer Information Service at (800) 426-9400.
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.
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
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.
STATUS
This behavior is by design of Microsoft Excel 98 Macintosh Edition.
NOTE: This behavior is a change from earlier versions of Microsoft Excel.
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.
Additional query words: XL98
Keywords: kbprogramming kbprb kbdtacode KB182239