HR Questionnaire from PDF to Excel
MORPHO DESIGNS SOFTWARE
"Powered by ParserMonster!"
Mitchell Allen – Support [redacted]
Report date: Sunday, September 03, 2017
AHRA CONTROLLER
The control panel is on the Instructions tab of the XLSM file that was delivered in your AHRA package. It was
designed to match a client's Questionnaire to the Answers, calculate Scores and generate a score file for
importing into AHRA PDF Generator. In addition, the control panel has buttons for various maintenance and
customization tasks. Each button is explained in this section.
CALCULATE SCORES
Once you have received a completed Questionnaire, copy it to the AHRA folder. Click Calculate Scores to
create an updated ScoreCurrentAudit.CSV, whose contents should be brought immediately into the Scorecard
tab of AHRA Controller.xlsm. When the scoring is completed, a simple message pops up:
If necessary, switch back to the AHRA Controller and click the Scorecard tab.
If Excel's calculation mode was set to Manual, change it to Automatic so that the table can update.
The calculation icon is in the ribbon's Formulas group.
MORPHO DESIGNS SOFTWARE
"Powered by ParserMonster!"
Mitchell Allen – Support [redacted]
Report date: Sunday, September 03, 2017
MAINTENANCE
You are the owner of all the code. You and your chosen developers are free to modify it at will. End-user
maintenance is covered in the AHRA Controller.xlsm section. You may want to share this Guide with said
developers and ask them to read the following carefully:
The Question and Answer databases were developed with the understanding that the audit wouldn't change.
Before embarking on any major updating of the Audit, you must understand the existing system. Once you have
a firm grasp, you should seriously consider consolidating all back-end processing under the Microsoft Access
platform. You can still use the Excel template, or perhaps you'll find a way to dynamically generate XLSX files.
Design choices were made in the interest of expediency, as well as in the interest of preserving sanity during the
manual review of the 1700+ response choices. Chief among the choices was the decision to correlate the data
validation feature of Excel with the response score.
The idea is simple: the audit is linear. The responses are limited to drop-down menu choices. By counting the
number of menu choices for each answer, one can find the nth score for a given nth response. In practical terms,
each response was given a unique identifier.
When the Calculate Scores button is clicked, a couple of dynamic class structures are built and used for
looking up the correct response. Careful examination of the code, comments and class modules should reveal
the inner workings.
MORPHO DESIGNS SOFTWARE
"Powered by ParserMonster!"
Mitchell Allen – Support [redacted]
Report date: Sunday, September 03, 2017
This design had the happy benefit of simplifying the color-coding of min and max scores. You'll find a hidden
worksheet with the codename XPivotSdb. The first and last columns were pasted into another hidden worksheet
with the codename XColorCode. (The pivot table was set to display min first, to get the data for the second
column of XColorCode.) Study these in conjunction with the Conditional Formatting rules and you'll be able to
update the color codes if needed.
Note: Whenever you change the min-max table, be sure to copy and paste just the values
to a text file and import that into Microsoft Access – you'll see the relevant table.
MORPHO DESIGNS SOFTWARE
"Powered by ParserMonster!"
Mitchell Allen – Support [redacted]
Report date: Sunday, September 03, 2017
One final thing: be aware of how the auto-generated VBA is built from strings in the XBackendData tab of the
Questionnaire template. Those must be pasted into the LoadQQ() procedure in the XLSM. It is very tedious to
create or modify the drop-down menus, because you must create a named range for each. Study the existing
data to see how things are connected. Here is a sample of the named ranges in the template:
At first, each drop-down group was given a name associated with the response itself, such as
QisChartUpdateFreq. Later, it was easier to just use the category and question number, such as QisHRIS10.
The fragility of this connection is the main reason for considering a rebuild, rather than trying to deal with such
contortions.