Power User Tips

Advanced techniques combining DVDAddin + Excel + scripting for pro users.

The formulas on this page are written for the Vietnamese Regional format, using a semicolon ; to separate arguments. On a machine running the English (US) format, type a comma , instead.

1. VBA macros calling DVDAddin UDFs

DVDAddin's UDFs are XLL functions and can be called from VBA with Application.Run using exactly the same function name as in the worksheet:

Sub TestDvdUDF()
    Dim result As String
    result = Application.Run("dvdVnd", 1234567, 1)
    Debug.Print result   ' "Bằng chữ: Một triệu hai trăm ba mươi tư nghìn..."

    Dim translated As String
    translated = Application.Run("dvdTranslate", "Hello", "en", "vi")
    Debug.Print translated   ' "Xin chào"

    Debug.Print Application.Run("dvdUnDiacriticsVi", "Bê tông cốt thép")
    ' "Be tong cot thep"
End Sub

Notes:

The two asynchronous functions (DVDFx and dvdAIExplain) do not block VBA — on the first call they return an #N/A error instead of waiting for the result (see section 8).
The functions that write their result into a target range (dvdMCLookup, dvdMVLookup, dvdUniqueV, dvdAutoHide) and the drawing functions (dvdPic, dvdQR, dvdColorShapes) are designed to run inside a formula cell; when called from VBA, the part that acts on the sheet may not happen.
Besides the UDFs, the .xll also registers fixed-name shortcut macros of the form DvdShortcut_* — VBA can call them directly, for example Application.Run "DvdShortcut_AutoFitMerge". This is how you run a ribbon command from your own macro.
The list of 47 functions and their exact signatures: UDF functions.

2. Power Query + DVDAddin

Power Query runs on its own M engine and cannot call Excel UDFs — not even an add-in's. How to combine the two tools:

Pattern: PQ loads the data → a UDF column processes it → output

Power Query loads the CSV / database → sheet RawData.
Sheet Processed holds formulas referencing it, for example =dvdUnDiacriticsVi(RawData!A2).
Refresh PQ → RawData updates → Processed recalculates by itself.

If the source is an Excel Table, a formula column sitting inside the Table follows the new row count automatically after each refresh — no filling down by hand.

What to leave to PQ

Combining files, unpivoting, joining several tables, bulk data-type conversion — PQ is far faster than UDFs because it runs once at refresh instead of recalculating cell by cell. Conversely, leave the Vietnamese-specific and construction-records-specific parts (removing diacritics, spelling numbers out in words, two-way norm lookups, reading a schedule) to the UDFs.

3. Using UDFs in Conditional Formatting

A CF rule of the "Use a formula to determine which cells to format" kind can call a UDF, but only a pure function: takes a value, returns a value, never touches the sheet.

=dvdCountOccurrences(A1; "Ø") > 0

→ Highlights cells containing the diameter symbol written as Ø.

=dvdUnDiacriticsVi(A1) <> A1

→ Highlights cells that still carry Vietnamese diacritics in a column that is supposed to be accent-free (file names, codes).

Cannot be used in CF:

Functions that write to the sheet: dvdMCLookup, dvdMVLookup, dvdUniqueV, dvdAutoHide.
Drawing functions: dvdPic, dvdQR, dvdColorShapes, dvdMoveShape.
Functions that need a real reference and run in macro mode: dvdExplain, dvdExplainE, dvdAIExplain.
Network functions (dvdTranslate, dvdStock, DVDFx) — technically they can run, but CF recalculates very often and would call the network continuously.

If CF does not work, put the UDF result in a helper column and have CF reference that column — this always works and is faster.

4. Multi-monitor layout

Excel has supported multiple monitors since the 2013 version. Many DVDAddin windows are modeless (they do not lock Excel), so you can push them onto a second monitor and still work on the worksheet:

Translate — select a different cell in Excel while the window is open.
Numbering — select a new range and click Apply again, without reopening the command.
Batch print — adjust the form and the print settings at the same time.
Explain formula (Coach) — move to another cell and click Explain again to analyse the new cell.
File List — writes into the selected cell each time you click Apply.

Conversely, some windows are modal and must be closed before you can carry on: Preferences, Find Cmd, About.

Modeless windows write into the selected cell

The very thing that makes them convenient makes them dangerous: the window writes into the cell that is selected at the moment you click Apply. Glance at the cursor before every click.

Excel has a Quick Access Toolbar (QAT) — pin the commands you use a lot:

