0

I am unable to iterate completely through an excel VBA Collection of custom objects. I receive an Invalid procedure call or argument error on the third object.

When examining each object and stepping line-by-line through the procedure, it works on the first two objects, then fails on the third. I've tried executing the code alone, calling the third object instance manually, with no error.

Why would the procedure execute the calculation and set the first two object instance's property correctly, then fail the third time?

I'm new to VBA - what obvious thing am I missing?

The Procedure:

Sub calculateMaintenancePlan()
  'Calculates next x maintenance cycles for a number aircraft
  'Assumes the spreadsheet defines a named range containing the following columns:
  '1                               2                              3                       4                                    5                        6
  'Tail Number      Current Hours   Next Cycle  Next Cycle DUE     In Heavy        Week Id Cycle Start
  '6004                      11265.0             4                      11333.7                       FALSE              [Integer or -10 if not in heavy]

  'debugging/utility variables
  Dim temp As Variant
  Dim i As Integer, j As Integer

  'Assumes the spreadsheet defines a named range containing the following columns:
  'Cycle ID   Cycle Type      Duration    Text ID
  '1                  200 hour           1                    HMT1

  ReDim mx_cycles(1 To Worksheets("Matrix Inputs").Range("maintenance_cycles").Rows.Count, 1 To 3)
  For i = 1 To Worksheets("Matrix Inputs").Range("maintenance_cycles").Rows.Count
        mx_cycles(i, 1) = Worksheets("Matrix Inputs").Range("maintenance_cycles").Cells(i, 2)
        mx_cycles(i, 2) = CInt(Worksheets("Matrix Inputs").Range("maintenance_cycles").Cells(i, 3))
        mx_cycles(i, 3) = Worksheets("Matrix Inputs").Range("maintenance_cycles").Cells(i, 4)
        'Debug.Print i & " : " & mx_cycles(i, 1) & " : " & mx_cycles(i, 2) & " : " & mx_cycles(i, 3)
  Next i

  'an array containing each aircraft tail number, assume spreadsheet
  'contains named range called "aircraft"
  Dim aircraft As Collection, s_Aircraft As Collection

  Dim acft As c_Aircraft
  Set aircraft = New Collection
  Set s_Aircraft = New Collection

  'Set the Collection size to the total number of aircraft on station
        'and create a c_Aircraft instance representing each airframe

  For i = 1 To Range("aircraft").Count
        Set acft = New c_Aircraft
        acft.init_aircraft Worksheets("Matrix Inputs").Range("inputs"), i, mx_cycles
        aircraft.Add acft, CStr(acft.tailNumber)
  Next i

  'Sort the aircraft
  Set s_Aircraft = sortedAircraft(aircraft)
End Sub

'Sort a Collection of c_aircraft objects
Private Function sortedAircraft(unsortedAircraft As Collection) As Collection
    Set sortedAircraft = New Collection
    Dim acft As c_Aircraft
    Dim temp_acft As c_Aircraft
    Dim i As Long, j As Long
    Dim next_acft_cycle_start_week_id As Integer
    Dim previous_acft As String
    Dim t_tailNum
    'copy the Collection to a new collection
    For Each acft In unsortedAircraft
        sortedAircraft.Add acft
    Next acft

    'Sort the aircraft
    For i = 1 To sortedAircraft.Count
        For j = i + 1 To sortedAircraft.Count
            If sortedAircraft.Item(i).hoursToDUE > sortedAircraft.Item(j).hoursToDUE Then
                Set temp_acft = sortedAircraft.Item(j)
                sortedAircraft.Remove j
                t_tailNum = CStr(temp_acft.tailNumber)
                sortedAircraft.Add temp_acft, t_tailNum, i
            End If
        Next j
    Next i

    previous_acft = CStr(sortedAircraft.Item(1).tailNumber)

    For Each acft In sortedAircraft
        If acft.inHeavy = False Then
            '******* FAILS here on the third item of six items in the collection
            Debug.Print sortedAircraft.Item(previous_acft).weekIdCycleStart + sortedAircraft.Item(previous_acft).nextCycleDuration + 1
            previous_acft = CStr(acft.tailNumber)
        Else
            previous_acft = CStr(acft.tailNumber)
        End If
    Next acft
