This article explain Error Number 10: "This array is fixed or temporarily locked" and its resolution. This error usually occurs when VBA Code is not properly written.There are many circumstances when user would like to Re-dimension existing array variable to accomplish its purpose. But that could be done in numerous ways .This article explains a very simple a unique concept to illustrate this methodology.
In order to implement this concept first of all we have to create a form named "frmMain" as shown in figure fig 1.1
As shown in figure above form will contain a Button with caption Enter. When User Click on this button a VBA code that is associated with this button will executes and perform its corresponding action. As a result following message box will appears. Shown in figure 1.2
Now in next step above shown values in message box will be Re-dimensioned and an error will fire. All above process has been coded below.
In order to resolve this error array variable needs to be declared dynamically rather than statically. So in VBA code we need to replace array declaration with appropriate code i.e. array variable needs to be Re-Dimensioned using ReDim.
VBA associated with On Click Event of Button:-
Private Sub btnCmd_Click() Dim sm_Arr(25) As Variant ' Declare array as dynamic for removing error i.e. Use ReDim inplace of Dim Dim strValue As String For i = 0 To UBound(sm_Arr) sm_Arr(i) = i strValue = strValue & i & "," Next i MsgBox "Value is :" & vbNewLine & strValue, vbInformation, "Number's" another_sub sm_Arr() End Sub Public Sub another_sub(sm_Value() As Variant) ReDim sm_Value(10) For i = 0 To UBound(sm_Value) sm_Value(i) = i strValue = strValue & i & "," Next i MsgBox "Value is :" & vbNewLine & strValue, vbInformation, "Value's Re-Dimensioned" End Sub
It is advised that the information provided in the article should not be used for any kind formal or production programming purposes as content of the article may not be complete or well tested. ERP Makers will not be responsible for any kind of damage (monetary, time, personal or any other type) which may take place because of the usage of the content in the article.