DiseaseExporter

Internal members (not exported)

Factory

Create #

Instantiate a DiseaseExporter with its dependencies configured.

Signature:

Public Function Create(ByVal workbookManager As DiseaseExportWorkbook, _
                       ByVal appGuard As ApplicationState) As DiseaseExporter

Parameters:

Returns: DiseaseExporter ready to generate exports.

Configuration

Initialise #

Attach the workbook manager and application guard.

Signature:

Public Sub Initialise(ByVal workbookManager As DiseaseExportWorkbook, _
                      ByVal appGuard As ApplicationState)

Disease Export

BuildDiseaseWorkbook #

Populate a workbook with dictionary, choices, translations, and metadata.

Signature:

Public Function BuildDiseaseWorkbook(ByVal diseaseWksh As Worksheet, _
                                     ByVal translationTable As ListObject, _
                                     ByVal diseaseName As String, _
                                     ByVal languageTag As String, _
                                     Optional ByVal diseaseCode As String = vbNullString, _
                                     Optional ByVal logger As DiseaseLogger = Nothing) As Workbook

The Choices sheet is the master Choices sheet cut down to the lists the disease uses, in the six-column layout of a setup. The two editable label columns are then translated into the language of the disease, and the label column carries the formula a setup keeps there: the translated label when present, the plain label otherwise. The dictionary, choices and translations blocks are each wrapped in a ListObject named the way a setup names its own, so SetupImport finds them by name.

Parameters:

ExportDisease #

Export the disease workbook to disk and return the saved file path.

Signature:

Public Function ExportDisease(ByVal exportFolder As String, _
                              ByVal diseaseWksh As Worksheet, _
                              ByVal translationTable As ListObject, _
                              ByVal diseaseName As String, _
                              ByVal languageTag As String, _
                              Optional ByVal diseaseCode As String = vbNullString, _
                              Optional ByVal logger As DiseaseLogger = Nothing) As String

Internal Helpers

EnsureFreshWorkbook #

Ensure the workbook manager has a pristine workbook.

Signature:

Private Sub EnsureFreshWorkbook()

BeginGuard #

Begin the guarded Application state only once per call.

Signature:

Private Sub BeginGuard(ByRef guardStarted As Boolean)

RestoreGuard #

Restore guarded Application state when required.

Signature:

Private Sub RestoreGuard(ByVal guardStarted As Boolean)

ValidateDiseaseInputs #

Validate disease export inputs.

Signature:

Private Sub ValidateDiseaseInputs(ByVal diseaseWksh As Worksheet, _
                                  ByVal diseaseName As String, _
                                  ByVal languageTag As String)

ValidateFolder #

Validate that a folder path was supplied.

Signature:

Private Sub ValidateFolder(ByVal exportFolder As String)

PrepareSheets #

Prepare disease export worksheets.

Signature:

Private Sub PrepareSheets(ByVal targetBook As Workbook, _
                          ByRef metadataSheet As Worksheet, _
                          ByRef dictionarySheet As Worksheet, _
                          ByRef choicesSheet As Worksheet, _
                          ByRef translationsSheet As Worksheet)

ResolveDiseaseTable #

Retrieve the disease table from the worksheet.

Signature:

Private Function ResolveDiseaseTable(ByVal diseaseWksh As Worksheet) As ListObject

ResolveMasterChoices #

Wrap the master Choices sheet of the workbook holding the disease.

Signature:

Private Function ResolveMasterChoices(ByVal diseaseWksh As Worksheet) As LLChoices

Answers Nothing when the workbook carries no Choices sheet; the export then falls back on the values the disease sheet carries.

PopulateMetadata #

Populate metadata worksheet with export details.

Signature:

Private Sub PopulateMetadata(ByVal metadataSheet As Worksheet, _
                             ByVal diseaseName As String, _
                             ByVal languageTag As String, _
                             ByVal diseaseCode As String)

WriteWorkbookTags #

Write the disease name, language and code as hidden names of the workbook.

Signature:

Private Sub WriteWorkbookTags(ByVal targetBook As Workbook, _
                              ByVal diseaseName As String, _
                              ByVal languageTag As String, _
                              ByVal diseaseCode As String)

The Metadata sheet says the same for a person reading the file; the tags are what the import reads, and they survive a file that lost its Metadata sheet on the way.

PopulateDictionary #

Populate the dictionary sheet in the Tab_Dictionary table.

Signature:

Private Sub PopulateDictionary(ByVal dictionarySheet As Worksheet, ByVal diseaseTable As ListObject)

The headers are the ones of a setup dictionary, so CustomTable.Import matches them by name. Variable Order stays as the first column: the setup dictionary has no such column and the import skips it.

BuildDictionaryMatrix #