End Function

The Object: Represents an aircraft with various properties

Option Explicit

'Class c_Aircraft
'Requires call to init_aircraft() with the Range and "row" of the aircraft instance to be created.
'Assumes the Range contains the following columns:
'1                          2                              3                     4                                    5                        6
'Tail Number Current Hours   Next Cycle  Next Cycle DUE     In Heavy        Week Id Cycle Start
'6004                   11265.0             4                      11333.7                       FALSE              0

'Attributes
Private p_tailNumber As Long
Private p_tailNumStr As String

Private p_initialAircraftHours As Double
Private p_currentAircraftHours As Double

Private p_initialNextCycleType As Integer
Private p_nextCycleType As Integer

Private p_initialNextCycleDuration As Integer
Private p_nextCycleDuration As Integer

Private p_initialHoursNextCycleDue As Double
Private p_hoursNextCycleDue As Double

Private p_initialInHeavy As Boolean
Private p_inHeavy As Boolean

'An integer representing the Week Id the current cycle
'started, if in heavy maintenance
Private p_initialWeekIdCycleStart As Integer
Private p_weekIdCycleStart As Integer

Private p_initialHoursToDUE As Double
Private p_hoursToDUE As Double

Private p_initialHoursToDNE As Double
Private p_hoursToDNE As Double

'General Methods

'Custom Initialize
Public Sub init_aircraft(data_range As Range, asset_number As Integer, mxCycles() As Variant)
    p_tailNumber = data_range(asset_number, 1)
    p_tailNumStr = CStr(data_range(asset_number, 1))
    p_initialAircraftHours = data_range(asset_number, 2)
    p_currentAircraftHours = p_initialAircraftHours
    p_initialNextCycleType = data_range(asset_number, 3)
    p_nextCycleType = p_initialNextCycleType
    p_initialNextCycleDuration = mxCycles(p_nextCycleType, 2)
    p_nextCycleDuration = p_initialNextCycleDuration
    p_initialHoursNextCycleDue = data_range(asset_number, 4)
    p_hoursNextCycleDue = p_initialHoursNextCycleDue
    p_initialInHeavy = data_range(asset_number, 5)
    p_inHeavy = p_initialInHeavy

    If p_inHeavy Then
        p_initialWeekIdCycleStart = data_range(asset_number, 6)
        p_weekIdCycleStart = p_initialWeekIdCycleStart
    Else
        'set to a week prior more than the longest cycle duration
        p_initialWeekIdCycleStart = -10
        p_weekIdCycleStart = -10
    End If

    p_initialHoursToDUE = Round(p_hoursNextCycleDue - p_currentAircraftHours, 1)
    p_hoursToDUE = p_initialHoursToDUE
    p_initialHoursToDNE = Round(p_hoursNextCycleDue - p_currentAircraftHours + 15, 1)
    p_hoursToDNE = p_initialHoursToDNE
End Sub

'Return the aircraft objects properties as String
Public Function print_aircraft() As String
    print_aircraft = p_tailNumber & vbCrLf & _
                        "Current Hours: " & p_currentAircraftHours & vbCrLf & _
                        "Next Cycle: " & p_nextCycleType & vbCrLf & _
                        "Next Cycle Duration: " & p_nextCycleDuration & vbCrLf & _
                        "Hours Next Cycle Due: " & p_hoursNextCycleDue & vbCrLf & _
                        "In Heavy: " & p_inHeavy & vbCrLf & _
                        "Week Id Cycle Start: " & p_weekIdCycleStart & vbCrLf & _
                        "DUE: " & p_hoursToDUE & vbCrLf & _
                        "DNE: " & p_hoursToDNE
End Function

'Get/Let Methods

' Hours Remaining to the DNE
Public Property Get hoursToDNE() As Double
    hoursToDNE = p_hoursToDNE
End Property

Public Property Let hoursToDNE(HoursDNE As Double)
    p_hoursToDNE = HoursDNE
End Property

'Hours Remaining to the DUE
Public Property Get hoursToDUE() As Double
    hoursToDUE = p_hoursToDUE
End Property