Right-click a DVDAddin button on the ribbon → Add to Quick Access Toolbar.
The QAT button appears on the top bar (above or below the ribbon).
Call it with Alt + 1/2/3/... following the order on the QAT.

Worth pinning to the QAT: License, Translate, AI Chat, Numbering, Sheet List.

DVDAddin's default shortcuts

Keys bound to a ribbon button:

Shortcut	Command
`F3`	Formula View
`F6`	Copy/Paste visible
`F10`	Sheets → PDF
`Ctrl+Shift+D`	Move/Resize
`Ctrl+Shift+I`	Italic
`Ctrl+Shift+M`	AutoFit merged
`Ctrl+Shift+Q`	Find Cmd
`Ctrl+Shift+R`	Text Reverse
`Ctrl+Shift+S`	SMART case (AI)
`Ctrl+Shift+W`	Navigation
`Ctrl+Shift+Z`	Merge keep data
`Alt+C`	Insert and Copy
`Alt+S`	OCR Screen Clip
`Alt+X`	Copy/Paste visible — same command as `F6`
`Ctrl+Alt+I`	Quick Input
`Ctrl+Alt+S`	Capture and Annotate

Keys with no matching button on the ribbon — keyboard only:

Shortcut	Effect
`F8`	Invokes Excel's Format Painter
`Shift+F1`	Takes the content of the selected cell as a question for the AI and writes the answer into the cell directly below
`Ctrl+J`	Cycles horizontal alignment: left → center → right
`Ctrl+M`	Cycles vertical alignment: top → middle → bottom
`Ctrl+Shift+A`	Fills the selection with a yellow background; press it again on an already-yellow cell to remove the fill
`Ctrl+Shift+C`	Cycles letter case: UPPERCASE → Title Case Of Each Word → Sentence case → lowercase
`Ctrl+Shift+E`	Quick-translates the selection into English, overwriting in place
`Ctrl+Shift+V`	Quick-translates the selection into Vietnamese, overwriting in place

A few practical notes:

Ctrl+Alt+S is a system-wide hotkey (registered at the Windows level), so it captures from whatever window you press it in; the other keys only work while Excel is the foreground window.
F10 overrides Excel's key for opening the menu bar — press Alt when you need the menu.
The two quick-translate keys overwrite cells directly and need a network connection; for large tables use the Translate command.
Shortcuts only work once the add-in has finished loading; right after installing you must restart Excel.

The Shortcuts section in Preferences is the editing table: click a row's key cell and type a new combination to change it, pick any ribbon command in the list box and click add to create a new shortcut row (up to 10 self-added commands), or click the button that restores the defaults. Settings are stored in HKCU\Software\DVD\DVDAddin\Shortcuts, so backing up that registry key backs up your whole shortcut set as well (see section 18).

If you cannot remember a shortcut, press Ctrl+Shift+Q and type a few letters of the command name — faster than hunting for the button on the ribbon.

6. Reading the add-in's logs when something goes wrong

DVDAddin writes logs for a few parts that fail most often. These are what to attach when you report a bug to support:

Log	Path	Contents
Case change	`%TEMP%\DVDAddin\case.log`	Details of which cells were changed and why some could not be
Ribbon labels	`%TEMP%\DVDAddin\ribbon-labels.log`	Diagnostics for when the ribbon shows the wrong text or is missing buttons
DVD Chat	`%TEMP%\dvdaddin-chat.log`	Connection and messages of the team chat
Construction diary	`%LocalAppData%\DVDAddin\diary.log`	Every step of the New Diary command, with the build tag

Quickly view the newest log:

powershell

Get-ChildItem "$env:TEMP\DVDAddin" -Filter '*.log' |
    Sort-Object LastWriteTime -Descending |
    Select-Object -First 1 |
    Get-Content -Tail 100

The last line before Excel froze usually points straight at what broke. diary.log also records the build tag — use it to tell which add-in build Excel is running; see the next section.

7. Checking whether Excel is really running the new .xll

"I already updated but the bug is still there" is usually not an unfixed bug but Excel still holding the old build in memory. Excel keeps the loaded XLL file until every Excel window is closed for good — installing over it while Excel is open either makes the installer report a file-lock error, or copies the new file while the running session is still the old code.

Compare Excel's start time with the XLL file's modification time:

powershell

