Modularised Document Construction

The goal: A set of configurable options, combined with a standardised data source, to produce a standardised Report.

This question: One module of said document, with accompanying code.

Input: (Dropdown selection): An Attitude To Risk (ATR) Value. 1 to 5.

Business Logic: Select from pre-configured descriptions and hardcoded text sections. Insert relevant name into introduction text. Format as appropriate.

Output: An attitude to risk description and overview. Printable.

Input:

Hardcoded Text:

Pre-Configured Descriptions:

Output:

Considering this document is likely to span to ~30 sections or more, is my current approach sufficiently modularised and self-contained?

Have I chosen a bad way to go about this? (I considered building it in Word, but Word is, well, messy).

How's my naming?

Is it okay to hard-code Named Ranges like that?

How easy is it to follow my code?
Any other feedback is, of course, welcomed.

Code:

```
Option Explicit

Public Sub BtnMakeSelections_OnClick()

DisableApplicationSettings

FillAtrSheet

ResetApplicationSettings

End Sub

Public Sub FormatAsPlainText(ByRef formatRange As Range)

formatRange.Font.Size = 12
formatRange.Font.Bold = False
formatRange.Font.ColorIndex = 0

End Sub

Public Function GetAtrNum() As Long

Dim atrRange As Range
Set atrRange = wsInputs.Range("ATR_Selection")

Dim atrNum As Long
atrNum = CLng(atrRange.value)

GetAtrNum = atrNum

End Function

Public Sub CopyPasteCell(ByRef copyCell As Range, ByRef pasteCell As Range, Optional ByVal pasteRowHeights As Boolean = False)

copyCell.Copy
pasteCell.PasteSpecial xlPasteAll

If pasteRowHeights Then
Dim sourceRowHeight As Long
sourceRowHeight = copyCell.rowHeight
pasteCell.rowHeight = sourceRowHeight
End If

End Sub

Public Sub FindAndFormatAsHeading(ByRef targetCell As Range, ByVal targetString As String)

If HasCharacters(targetCell) Then
Dim targetCharacters As Characte

Actually, I wanted to post the following as a comment. But it is too much text for a comment and therefore I am posting it not as an answer but merely "for your consideration":

I (personally) hate named ranges. Just like mentioned in your question that "Word is kind of messy" I have the same experience with named ranges. People are copying things into your workbook and named ranges get changed or end up broken. Some people are not aware that they are not supposed to touch the named ranges and ... well, still they do.

I do know that - at some point - you have to code a reference and all of them can get broken (especially in Excel where there is almost no security). So the following is probably more of a "taste" thing and can be well debated:

I (personally again) prefer a very hidden Ref sheet where I duplicate (linked cells) all important data and then take everything for my code from that sheet instead. The essential advantage of this solution over named ranges is that (1) you have all important data in one place and that (2) broken links can be easily fixed. Furthermore, you have (3) an entire sheet. That allows you for more information and possible "meta data" or background information (when compared to only having the name of a named range). You may even color code your Ref sheet to make it easier to navigate (for yourself and others).

Since Office 2007+ the compression on Excel files is pretty good and the increase in file size is negligible. Other than that linked cells behave pretty similar to named ranges and (when moved within the file) do not break but get automatically "fixed" to the new location.

Another approach is described here (which I still prefer over named ranges): https://stackoverflow.com/questions/36800416/working-with-changing-named-ranges-excel-vba/36800944#36800944

Disclaimer: as I said. These are merely my personal preferences. Still, I can say that this works pretty well (for years) in a rather large corporation environment.

StackExchange Code Review Q#123458, answer score: 3