Build a dictionary matrix selecting required columns.

Signature:

Private Function BuildDictionaryMatrix(ByRef sourceData As Variant, ByVal rowCount As Long) As Variant

Choices Export

PopulateChoices #

Populate the choices sheet with the lists the disease uses.

Signature:

Private Sub PopulateChoices(ByVal choicesSheet As Worksheet, _
                            ByVal diseaseTable As ListObject, _
                            ByVal masterChoices As LLChoices, _
                            ByVal logger As DiseaseLogger)

Six columns, in the order of a setup Choices table: list name, ordering list, non translated label, translated label, label, short label. The rows come from the master Choices sheet, read once as a block and kept to the lists the disease names. A list the master sheet does not carry takes the values the disease sheet holds for it, and is logged. The label column is written as the formula a setup keeps there. The block is wrapped in the Tab_Choices table.

BuildChoiceRows #

Build the choice rows: the master rows of every list the disease names.

Signature:

Private Function BuildChoiceRows(ByRef sourceData As Variant, _
                                 ByVal masterChoices As LLChoices, _
                                 ByVal logger As DiseaseLogger) As Collection

DiseaseListNames #

Distinct list names of the disease table, in first-use order.

Signature:

Private Function DiseaseListNames(ByRef sourceData As Variant) As BetterArray

ReadMasterBlock #

Read the master Choices block with its headers, once.

Signature:

Private Function ReadMasterBlock(ByVal masterChoices As LLChoices, _
                                 ByRef block As Variant, _
                                 ByRef nameColumn As Long, _
                                 ByRef labelColumn As Long, _
                                 ByRef shortColumn As Long) As Boolean

Returns: Boolean. True when the block carries a list name and a label column.

SameHeader #

True when two headers are the same text, whatever their case.

Signature:

Private Function SameHeader(ByVal headerText As String, ByVal expected As String) As Boolean

AppendMasterRows #

Append the master rows of one list; answers how many were added.

Signature:

Private Function AppendMasterRows(ByVal rows As Collection, _
                                  ByRef block As Variant, _
                                  ByVal nameColumn As Long, _
                                  ByVal labelColumn As Long, _
                                  ByVal shortColumn As Long, _
                                  ByVal listName As String) As Long

AppendDiseaseRows #

Append the values the disease sheet carries for a list the master lacks.

Signature:

Private Sub AppendDiseaseRows(ByVal rows As Collection, _
                              ByRef sourceData As Variant, _
                              ByVal listName As String)

ChoiceRow #

Build one export row; the label column is filled by its formula later.

Signature:

Private Function ChoiceRow(ByVal listName As String, _
                           ByVal position As Long, _
                           ByVal labelValue As String, _
                           ByVal shortValue As String) As Variant

WriteLabelFormulas #

Write the label formula on every exported choice row.

Signature:

Private Sub WriteLabelFormulas(ByVal choicesSheet As Worksheet, ByVal rowCount As Long)

The translated label when present, the non translated label otherwise, which is the formula the setup Choices sheet keeps.

TranslateChoices #

Translate the exported choices into the language of the disease.

Signature:

Private Sub TranslateChoices(ByVal choicesSheet As Worksheet, _
                             ByVal translationTable As ListObject, _
                             ByVal languageTag As String, _
                             ByVal logger As DiseaseLogger)

LLChoices.Translate writes the translated label and the short label columns; the non translated label keeps the master value and the label formula follows. A language the translations table has no column for leaves the values as they are and is logged.

TableHasColumn #

Whether a ListObject carries a header of the given name.

Signature:

Private Function TableHasColumn(ByVal table As ListObject, ByVal headerName As String) As Boolean

Warn #

Record a warning on the logger when one is given.

Signature:

Private Sub Warn(ByVal logger As DiseaseLogger, ByVal message As String)

RangeBlock #

Read a range as a two-dimensional block, one cell included.

Signature:

Private Function RangeBlock(ByVal target As Range) As Variant

TextOf #

Text of a cell value; an error value reads as empty.

Signature:

Private Function TextOf(ByVal cellValue As Variant) As String

Shared Helpers

ContainsKey #

Determine whether a name has already been seen, without regard to case.

Signature:

Private Function ContainsKey(ByVal keys As BetterArray, ByVal key As String) As Boolean

FindWorksheet #

Locate a worksheet by name without relying on implicit errors.

Signature:

Private Function FindWorksheet(ByVal targetBook As Workbook, ByVal sheetName As String) As Worksheet

SplitChoices #

Split choice strings safely.

Signature:

Private Function SplitChoices(ByVal rawValue As Variant) As Variant

PopulateTranslations #

Copy the values of a ListObject to a worksheet in one assignment.

Signature:

Private Sub PopulateTranslations(ByVal targetSheet As Worksheet, _
                                 ByVal sourceTable As ListObject, _
                                 Optional ByVal tableName As String = vbNullString)

Parameters:

WrapAsTable #

Wrap a block anchored on A1 in a plain ListObject of the given name.

Signature:

Private Sub WrapAsTable(ByVal targetSheet As Worksheet, _
                        ByVal dataRowCount As Long, _
                        ByVal columnCount As Long, _
                        ByVal tableName As String)

Parameters:

ApplySheetCosmetics #

Give the four sheets of a disease export their format.

Signature:

Private Sub ApplySheetCosmetics(ByVal metadataSheet As Worksheet, _
                                ByVal dictionarySheet As Worksheet, _
                                ByVal choicesSheet As Worksheet, _
                                ByVal translationsSheet As Worksheet)

A reader opens an exported disease file and a setup migration export and sees the same thing. The Metadata sheet is a label and value block, so its two columns take their own widths.

Export Format

FormatWrittenSheet #

Give one written sheet the export format.

Signature:

Public Sub FormatWrittenSheet(ByVal targetSheet As Worksheet, _
                              Optional ByVal columnWidth As Double = EXPORT_COLUMN_WIDTH)

The shape is the one DataSheet.Export writes for a setup migration: body font Consolas 9, a header band of RGB(240, 240, 244) carrying a bold 10, wrapped text and a column width of 25.

Only the written block is formatted. Running this over Cells meant the auto-fit walked a million empty rows and the width was set on every column of the sheet.

The header height is written after the auto-fit. The auto-fit reads the wrapped body and sizes row 1 with it, so a height set before it is lost.

The exported tables carry no table style, so this format is the whole of what a reader sees.

Parameters:

FormatMetadataSheet #

Give the Metadata sheet its two widths.

Signature:

Public Sub FormatMetadataSheet(ByVal targetSheet As Worksheet)

The sheet holds a label column and a value column. The value column is the wider of the two; the rest is the export format.

Parameters:

WrittenBlock #

The written block of a sheet.

Signature:

Private Function WrittenBlock(ByVal targetSheet As Worksheet) As Range

A sheet holding nothing answers Nothing. The UsedRange of an empty sheet is $A$1, and formatting it would band the first row of a sheet a reader was given nothing on.

Parameters:

Returns: Range. The used block, or Nothing when the sheet is empty.

HideGridLines #

Turn the gridlines off on one written sheet.

Signature:

Private Sub HideGridLines(ByVal targetSheet As Worksheet)

The view of a sheet is reached through the window of its workbook, the way LLExporter.RemoveGridLines reaches it. A workbook with no window leaves the sheet as it is. The zeros stay on show: a 0 in the ordering column is a value a reader needs.

Parameters:

FreezeHeaderRow #

Freeze the header row of one written sheet.

Signature:

Private Sub FreezeHeaderRow(ByVal targetSheet As Worksheet)

The freeze needs the sheet in front and a window to write on, so the sheet is activated first. A window can refuse it: an Excel running off screen answers 1004, "Unable to set the FreezePanes property of the Window class". A frozen header is how a sheet looks, so a refusal leaves the sheet written and the export carries on. LLDataEntry.FreezeHeader answers the same refusal the same way.

Parameters:

EnsureWorksheet #

Ensure a worksheet exists and optionally rename the provided template.

Signature:

Private Function EnsureWorksheet(ByVal targetBook As Workbook, _
                                 ByVal sheetName As String, _
                                 Optional ByVal template As Worksheet = Nothing) As Worksheet

ClearSheet #

Clear worksheet contents while preserving structure.

Signature:

Private Sub ClearSheet(ByVal sheet As Worksheet)

WriteHeaderRow #

Write one header row through a BetterArray.

Signature:

Private Sub WriteHeaderRow(ByVal anchorCell As Range, ByVal values As Variant)

CollectionToMatrix #

Convert a collection of row arrays into a matrix.

Signature:

Private Function CollectionToMatrix(ByVal rows As Collection, ByVal columnCount As Long) As Variant

BuildDiseaseFilePath #

Build path for disease export.

Signature:

Private Function BuildDiseaseFilePath(ByVal exportFolder As String, ByVal diseaseName As String) As String

The separator is added only when the folder lacks one, so a folder picked at a drive root gives one separator.

SanitizeFileName #

Remove characters invalid in file names.

Signature:

Private Function SanitizeFileName(ByVal fileName As String) As String

ErrorHandling

ThrowError #

Centralised ProjectError-based exception helper.

Signature:

Private Sub ThrowError(ByVal errNumber As ProjectError, ByVal errMessage As String)
Used in (8 file(s))