$xll = 'C:\DVDAddin\DVDAddin-AddIn64-packed.xll'
(Get-Item $xll).LastWriteTime
Get-Process EXCEL -ErrorAction SilentlyContinue | Select-Object Id, StartTime

An Excel StartTime earlier than the XLL's LastWriteTime means the current session has not loaded the new build — quit every Excel (including processes left behind in Task Manager) and reopen it.

See which XLL Excel is registered to load:

powershell

Get-ItemProperty 'HKCU:\Software\Microsoft\Office\16.0\Excel\Options' |
    Select-Object -Property OPEN*

Each OPEN, OPEN1, OPEN2… entry has the form /R "C:\DVDAddin\DVDAddin-AddIn64-packed.xll". The 32-bit build uses the file DVDAddin-AddIn-packed.xll in the same folder.

8. Asynchronous functions and `#N/A waiting...`

Two functions that fetch external data run asynchronously: DVDFx and dvdAIExplain. On the first calculation the cell shows #N/A waiting...; when the result arrives Excel updates the cell itself — no F9 needed.

Practical consequences:

Do not wrap IFERROR around them. IFERROR swallows the waiting state and the cell stays stuck on the fallback value.
DVDFx caches for one hour per currency pair and per date — filling the formula down 500 rows with the same USD/VND pair costs a single network call.
To lock an exchange rate for a payment dossier, pass the date argument: =DVDFx("USD"; "VND"; "2026-06-30") gives a fixed result that does not change with the day the file is opened.
dvdAIExplain needs a Gemini API key declared in Preferences → the AI Assistant section; without a key the function returns nothing.

The other two network functions — dvdTranslate and dvdStock — run synchronously, so filling hundreds of rows at once will freeze Excel and can easily get you blocked by the provider. For large tables, use the Translate command instead of the formula.

9. Excel Online, Mac and Excel mobile

DVDAddin is an .xll add-in (Excel-DNA) that runs on Excel desktop for Windows. Excel Online, Excel for Mac and Excel mobile use a different add-in model (Office.js) and therefore cannot load DVDAddin — no DVD Addin / DVD Cons ribbon, no dvd* functions.

The consequence for teamwork: a file with dvd* formulas opened in Excel Online shows #NAME? in those cells. The already-calculated values are still in the file, but they are lost the moment Excel Online recalculates. For files you have to send to people without the add-in, Copy → Paste Values the UDF columns before sending.

10. Bilingual VI + EN workbook

=dvdTranslate(A1; "vi"; "en")
="Vietnamese: " & A1 & " | English: " & B1

→ Column A in Vietnamese, column B translated into English, column C combining both for international clients.

For official records, Paste Values once the translation is done so the translation does not change every time the file is opened. For bilingual dates in a report, use dvdLDate with language argument 2.

11. Excel + Python in Excel

Office 365 has Python in Excel (=PY(...)). A Python cell runs on Microsoft's cloud, so it cannot call dvd* UDFs — just as with PQ, the two sides exchange data through intermediate cells:

dvd* UDFs for Vietnamese text processing and records logic, with the result sitting in a cell.
=PY(...) reads that range with xl("A1:C100") for the heavy analysis (pandas, statistical charts).

The real flow: raw data → UDF cleans/normalises → Python analyses → results returned to Excel cells.

12. One form, hundreds of dossiers

This is the most valuable technique in Batch print and the one most often overlooked: instead of writing a script, you let the add-in write each number in turn into a control cell (driver cell) and reprint the form after every assignment.

The form sheet takes all of its content from one cell — for example BBNT!$B$2 holds the element number, and the remaining cells VLOOKUP on that number.
In the Batch print window: Number range = Continuous 1–120, Driver cell = BBNT!$B$2, Sheet-print cell pointing at the cell containing the string BBNT+PhuLuc, Filename cell pointing at the cell with the formula ="BBNT_Cot_"&B2.
Tick the option to combine the PDFs into one file, then click Export PDF.

The configuration is saved into a hidden DVDAddinSetting sheet inside the workbook itself — send the file to someone else and the configuration travels with it, ready to run as soon as they open it. The add-in really changes the driver cell value while it runs, so save the file before a large batch.

This window needs manual interaction, so it cannot run unattended; put the real automation in the step that follows — see section 14.

13. Referencing a shared file on the network

A child workbook referencing a price-list file stored on a server:

='\\server\share\[BangGia.xlsx]Sheet1'!$A$1

(The square brackets wrap the file name, not the sheet name — getting this wrong is the most common mistake.)

