Creating Custom Data Projections

Custom projections transform your atomic model data into usable Excel tables and reports.

Approaches to Data Projections

Static VBA Approach

• Directly injects data into sheets using VBA, without formulas.
• Data remains static until the script is run again.
• Zero calculation overhead, because there are no formulas

UDF (Formula-based) Approach

• Uses custom worksheet formulas (UDFs) that dynamically update.
• Adds calculation overhead; UDF functions are triggered when Workbook is recalculated.
• Can use caching to fix some performance issues.

Pros and Cons

Static VBA

• ✅ Performance: Fast loading; minimal overhead after initial injection.
• ✅ Clarity: No hidden or redundant rows needed.
• ❌ Static: Data quickly becomes stale unless refreshed manually.
• ❌ Integration: Can't feed directly into other formulas easily.

UDF (Formula-based)

• ✅ Dynamic: Always up-to-date, never stale.
• ✅ Composable: Results can feed directly into other formulas.
• ✅ Self-resetting: Automatically clear when source data is emptied.
• ❌ Performance: Too many UDFs can slow down the workbook.
• ❌ Visibility Patterns: Excessive rows/columns may need to be pre-filled and hidden, cluttering sheets.

Important UDF Performance Tip

Use a simple IF formula as a "gate" to reduce unnecessary UDF calculations: =IF(A2="", "", YourUDFHere(A2))