Table of contents
At a glance
How do you validate an Excel workbook that uses VBA?
Define the intended GMP use of the Excel workbook with VBA, business rules and consequences of failure. Identify the exact code, workbook and environment versions tested. Combine code review with tests for normal inputs, boundaries, invalid inputs and interrupted runs. Document results, deviations and release. A signed macro or successful execution alone does not establish that the calculations are correct or operation controlled.
- The subject of inspection comprises the technical procedure including VBA, formulas, data sources, and environment.
- Independent nominal values and targeted edge cases make code errors visible.
- Program release, macro security, and individual result verification are distinct responsibilities.
- Every relevant change requires a traceable state and a justified test selection.
When does VBA belong to Excel validation?
A macro can perform calculations, import files, filter datasets, prepare batch releases, or export reports. Therefore, it is essential to first evaluate the full workflow: what data enters the process, what decisions does the code make, and what output is subsequently used? Even a seemingly simple sorting function can alter the mapping between sample and result.
The EMA explicitly refers to validation pursuant to Annex 11 for GMP spreadsheets with individualized code, using VBA as an example; this does not imply a blanket requirement for all office documentation. Within the GMP context, the intended use and risk profile determine the depth of assessment. A formatting macro and a calculation used for quality decisions carry different implications for errors.
This guide expands on the use of Excel with VBA within the pharmaceutical GMP environment. FDA sources relate to Active Pharmaceutical Ingredients (API) under cGMP, provided this area is relevant to your process. The following review steps are practical implementation suggestions; they do not replace the definition of your specific application scope.
Separate program version, draft, and result dataset
The term "Version 2" is not enough if different files have the same name. Define a shared status of workbook, VBA project and relevant configuration. Link it with reason for change, check and release. A specific analysis process also receives its own record identifier; hundreds of completed copies are not one hundred software versions.
A possible logic is as follows: Release V1.0 is approved; V1.1-RC1 is a candidate for review; V1.1 is the resulting release. Any change during review generates a new candidate version. Numbering alone provides no protection: access, storage, and assignment must ensure traceability of version changes.
| Component | What to record | Practical demonstration |
|---|---|---|
| Workbook | File version, pages, formulas, names, and settings | Controlled original file; unique release identifier |
| VBA Project | Module, Forms, References and Code Modifications | Code comparison plus assignment to the actually inspected file |
| Environment | Excel-Build, operating system, Office bitness, relevant add-ins | Test environment protocol; agreed operational scope |
| Validation basis | Requirements, test data, independent setpoints | Versioned test cases, results, deviations, and review |
| Result record | Process identifier, entries, program status and processing | Continuous recording with professional assessment |
Define technical guidelines prior to code review
Describe a calculation without VBA: allowed inputs, units, calculation steps, rounding point, limit values, and behavior for missing data. Otherwise, the test merely confirms that the code performs the action it already performs. Expected results should be independently derived from the approved calculation procedure.
In the code review, assess the implementation: Are the work folder and table area clearly referenced? Are sample identifiers retained during sorting? What occurs with additional rows, empty import files, or a different active sheet? These questions represent independent review suggestions and will be selected based on the actual data flow.
Option Explicit helps identify undeclared variables during compilation. However, it does not detect a logically incorrect unit or a swapped reference between two valid variables. Code review and execution with targeted test data complement each other; a successful compile run is not evidence of correct results.
Example: Purposefully verify a rounding adjustment
In the example, a macro calculates the quotient A/B from two positive values A and B. For this example process, the following conditions are defined: B must be greater than zero; the result is rounded to the nearest whole number at the end, with exactly half-values being rounded up. This specification represents a selected example rule and is not a general GMP rounding rule.
Version R1 uses VBA Round. Microsoft describes this as Banker's Rounding: a half value is rounded to the nearest even number. Thus, the case of 5/2 represents the exact critical scenario. Candidate R2 is intended to implement the specified example rule. The selection of an alternative function must be aligned with this requirement; a Microsoft recommendation does not replace professional judgment.
The target values for the following matrix were independently verified using decimal arithmetic. The workbench was not executed here. R1 and R2 are example runs. U1 denotes the desktop test environment to be fully documented prior to execution; U1 is not a claimed Excel version. Result, execution date, and tester will be added only during actual testing.
Test matrix for code changes and unchanged functions
First, verify whether the selected test can detect the difference between R1 and R2. A comparison against only 7/2 would cause both rounding rules to yield 4, thus passing both cases. Then, supplement the test with edge cases and error pathways; the following matrix serves as a starting point, not a complete validation protocol. The discrepancy in R1 arises because the documented rounding behavior of VBA Round does not align with the specified example rule; the example does not describe any error in Excel or VBA, and it is not a performed product test.
| Version / Environment | Test data or event | Expected result | Review Focus |
|---|---|---|---|
| R1 / U1 | A=5; B=2 | The expected result is 3. VBA Round returns 2, revealing the discrepancy | Test detects the known difference |
| R2 / U1 | A=5; B=2 | 3 according to the specified example rule | New code meets the amended requirement |
| R2 / U1 | A=7; B=2 | 4 | Original case remains correct |
| R2 / U1 | A=249 or 251; B=100 | 2 or 3 | Values on both sides of the rounding boundary |
| R2 / U1 | B = 0, empty or text | Reject input; no usable new result | No silent replacement and no old result as new |
| R2 / U1 | Termination prior to completion; subsequent restart thereafter | Detect incomplete runs; perform a clean restart | No mixing of partial results |
| R2 / U1 | Destination repository without write permission | Storage error visible; no success message | Export status matches the existing file |
| R2 / U1, Macro block disabled | Open file and request calculation | Assert no successful macro execution. | Do not open the file for processing evaluation |
Incorrect entries and premature terminations must not produce plausible historical values.
A meaningful negative test begins with a successful run and subsequently generates an error. If the previous result cell remains visible, the user must be able to clearly recognize its status. Existing records are not deleted; the new failed attempt must not appear as a successful new result.
On Error Resume Next may allow execution to continue after a runtime error. This is not inherently incorrect, but it requires appropriate error evaluation and handling. A blanket continuation until successful completion may, for example, mask a failed import. Therefore, verify the error path following the first written value.
Changes to Application.EnableEvents or the calculation mode must be accounted for in the design of controlled restoration to the previous state. Normal termination and handled errors should be verified. A process crash may prevent such cleanup logic; therefore, a detectable incomplete run and a regulated restart are required. Dependent formulas must demonstrably use the current data state prior to export of results.
Macro security and signature do not prove computational correctness
The IT system defines which code may be executed. Microsoft describes for Office on Windows a macro blocking mechanism that depends on origin and trust status as well as policy settings. A blocked file must be clearly identifiable as unusable for macro processing within the intended workflow. Security rules should not be broadly relaxed to enable testing to proceed.
A VBA project signature protects the statement regarding the signed code version, not the accuracy of the calculations. Upon code modifications, the signature is removed; if the valid previous certificate is available on the device, the project can be automatically re-signed upon saving. Therefore, "the file is signed" is not a sufficient comparison to the released version.
Distinguish three decisions: May the code be executed? Has this specific program version been technically approved? Has each individual result dataset been reviewed? The macro signature does not automatically answer all three questions and does not replace an electronic release signature for the dataset.
Consider Desktop, Office Bitness and Dependencies
Excel online cannot create or execute or edit VBA macros. However, a macro file can still be opened and edited in this environment. Therefore, verify how users are directed to the intended desktop execution and how a simply opened file can be distinguished from one that has completed a macro run.
When invoking Windows API calls, differences between 32-bit and 64-bit Office versions may be significant. Microsoft notes that PtrSafe alone is insufficient: pointers, handles, and associated declarations must be compatible. This does not imply a blanket requirement for modification of every macro. First assess whether such calls or other architecture-dependent issues are present.
Additional in-house validation questions relate to decimal separators in imports, library references, add-ins, and network paths. Define supported combinations and justify the test selection. An unmanageable diversity of workstation configurations may generate more effort than the actual change to the software.
Release, subsequent modifications and effort planning in SMEs
For a manageable project, the Technical Department should provide the calculation rules, example data, and edge cases. A code owner shall describe the implementation and dependencies. IT shall document the operational environment and distribution; QA shall evaluate the intended evidence and release criteria within the quality management system. If a single individual reviews multiple areas, competence and review responsibilities must remain clearly traceable.
The effort is particularly dependent on unknown legacy code, missing requirements, external data sources, numerous file copies, and ambiguous change rights. Start with a current-state assessment and a clearly defined, critical workflow. A number of macro-lines or a generic daily estimate does not provide reliable effort forecasting.
In case of a change, you will document affected requirements, common functions, data formats and required retests. Keep the assignment of previous records to the current state. A PDF can be a useful report; it does not automatically replace the input, processing information and metadata required for the reconstruction.
Release checklist for a VBA change
Use this checklist for joint review. A missing item indicates a specific need for clarification; tick marks alone do not constitute successful verification.
- Intended use, relevant macros, and consequences for the process of errors are described.
- The verified file is uniquely associated with code, configuration, and environment.
- Reference values were determined independently of the specific macro being tested.
- Normal cases, edge cases, incorrect inputs, interruptions, and failed storage are verified.
- Old values and partial results cannot be confused with a successfully completed new run.
- Macro release, signature, and professional approval are evaluated separately.
- Changes, deviations, reviews, and accepted operating limits are documented.
- Distribution, recall of obsolete templates, and retrieval of prior records are governed.
Project experience and collaboration
Link the VBA run to its code, inputs and expected results
For a VBA workbook, we identify the decision its result supports. The process owner supplies known inputs and independent expected values. IT identifies the code version, libraries, storage paths and execution environment. Focused normal and failure cases may cover a small macro. Linked files or automated exports also require handover tests. An interruption during execution is particularly informative. Can users tell which results remain usable and which state must be restored?
Frequently asked questions about Excel with VBA
Must every Excel file with VBA be validated?
The intended use is decisive. The EMA requires validation of GMP spreadsheets with individualized code pursuant to Annex 11. This does not justify a blanket obligation for private or general office files. First, establish the GMP relevance; then define the scope based on functions and potential consequences of errors.
Must each code line have its own test case?
A rigid mapping "one line, one test" is not a viable validation strategy. Assign requirements, critical branches, and failure paths to appropriate tests. Code review can reveal technical interdependencies; execution tests demonstrate behavior in the intended environment. Justify which combinations adequately address the specific risks.
Are historical datasets acceptable as test data?
They are helpful for typical workflows but may not include edge cases or errors. Purposefully supplement missing values, incorrect data types, and cases on either side of a decision threshold. Calculate expected values independently. Historical outputs from the same macro alone do not constitute independent validation evidence that the calculation is correct.
Is Git required for VBA version control?
The above mentioned GMP sources do not prescribe Git. The decisive factors are controlled changes and the unique assignment of the released status. Git can support code comparisons; exported modules alone do not automatically capture all workbook components. Therefore, keep together the actually checked file, configuration and release comprehensible.
Is a digital macro-signature sufficient as evidence of release?
No. It does not replace professional testing or reference to your authorized release. Microsoft also describes automatic re-signing upon saving, provided that the previously used valid certificate is available on the device. Therefore, compare the signed version with the controlled release package and manage the signing authorization accordingly.
Why might VBA Round return a different result than expected?
VBA Round uses Banker's Rounding. This may differ from the expected rounding rule at exactly halfway values. Define the rounding digit, timing, and handling of halfway values prior to selecting a function. Also verify values immediately adjacent to the threshold; cell formatting alone does not replace the defined calculation.
Sources: [5]
What is the most important test during a macro-breakdown?
Check whether an incomplete run is detectable as such and whether, upon restart, old and new results are not inadvertently mixed. Test the termination following the first data modification. Include considerations for memory errors and altered application settings; a caught runtime error is not equivalent to an ended Excel process.
Can a VBA workbook be operated in the browser under validated conditions?
Web Excel does not execute VBA. If your approved process requires a macro function, opening the file in the browser is not a substitute for desktop execution. Define the authorized execution pathway and ensure that users do not inadvertently adopt results from a file that has not been executed.
Sources: [11]
Must every Office update be revalidated?
Determine the extent from the documented effect. An update can affect relevant components without changing the VBA code; vice versa, not every update automatically justifies a complete repeat. Check especially interfaces, calculation dependencies and security guidelines. Document the selection of repeat tests and remaining operating limits.
How can a small team efficiently prepare for VBA validation?
First, provide the original file, code access, calculation rule, a complete example workflow, and the workstation configuration. Identify the subject matter expert, code owner, IT, and QA responsibilities. If there is a lack of coding understanding or independent target values, resolve this gap prior to conducting extensive test protocols: otherwise, documentation will be recorded without the ability to assess technical accuracy.
Sources and review date
The basis is the aforementioned EU-GMP and FDA sources, as well as publicly available Microsoft documentation retrieved on 6 October 2026. Microsoft information is specific to the described platforms. The test matrix, versioning schema, and inspection procedures are internal implementation proposals; the example workbench was not executed.
- EMA: GMP/GDP Questions and Answers – Annex 11, Spreadsheets
- European Commission: EU GMP Annex 11, Computerised Systems, 2011
- European Commission: EU GMP Chapter 4, Documentation, 2011
- FDA: Data Integrity and Compliance With Drug CGMP, finale Guidance, 2018
- Microsoft Learn: Round function (Visual Basic for Applications)
- Microsoft Learn: On Error statement
- Microsoft Learn: Application.EnableEvents property (Excel)
- Microsoft Learn: Application.Calculation property (Excel)
- Microsoft Support: Digitally sign your VBA macro project
- Microsoft Learn: Macros from the internet are blocked by default in Office
- Microsoft Support: Work with VBA macros in Excel for the web
- Microsoft Learn: 64-bit Visual Basic for Applications overview
- Microsoft Learn: Option Explicit statement
Sources checked: .
Clarify the testing requirements of your VBA workfolder
Daniel Herrmann Consulting supports you in distinguishing existing Excel applications, evaluating evidence gaps, and implementing changes in a traceable manner. The starting point is your process and the actually used workbook.
- Computer system validation →
Bring your GxP systems into validated operation, from laboratory software, MES and eQMS to ERP. We define the scope, plan risk-based tests using GAMP 5 and hand the evidence to QA for release.

