I've been putting together a UDF in Excel (365) to calculate the longest common subsequence between two strings (based on this implementation in python 
Other Tips:
- Use Option Explicit.
- I use MZ Tools for Excel. If you are a serious programmer, I definitely recommend using that.
CodePudding user response：
Excel "swallows" user-defined function errors and wraps them into a Variant/Error value, such that any function that throws a VBA run-time error would return a #VALUE! error to the calling worksheet.
The trick is to remove the wrapper and invoke the function directly yourself.
In the VBIDE, press Ctrl G to bring up the immediate toolwindow, then type ? followed by the name of the function and its arguments:
?ClosestMatch("aabbaaaa", "aaaabbaa")
The ? is shorthand for PRINT so if all goes well, the function returns a value that gets printed right underneath:
?ClosestMatch("aabbaaaa", "aaaabbaa")
aa
But if anything goes wrong and the function throws an error, you'll get a VBA run-time error prompt and will be taken directly to the instruction responsible for the #VALUE! error the worksheet is seeing, and by using the debugger tools at your disposal you'll be able to:
- Hover any variable to see its value
- Bring up the locals toolwindow to see all variables and their values
- Set the current statement (the yellow arrow) to any other statement in the function
- Step-through (F8) the code and execute a single statement at a time
- Place and remove breakpoints (F9) to halt execution at a specific statement
- Resume execution (F5), even after modifying the code on-the-fly
Consider using Debug.Print statements instead of MsgBox, to print to the immediate toolwindow instead of popping a disruptive message box.
Then consider writing several test methods that call your function with various argument combinations, and assert on the result: if the function returns the expected output, the test passes, otherwise the test fails. When all tests pass, you know your function is going to work as intended for all the covered cases. Rubberduck (a free and open-source VBIDE add-in project I started a few years ago) gives you tooling to easily write and run such unit tests, and its static code analysis can help you avoid many pitfalls, beginner traps, and archaic code constructs (for example While...Wend can be written as a more standard Do While...Loop structure).