Simplify VBA Code

Question

I have a macro which reads and writes data from two sheets in the same workbook.

Is it possible to clean up and simplify the code/statements to improve readability and aid in debugging efforts?

The statements have become so long they are confusing to read even when using the space-underscore method to use more than a single line.

Example of a statement which has become unwieldy:

Range("mx_plan").Cells(WorksheetFunction.Match(sortedAircraft.Item(i).tailNumber, Range("aircraft")), WorksheetFunction.Match(currentWeekId, Range("week_id")) + weekly_hours_col_offset) = (acft_hoursDNE / acft_weeksRemaining)

I've intentionally tried to avoid making explicit references to individual cells or ranges.

Answer 1

Your statement is 225 characters!

Debugging it will be impossible, because it's one instruction doing way too many things, and you can only place a breakpoint on a line of code... so you can't break and inspect any of the intermediary values you're using.

Break it down:

tailNumber = sortedAircraft.Item(i).tailNumber
aircraft = someSheet.Range("aircraft").Value
planRow = WorksheetFunction.Match(tailNumber, aircraft)

weekId = someSheet.Range("week_id").Value
planColumn = WorksheetFunction.Match(currentWeekId, weekId)

Set target = someSheet.Range("mx_plan").Cells(planRow, planColumn + weekly_hours_col_offset)
target.Value = acft_hoursDNE / acft_weeksRemaining

Remember to declare (Dim) all variables you're using (use Option Explicit to make sure the code won't compile if you make a typo with a variable name), use meaningful names for all identifiers (names that tell the reader what they're for - use comments when the why isn't obvious from the code alone).

By breaking it down into multiple smaller steps, you're not only making it easier to read/maintain, you're also making it easier to debug, because a runtime error will be raised in a specific instruction on a specific line, and you'll be able to more easily pinpoint the faulty inputs.