Every time the price list on the server is saved, the child workbook updates the next time it is opened, or when you click Data → Edit Links → Update Values.

If the whole crew needs to see the changes immediately in the same file, this is the wrong tool — use Connect. That command synchronises cell values and formatting in real time between several machines, over a LAN (one machine runs the server with a port and a password, the others connect by IP) or over the internet with an MQTT broker and a room code. It does not synchronise files: no copy is downloaded to any machine, only cell changes are pushed.

Two things people stumble on: every machine in the session must enter exactly the same Data Password (packets are encrypted with it), and closing the window is not leaving the session — to stop for good you must run Disconnect.

14. Automatically archiving PDFs by date

A PowerShell script that runs every evening:

powershell

$today = Get-Date -Format 'yyyy-MM-dd'
$src = 'D:\Output\BBNT'
$dst = "D:\Archive\BBNT\$today"

if (-not (Test-Path $dst)) { New-Item -ItemType Directory -Path $dst | Out-Null }
Move-Item -Path "$src\*.pdf" -Destination $dst

Windows Task Scheduler → New Task → Trigger: Daily 11PM → Action: run the script above.

→ The D:\Output\BBNT\ folder always stays clean and the exported records file themselves by date. The script only touches files and never opens Excel, so it runs even when nobody is logged in — quite unlike trying to drive Excel from a scheduled task (Microsoft does not support Office automation in unattended mode; it hangs silently at the first dialog).

15. Getting the AI to build and fix formulas

No UDF accepts a free-form prompt. DVDAddin's AI features live in ribbon commands and in one formula-explaining function:

AI Chat — a question-and-answer window; describe what you need ("sum column B, only the rows where column A = Hà Nội") and get a formula back.
Explain formula (Coach) — breaks the selected cell's formula down step by step; it is modeless, so you can move to another cell and click Explain again.
Fix formula error — diagnoses #N/A, #VALUE!… and proposes a replacement formula.
=dvdAIExplain(F6; TRUE) — explains right inside the cell, handy when the explanation has to be stored in the file.

All of them need an API key declared in Preferences → AI Assistant.

16. Recording repetitive actions with Auto Click

Auto Click records every mouse click with its coordinates and timestamp into a table on the open sheet (columns No. / Timestamp / X / Y / Wait ms), then replays it for as many loops as you set.

Its strength compared with Excel's macro recorder: it clicks into any window — cost-estimate software, a records-management website, design software with no macro support.

Because the action sequence sits right on the sheet:

You can edit it by hand: adjust X, Y to shift a position, adjust Wait (ms) to wait longer for the target application to respond.
Saving the workbook saves the script; sending the file to someone else sends the whole procedure.
It writes into the active sheet — open a blank sheet before pressing Recorder, otherwise it overwrites your data.

Limits to remember: replay uses absolute screen coordinates (move a window, change the resolution or the display scaling and the clicks land in the wrong place) and it only simulates mouse clicks, no typing. Test with a small loop count before letting it loose on real data.

17. Calling UDFs across workbooks

DVDAddin's UDFs load at application level (XLL), so every open workbook can call them — no reference to add.
VBA macros in your own .xlam file are the opposite: a workbook that wants to use them must go to Tools → References in the VBE and tick that project's name.
An .xlsx file containing dvd* formulas opened on a machine without the add-in shows #NAME?. Paste Values before sending it outside.

18. Backing up the license before a Windows update

A Windows feature update (23H2 → 24H2…) usually keeps the user profile but can lose some registry keys. DVDAddin stores the license in two places and reads them in that order:

Location	Contents
`%LocalAppData%\DVDAddin\license.dat`	Primary copy
`HKCU\Software\DVD\DVDAddin\Licensing`, value `LicenseBlob`	Fallback copy, used when the folder above is not writable

The add-in's general settings live in HKCU\Software\DVD\DVDAddin.

Before the update:

powershell

$dst = "$env:USERPROFILE\Documents\DVDAddin-backup"
New-Item -ItemType Directory -Path $dst -Force | Out-Null
Copy-Item "$env:LOCALAPPDATA\DVDAddin\license.dat" "$dst\license.dat" -Force
reg export "HKCU\Software\DVD\DVDAddin" "$dst\dvdaddin-settings.reg" /y

After the update, if the license has disappeared:

powershell

