Best Practices

Lessons learned from using DVDAddin effectively — avoiding mistakes, optimising your workflow, scaling up to large projects.

1. Standard workbook structure

One project = 1 master workbook + N child workbooks

❌ Wrong: a single Excel file holding all the data + calculations + reports for a project with 1000+ items → the workbook grows past 50 MB, opens slowly and crashes easily.

✓ Right:

• MasterData.xlsx — rate schedules, work item catalogue, norms (read-only).
• KhoiLuong.xlsx — the project quantity take-off (references MasterData).
• BCH_Tien_Do.xlsx — Gantt + construction diary.
• BCH_Nghiem_Thu.xlsx — acceptance records (BBNT) generated through Merge to Word.

Split them up → each file is small, loads fast and crashes less.

Sheet structure

A workbook should have three kinds of sheet:

Type	Purpose	Example name
Input	Where the user enters data	Input, DATA, Khoi_Luong
Config	Settings + lookups	Settings, BangGia, DanhMuc
Output	Reports + charts (hidden)	Report, Gantt, Summary

Set the Output sheets to VeryHidden → users cannot edit them by mistake.

Sheet names the add-in relies on

Some commands use their own reserved sheets — do not rename or delete them: DVDAddinSetting — the hidden sheet holding the configuration for Batch print; Mucluc — the lookup table matching sheet name ↔ the range whose merged-cell row heights must be fitted during batch printing.

Naming convention

✓ Sheet name: BBNTCV, KT_DD, BangGia_2026.

• No spaces (easier to reference in formulas).
• Use a prefix per type (BB = biên bản / report, KT = khối lượng / quantities, …).

To rename in bulk use Rename Sheets; to strip Vietnamese diacritics from sheet names and avoid reference errors, use UnDiacritics.

✓ Named Range: DanhSachHangMuc, BangGia_2026_Range.

• PascalCase.
• Suffix _Range for a range, _Value for a single cell.

Broken Named Ranges (from deleted sheets or copied files) make the workbook slow to open and produce #REF! — clean them out periodically with Delete error Defined Names.

2. Using UDFs the right way

Cell-level UDFs only for a handful of important cells

❌ Wrong: filling =dvdTranslate(A1,"vi","en") down 1000 rows. This function calls the network synchronously — Excel waits for one cell at a time, with a 15-second timeout per cell.

✓ Right:

• Select the whole range and run Translate — the result is written straight into the cells, and afterwards the workbook has no formulas left to recalculate.
• For bulk text processing with free-form instructions (summarising, rewriting to a document template), use the AI Chat instead of a formula.

There is exactly one AI function available as a UDF, dvdAIExplain (explain a formula) — it runs asynchronously so it does not block Excel, but it still consumes API quota for every cell.

Convert UDFs → values when you no longer need them dynamic

Once a UDF has returned its result → lock it in:

Ctrl + A  (select all)
Ctrl + C  (copy)
Ctrl + Alt + V → V → Enter  (paste values only)

→ The workbook no longer recalculates the UDFs on every change → it opens faster.

Limit volatile functions

Volatile functions are recalculated by Excel every time ANY cell changes. DVDAddin has exactly 5 volatile functions:

• dvdAutoHide
• dvdSumVisible
• dvdMVLookup
• dvdMCLookup
• dvdUniqueV

Built-in functions in the same category: NOW(), TODAY(), RAND(), OFFSET(), INDIRECT().

→ Keep the number of cells using them down to a few dozen. If you only need the total of a filtered range and do not need it updated instantly, SUBTOTAL(109, ...) is cheaper than dvdSumVisible.

3. Protecting important data

Sheet Protection

Sheets Settings, BangGia, DanhMuc — always protect them with a password:

• Review → Protect Sheet → set a password.
• Users can edit Input but CANNOT change Config.

Forgot the sheet password? → Unprotect Sheet.

Workbook Structure Protection

To stop users adding/deleting/renaming sheets:

• Review → Protect Workbook → Structure.

File Encryption (for sensitive data)

Files containing salary / actual cost information:

• File → Info → Protect Workbook → Encrypt with Password.

⚠️ Note: this encrypts the entire file and is completely different from a sheet password. The Unprotect Sheet command cannot open an encrypted file. Forgetting the file password means losing the data for good → use a password manager.

4. Version control

Folder structure per project

D:\Projects\MHC_Van_Phong\
├── 00_Master\
│   ├── MasterData.xlsx
│   └── Templates\
├── 01_TKBVTC\
├── 02_HoSoQLDA\
│   ├── KhoiLuong_v01.xlsx
│   ├── KhoiLuong_v02.xlsx     ← revision one week later
│   ├── KhoiLuong_v03_Final.xlsx
│   └── _archive\               ← old v01, v02
├── 03_NhatKy\
│   ├── Thang_5\
│   ├── Thang_6\
│   └── ...
└── _backup\                   ← weekly zip backup

Build this folder tree from a list in Excel with Create Folder. To capture an existing folder structure into a sheet for review, use Folder Tree.

Name files to a pattern

{DA}_{Loai}_{Date}_{Version}.{ext}

Examples:

• MHC_KhoiLuong_2026-05-15_v3.xlsx
• MHC_TienDo_W20_v1.pdf
• MHC_NhatKy_T05_v1.pdf