Public Property Let hoursToDUE(HoursDUE As Double)
    p_hoursToDUE = HoursDUE
End Property

' Aircraft in Heavy Property
Public Property Get inHeavy() As Boolean
    inHeavy = p_inHeavy
End Property

Public Sub setInHeavy(Value As Boolean, weekIdCycleStarted As Integer)
    p_inHeavy = Value
    p_weekIdCycleStart = weekIdCycleStarted
End Sub

'p_weekIdCycleStart
Public Property Get weekIdCycleStart() As Integer
    weekIdCycleStart = p_weekIdCycleStart
End Property

'p_weekIdCycleStart
Public Property Let weekIdCycleStart(weekId As Integer)
    p_weekIdCycleStart = weekId
End Property

' Aircraft Hours at Next Maintenance Cycle Due Property
Public Property Get hoursNextCycleDue() As Double
    hoursNextCycleDue = p_hoursNextCycleDue
End Property

Public Property Let hoursNextCycleDue(Value As Double)
    p_hoursNextCycleDue = Value
End Property

' Next Maintenance Cycle Due Property
Public Property Get nextCycleType() As Integer
    nextCycleType = p_nextCycleType
End Property

Public Property Let nextCycleType(cycleType As Integer)
    p_nextCycleType = cycleType
End Property

' Next Maintenance Cycle Duration Property
Public Property Get nextCycleDuration() As Integer
    nextCycleDuration = p_nextCycleDuration
End Property

Public Property Let nextCycleDuration(cycleDuration As Integer)
    p_nextCycleDuration = cycleDuration
End Property

' Current Aircraft Hours Property
Public Property Get currentAircraftHours() As Double
    currentAircraftHours = p_currentAircraftHours
End Property

Public Property Let currentAircraftHours(Value As Double)
    p_currentAircraftHours = Value
End Property

' Tail Number Property
Public Property Get tailNumber() As Long
    tailNumber = p_tailNumber
End Property

Public Property Let tailNumber(Value As Long)
    p_tailNumber = Value
End Property
Zephyr Mays
  • 477
  • 3
  • 7
  • 24
  • You don't provide enough code for us to begin to repro what the problem might be. We need the same code and information you have - complete, including what you're writing to the objects. What's the name of the custom class? – Cindy Meister Feb 19 '16 at 16:48
  • . @CindyMeister updated as requested - still unsure what is causing the issue. Thought maybe it was caused by a type mis-match, but even when I use CStr() of CInt(), I still get a error. – Zephyr Mays Feb 20 '16 at 05:32
  • We still don't have the code that populates the collection. Without that, it's not possible to repro what's going on. Also, what line of code is causing the error? – Cindy Meister Feb 20 '16 at 07:33
  • . @CindyMeister - Apologies, that should be the whole thing now. The error is indicated via comment in the `For Each` loop at the end of `Private Function sortedAircraft`. Much appreciate your time looking over my work. – Zephyr Mays Feb 20 '16 at 07:46
  • What I'm tying to do in that `For Each` loop is set a property of the current item in the loop based on some properties of that current item and the one before it. E.g.: `acft.weekIdCycleStart = sortedAircraft.Item(previous_acft).weekIdCycleStart + sortedAircraft.Item(previous_acft).nextCycleDuration + 1` – Zephyr Mays Feb 20 '16 at 07:51
  • But I can't even get a simple `Debug.Print` to read those properties. Again, I'm new to VBA, so I'm probably missing something. Aside: I cannot use a `Dictionary` object as the development and intended use of the project will be in both Mac and Windows environments, and as I understand it `Dictionary` is not available on the mac side. Also, using Excel Mac 2011, but getting same error in Windows too. – Zephyr Mays Feb 20 '16 at 07:55
  • Thanks to this thread and the answer posted by answered by @ScottWhitlock [here](http://stackoverflow.com/questions/4805475/assignment-of-objects-in-vb6), I believe I found the source of the error. Though I don't completely understand the problem, I was able to trace the problem back to where I'd tried to duplicate each instance of c_Aircraft and add to a new Collection. Will post the full code shortly. – Zephyr Mays Feb 21 '16 at 03:31

0 Answers0