Copy-Item "$env:USERPROFILE\Documents\DVDAddin-backup\license.dat" `
          "$env:LOCALAPPDATA\DVDAddin\license.dat" -Force

If you get an Access denied error when the add-in writes the license: the file has the read-only attribute set, or the whole folder is blocked (ACL, Windows Defender's Controlled Folder Access, or an old directory sitting exactly at the license.dat path). Fix it with attrib -r, then delete the file and let the add-in recreate it; the registry key above is the fallback when the filesystem blocks it outright.

19. Bulk data entry with Quick Input

For quantity tables, construction diaries and material ledgers — anything where you have to type hundreds of rows — Quick Input (Ctrl+Alt+I) saves more than every formula on this page.

By default it suggests the unique values taken from the column above. The advanced configuration lives in Configure source ranges…: declare Target range → Source range rules to force the add-in to take its suggestions from the proper master catalogue, even on another sheet.

The most valuable part is the per-column mappings: one rule can map several Target range ↔ Source column pairs, so picking one row in the catalogue fills a whole set of columns at once — type "xi mang" in the Name column and the add-in also fills in Material code, Unit and Unit price.

Turn on Auto-suggest on cell select while you are doing data entry so the window pops up on every cell selection; turn it off when you go back to writing formulas. Even switched off, it can still be called with Ctrl+Alt+I.

Note: rules are bound to range addresses, so if inserting or deleting rows and columns shifts the target range, you have to reopen the dialog and update the address.

20. Updating the add-in on several machines

Check Update only checks the version and opens the download page — it does not install anything. The procedure to get the whole team onto the same build:

On each machine run DVD Addin → About → About Menu → Check Update and compare version numbers.
On any machine that has a newer build, click Download (or use Download Tool to open the installer link directly).
Close every Excel — including EXCEL.EXE processes left behind in Task Manager. The loaded XLL file is locked; installing over it while Excel is open either reports an error or leaves a session running the old code.
Run the installer over C:\DVDAddin, reopen Excel and check the version number in About.

Confirm that the right build is loaded by comparing Excel's StartTime with the XLL's LastWriteTime — see section 7 above.

With many machines at once: the XLL registration lives in HKCU\Software\Microsoft\Office\<ver>\Excel\Options under the values OPEN, OPEN1… so a deployment script must run per user profile, not per machine.

Recipes — handy formula snippets.
Customization — deep configuration through the registry.
Performance — optimising heavy workbooks.
Full command reference — 186 ribbon commands.
UDF functions — 47 worksheet functions.

Power User Tips ​

1. VBA macros calling DVDAddin UDFs ​

2. Power Query + DVDAddin ​

Pattern: PQ loads the data → a UDF column processes it → output ​

What to leave to PQ ​

3. Using UDFs in Conditional Formatting ​

4. Multi-monitor layout ​

5. Quick Access Toolbar and shortcuts ​

DVDAddin's default shortcuts ​

6. Reading the add-in's logs when something goes wrong ​

7. Checking whether Excel is really running the new .xll ​

8. Asynchronous functions and #N/A waiting... ​

9. Excel Online, Mac and Excel mobile ​

10. Bilingual VI + EN workbook ​

11. Excel + Python in Excel ​

12. One form, hundreds of dossiers ​

13. Referencing a shared file on the network ​

14. Automatically archiving PDFs by date ​

15. Getting the AI to build and fix formulas ​

16. Recording repetitive actions with Auto Click ​

17. Calling UDFs across workbooks ​

18. Backing up the license before a Windows update ​

19. Bulk data entry with Quick Input ​

20. Updating the add-in on several machines ​

Related ​

Power User Tips

1. VBA macros calling DVDAddin UDFs

2. Power Query + DVDAddin

Pattern: PQ loads the data → a UDF column processes it → output

What to leave to PQ

3. Using UDFs in Conditional Formatting

4. Multi-monitor layout

5. Quick Access Toolbar and shortcuts

DVDAddin's default shortcuts

6. Reading the add-in's logs when something goes wrong

7. Checking whether Excel is really running the new .xll

8. Asynchronous functions and `#N/A waiting...`

9. Excel Online, Mac and Excel mobile

10. Bilingual VI + EN workbook

11. Excel + Python in Excel

12. One form, hundreds of dossiers

13. Referencing a shared file on the network

14. Automatically archiving PDFs by date

15. Getting the AI to build and fix formulas

16. Recording repetitive actions with Auto Click

17. Calling UDFs across workbooks

18. Backing up the license before a Windows update

19. Bulk data entry with Quick Input

20. Updating the add-in on several machines

Related