Attempting basic loading of an array: Function "cannot be assigned to an array", Sub "array is fixed or locked"

[OaklandJim]

Team Mr. Excel

This is related to a sub that I posted about earlier but the issue is different!

In a sub I need to do a basic filling of an array. A function I built was working but all of a sudden it does not. (I know, I know, I did SOMETHING...)

I tried writing a sub to do the job but that fails too.

When trying to use the function PlansListMed to fill then return the array I get an error message saying that Excel "cannot assign to an array." When trying to to use a sub to fill the array I get a run-time 10 error: array is fixed or temporarily locked.

What basic conept related to use of arrays am I failing to understand?

Code is below.

Jim

Sub FillPlanTypesMedical(ByVal prProviderCell As Range)

'   Array containing the list
    Dim avPlansList(1) As Variant

'   Holds csv list of plan types for the respective provider to specify
'   the list of plans for data validation.
    Dim sList As String
    
'   Used to access array elements.
    Dim iIndex As Long
    
'   Used to check for plans list array is empty.
    Dim bArrayIsEmpty As Boolean
    
    Dim sProvider As String
    
    Dim sState As String
    
    Dim sCounty As String
        
    sList = ""

    sProvider = prProviderCell.Value
    
    sState = [MedicalInputs].Range("State")

    sCounty = [MedicalInputs].Range("County")

'   --------------------------------------
'        Handle no provider selected
'   --------------------------------------

    sStepID = "2. Handle no provider selected"
    
    If prProviderCell.Cells(1).Value = "" _
     Then
        prProviderCell.Offset(0, 1).Value = ""
        prProviderCell.Offset(0, 1).Validation.Delete
        Application.EnableEvents = True
        Exit Sub
    End If
    
'   ----------------------------------------------------
'        Getting plans for the selected provider
'   ----------------------------------------------------
    sStepID = "3. Get plans for the selected provider."

'   Use function PlansListMed to gather the plans list (for a provider)
'   and put the array returned into the local array variable.

'    Get ERROR: Compile error cannot assign to an array
'    avPlansList = PlansListMed(prProviderCell.Value, sState, sCounty)
    
'   Use sub GetPlansListMed to gather the plans list (for a provider)
'   and put them into the local array variable.

'    Get ERROR: Run time error 10, the array is fixed or temporarily locked
'    in the sub called (see code below).
    Call GetPlansListMed(avPlansList, prProviderCell.Value, sState, sCounty)

Exit Sub

Function PlansListMed( _
    psProvider As String, _
    psState As String, _
    psCounty As String) _
As Variant

'   ----------------------
'       Error Handling
'   ----------------------
    Dim sSubName As String
    Dim sStepID As String

    sSubName = "PlansListMed"
    sStepID = "Declarations"
    On Error GoTo ErrHandler
'   ----------------------

    Dim vEnableStatus As Variant

    With Application
        vEnableStatus = .EnableEvents
        .EnableEvents = False
    End With

    Dim rProviders As Range

    Dim rPlans As Range

    Dim rCell As Range

    Dim iPlanIndex As Long

    Dim iArrayIndex As Long

    Dim asPlansList() As Variant

    Set rProviders = [Providers_MedPlans]

    Set rPlans = [PlanNames_MedPlans]

'   --------------------------------------------------
      sStepID = "2. Iterating Plans for State/County"
'   --------------------------------------------------
    For Each rCell In rProviders

        iPlanIndex = iPlanIndex + 1

'       Must be the correct provider, state and county combination. Note
'       that state column is two over from the provider column and the
'       county column is three over from the provider column.
        If rCell.Value = psProvider _
        And rCell.Value <> "" _
        And rCell.Value <> 0 _
        And rCell.Offset(0, 2) = psState _
        And rCell.Offset(0, 3) = psCounty _
         Then

'   --------------------------------------
      sStepID = "3. Add Element to Array"
'   --------------------------------------

            iArrayIndex = iArrayIndex + 1

            ReDim Preserve asPlansList(iArrayIndex)

            asPlansList(iArrayIndex) = rPlans.Cells(iPlanIndex)

        End If

    Next rCell

    PlansListMed = asPlansList()

    Application.EnableEvents = vEnableStatus

