The basic idea is assumed references are absent or wrong at runtime. Since the binary workbook macro runs from Java, debugging it is already a layer removed.
The fix was to specify the workbook:
Usually, I just call the subroutine and all that, everywhere. It was okay until now. Now, I have to think about this; the difficulty is compounded by not being locally reproducible. “Works on my machine!” Ha.
Before this, I had to copy-paste the data, click on the clipboard, and choose to “Convert to Number.” This snippet lets you do it within the macro:
Long strings of numbers that should be treated as text can break VLOOKUP. It’s also nice to see 94282849 instead of 9e7 in the cell.
Explicitly specify the columns argument and surround the array variable with parentheses. The documentation speaks of defaults and conveniences; do not believe it.
Range("A:A").RemoveDuplicates Columns:=(Array(1)), Headers:=xlYes
I’ve started using scratch tables, ephemeral views (if spreadsheets were single tables). I’ll spawn one at the end with Sheets.Add(), process it, and then delete it. It feels like each solution is subtly different, and I am gradually building a collection of useful subroutines. Things are getting cleaner, too.
The common elements I’ve been using are column enumerations, a sheet to act as a trace output console, and a lot of abuse with evaluations. Here I concatenate literal ampersand in a runtime formula with escaped double quotes with a UNC workbook path:
Cells(row_no,1) = "=VLOOKUP(J" & row_no & ", '\\net\path\[wb.xlsx]" & _
"Sheet1'!B:D, 3, 0)" & Chr(38) & """yes"""