Forecast maintenance interval for fleet of aircraft

Being new to VBA, and many years removed from college programming courses in Java, I'm seeking opinions about design, implementation, readability and general better practices using VBA to tackle complex problems. There are probably other programming languages much better suited to this problem, but I'm limited by the resources available (excel only) to the users it's intended for.

I'm developing a program to forecast maintenance intervals for a fleet of aircraft, by week. The aircraft are maintained in a phased cycle based on hours flown. The goal of the program is to determine, by week, when each aircraft will be due for it's next maintenance cycle, if in any week the fleet of aircraft cannot fly all the planned hours and when, if at all, two aircraft run out of hours and end up requiring maintenance in the same week, given the following constraints.

I currently spend an inordinate amount of time fat-fingering a spreadsheet to do precisely this, and the inter-related nature of each aircraft's hours flown in a given week, and their maintenance cycles seems uniquely suited for a computer program to figure out.

Constraints:

• Each aircraft is required to enter it's maintenance cycle after a fix amount of hours flown (e.g. 200)

• Only one aircraft can be in a maintenance cycle at once

• Each maintenance cycle requires differing amounts of time to complete (e.g. after 200 hours, the cycle takes one week to complete, but after 800 hours it takes five weeks)

• Each week the fleet of aircraft will have a fix number of hours to fly

• Each aircraft may be assigned to fly a fixed number of hours in a given week

The Goal

The end goal of the program is to determine both when you run out of hours and two aircraft are forecast to be in a maintenance cycle in the same week, or when you can't meet the planned hours for the fleet in a given week (e.g.run out of hours) by not allowing more than one aircraft to be in a cycle at the same time.

Inputs:

An Excel Workbook

I'm going to go for the low hanging fruit first and then try to comb through the code.

Structure - It's good practice to indent all of your code that way Labels will stick out as obvious. I'm not sure if you did that and the code block removed it or not.

Personal preference - I like variables all defined on their own line as it makes it easier to read rather than several dim-ed together. In the same breath I'll say great job on always giving variables a type even if they are declared on the same line. A lot of people make the mistake of only using a type once.

Standard VBA naming conventions have camelCase for local variables and PascalCase for other variables and names. Constants are usually Const ALLCAPS.

Speaking of Variable names - you've given your variables meaningful names, but they are difficult to work out. Why use col instead of column? Personal preference - I avoid variables like i and j because they aren't descriptive. But, there's technically nothing wrong with using them as it's standard practice to iterate with i. All of the underscores _ look messy to me as I'm only familiar with VBA conventions.

There's a lot of Worksheets("Matrix Inputs").Range("maintenance_cycles").Cells(1, 3).

Why not use the CodeName for the sheets? Worksheets have a CodeName property - View Properties window (F4) and the (Name) field can be used as the worksheet name. This way you can avoid Sheets("mySheet") and instead just use mySheet. At the same time you keep using .cells() - there has to be a better way. You're already using named ranges, so why the need for .Cells()? At least on the ones that aren't iterating.

Overall with your variables, they are pretty good, but there's no consistency. Some are like c_aircraft others are like startWeek1 and even some are like CInt.

A+ on using Option Explicit. The same goes for not using any .Select or .Activate statements.

As well as always using ByVal instead of ByRef, but ByRef is the default when not passing explicitly ByVal.

Integers - integers are obsolete. According to msdn VBA silently converts all integers to long.

Comments (though with the complexity I may off) - "code tells you how, comments tell you why". The code should speak for itself, if it needs a comment, it might need to be made more clear. If not, the comment should describe why you're doing something rather than how you're doing it. Here are a few reasons to avoid comments all together.

Maybe RubberDuck has some things to say as well.

For things like this

If cellIsInVisibleRange(Range("week_id").Cells(1, WorksheetFunction.Match(endWeek, Range("week_id"), 0))) Then
            If WorksheetFunction.Match(endWeek, Range("week_id"), 0) < viewportOffset Then
                    Application.Goto Range("week_id").Cells(1, 1), True
            Else
                    Application.Goto Range("week_id").Cells(1, WorksheetFunction.Match(endWeek, Range("week_id"), 0) - viewportOffset), True
            End If
  End If

There's just way too much going on here to keep track of - what are the Gotos doing? They go to a particular cell? I don't understand that at all, but that might be because I have no data to test.

When you are doing two loops back to back like this -

For i = 2 To Range("mx_plan").Columns.Count Step 3
            'For j = 1 To Range("mx_plan").Rows.Count
                    Range("mx_plan").Columns(i).ClearContents
            'Next j
  Next i

  For i = 3 To Range("mx_plan").Columns.Count Step 3
            'For j = 1 To Range("mx_plan").Rows.Count
                    Range("mx_plan").Columns(i).ClearContents
            'Next j
  Next i

Why not just combine them?

For i = 2 To Range("mx_plan").Columns.Count Step 3
            
                    Range("mx_plan").Columns(i).ClearContents
                    Range("mx_plan").Columns(i + 1).ClearContents
            
  Next i

I'm having a pretty difficult time figuring out how your table works. Where do you keep the current number of hours on the plane and cycle ID? Or are they stored and just not shown in the image? And the hours assigned per the constraints - do you assign that based on remaining hours or are those the required hours for the aircraft? If they are required, what happens if one plane is in maintenance and another needs the preventative maintenance? Does it just sit?

My approach

would be to work directly with my tailnumber table and call functions to do the work. Ignoring the exact way that you do it, it would be something like this -

Option Explicit

```
Sub myAircraftHours()
Dim planeTailNumber As Long
Dim planeTargetHours As Long
Dim planeRemainingHours As Long
Dim planeInMaintenance As Boolean
Dim currentWeek As Range
Dim plane As Range
Dim currentWeekID As Long
currentWeekID = 1
Dim currentWorkWeek As Long

For Each plane In Range("aircraft")
currentWorkWeek = WorksheetFunction.Match(

StackExchange Code Review Q#120772, answer score: 6