Exit Function

ErrHandler:

Application.EnableEvents = vEnableStatus

Call ErrorMessage(Err.Number, Err.Description, sSubName, sStepID)

End Function

Option Explicit

Sub GetPlansListMed( _
    pavPlansArray As Variant, _
    psProvider As String, _
    psState As String, _
    psCounty As String)
    
'   ----------------------
'       Error Handling
'   ----------------------
    Dim sSubName As String
    Dim sStepID As String
    
    sSubName = "PlansListMed"
    sStepID = "Declarations"
    On Error GoTo ErrHandler
'   ----------------------
    
    Dim vEnableStatus As Variant
    
    With Application
        vEnableStatus = .EnableEvents
        .EnableEvents = False
    End With

    Dim rProviders As Range

    Dim rPlans As Range

    Dim rCell As Range

    Dim iPlanIndex As Long

    Dim iArrayIndex As Long

    Set rProviders = [Providers_MedPlans]

    Set rPlans = [PlanNames_MedPlans]
    
'   --------------------------------------------------
      sStepID = "2. Iterating Plans for State/County"
'   --------------------------------------------------
    For Each rCell In rProviders

        iPlanIndex = iPlanIndex + 1

'       Must be the correct provider, state and county combination. Note
'       that state column is two over from the provider column and the
'       county column is three over from the provider column.
        If rCell.Value = psProvider _
        And rCell.Value <> "" _
        And rCell.Value <> 0 _
        And rCell.Offset(0, 2) = psState _
        And rCell.Offset(0, 3) = psCounty _
         Then

'   --------------------------------------
      sStepID = "3. Add Element to Array"
'   --------------------------------------
                  
            iArrayIndex = iArrayIndex + 1

'            Error: array is fixed or temporarily locked
            ReDim Preserve pavPlansArray(iArrayIndex)

            pavPlansArray(iArrayIndex) = rPlans.Cells(iPlanIndex)

        End If

    Next rCell
    
    Application.EnableEvents = vEnableStatus

Exit Sub

ErrHandler:

Application.EnableEvents = True

Call ErrorMessage(Err.Number, Err.Description, sSubName, sStepID)

End Sub

 

[6StringJazzer]

It would be very helpful to us which line of code is giving you the error. Otherwise we have to go fishing. I have not gone line-by-line through everything you are doing, but this popped out at me.

    PlansListMed = asPlansList()

This syntax is wrong. Do not use parentheses here. VBA will try to find a function called asPlansList but it doesn't exist. Removing the parentheses may solve your problem.

You are returning a Variant for this function, which is fine:

Function PlansListMed( _
    psProvider As String, _
    psState As String, _
    psCounty As String) _
As Variant

But you are assigning a Variant array to it:

    Dim asPlansList() As Variant

I am thinking that should work but I'm not 100% sure without a test. To be safe, remove the parentheses from this declaration. You can still treat it as an array, so you don't have to change other code.

 

[6StringJazzer]

Really beautiful code, by the way.

 

[OaklandJim]

6StringJazzer

How lame. Later in the sub I posted part of above (FillPlanTypesMedical) there is another/different issue related to detecting an empty array.

So, for whatever reason, when declaring the array I stupidly declared it with one element

avPlansList(1)

so I could attempt to debug the other problem (i was grapsing at straws). So of course when I try to fill the array with my sub the sub chokes because the array, indeed, is fixed. Like I said, lame.

Your post helped as it got me thinking about the issue! Thank you for taking the time to look at this.

Jim

 

[Alex Blakenburg]

To be safe, remove the parentheses from this declaration. You can still treat it as an array, so you don't have to change other code.

That will break my first suggestion in the parallel thread below.
The Err.Clear method should still work.
PS: I have logged off for the night.

Need assistance with detecting an empty variant array.

Team Mr. Excel In a sub I have a variant array. The sub is called by the Change event when the user selects an insurance provider from a validation dropdown list. That sub calls a function that fills the variant array with medical insurance plan names (plans) offered by the selected provider. A...

www.mrexcel.com