→ Easy to search, easy to archive, easy to roll back. To bring a batch of old files into line with the pattern: Rename Files.

Git for large teams

Team > 5 people + a long project → consider Git LFS for Excel:

• git init + .gitattributes: *.xlsx filter=lfs diff=lfs merge=lfs -text.
• Commit weekly.
• Each commit = 1 milestone.

(Advanced — only for teams with IT support. Note that Git cannot merge the contents of an Excel file; it only keeps the history of each version.)

5. License & Subscription

Buy the right number of seats

The Buy License dialog lets you choose the Standard or Pro edition, the duration (1 / 2 / 3 years, or Other — contact us) and the number of seats (1–10, or More than 10 seats — contact us). Each seat purchased produces its own license, all tied to the email given when ordering.

A team of 5 → buy the 5-seat package from the start, and ask the vendor for a separate sign-in account for each person. The reason is entirely practical: one account can hold only one session, so when a second person signs in the first one is thrown out mid-work.

PRO additionally unlocks the schedule analysis, QA/QC planning and AI quotation command groups — look for the PRO badge in the Command reference. The trial unlocks both editions in full, so try the exact commands you intend to buy before settling on an edition.

Know your expiry date in advance

The add-in does not raise an expiry warning on its own. To see how many days are left, open About — the License block shows the license type and the expiry date, plus a (N days left) note while the license is still valid — or License.

→ Set your own renewal reminder; do not wait until you open Excel and find the commands no longer run.

Back up the license

Before reinstalling Windows / switching machines, back up:

%LocalAppData%\DVDAddin\license.dat

The add-in also keeps a fallback copy in the registry at HKCU\Software\DVD\DVDAddin\Licensing (used when the %LocalAppData% folder is write-protected).

⚠️ Copying license.dat to another machine will not work: the license is bound to a machine identity hashed from three things — the MachineGuid of the Windows installation, the machine name and the operating system version. Reinstalling the add-in on the same Windows installation keeps the old file valid; reinstalling Windows from scratch or renaming the machine means you have to sign in again.

Update the right way

Close Excel completely before running the new installer. While Excel is open the .xll file is locked, the old build is still in memory, and you will get the "I updated it and the bug is still there" effect.

Check for a new version: Check Update; download the installer: Download Tool.

6. Backup strategy

Level 1 — Save the file often

Ctrl + S every 30 minutes of work.

Level 2 — Excel AutoRecover

File → Options → Save → "Save AutoRecover information every" → set 5 minutes (default is 10).

→ Excel crashes → reopen → you get the Recover option.

Level 3 — OneDrive / Google Drive auto-sync

Keep the project folder in OneDrive → every Ctrl + S uploads to the cloud automatically.

→ Machine fails / is lost → the file is still in the cloud.

Level 4 — Weekly archive

Every weekend:

• Zip the whole project folder.
• Name it DA_Backup_2026-05-15.zip.
• Upload to the cloud (Google Drive / Dropbox / S3).
• Keep the last 8 weeks.

Level 5 — Off-site backup

For critical projects:

• Back up to a physical hard drive kept in a different office.
• Back up to a cold storage service (S3 Glacier / Backblaze B2).

7. Collaboration

Single source of truth

Each type of data has one master file; the other files reference it read-only:

• The rate schedule master is MasterData.xlsx.
• Child files use =VLOOKUP(MaCT, '[MasterData.xlsx]BangGia'!$A:$H, 8).

→ Update the rate schedule → every child file picks it up when opened. The trade-off is that child files open more slowly (see Performance → #9).

Comment + review

When the team reviews a file:

• Right-click a cell → New Comment → write the feedback.
• Agree on a single Ghi_Chu sheet listing the items to fix if there are too many comments.

Real-time teamwork

The whole team editing one file → Connect. Two transports:

• LAN — one machine runs the server (default port 8888), the others enter the host IP to join.
• Internet over MQTT — all machines join the same "room ID" on a broker.

Along with it:

• Admin — assign View / Edit / Range permissions per sheet, Kick or Ban members. The host must set an Admin password before starting the server, otherwise this command will not open.
• Send Format — push the formatting of the selected range to the whole team.
• Disconnect — closing the window does not end the session; you must use this command.

Two things to agree on with the team beforehand: the Data password must be exactly identical on every machine (otherwise incoming packets cannot be decrypted), and for sensitive data do not use a public MQTT broker.

This is direct synchronisation between machines within one working session — not cloud storage; when the session ends, it is over. Storage and version naming still follow the Version control section above.

8. Documentation

A Huong_Dan sheet in every file

For a complex file → add a first sheet named Huong_Dan (yellow tab) with:

• The purpose of the file.
• The structure of the sheets.
• The important driver cells (especially if the file uses Batch print).
• The weekly update workflow.
• Contact details of the person who created the file.

→ A colleague / manager opening the file for the first time understands it immediately.

Named Ranges with descriptions

Formulas → Name Manager → set a comment for each Named Range:

• BangGia_2026 — "Rate schedule effective from 01/01/2026. Updated quarterly."
• DanhMucCongTac — "The company's standard work item catalogue."

→ Whoever comes next opens Name Manager and immediately sees what each range is for.

Related

• Performance Tuning — optimising large workbooks.
• Backup & Restore — the backup strategy in detail.
• Customization — registry, configuration files, shortcuts.
• Full command reference — all 186 ribbon commands.