Thread: Debugging VBA

The purpose of this thread is to assist users in debugging VBA. It's intended to be a source of links in other threads to which we can vector members for basic debugging. Learning to debug is the very best way to improve your coding skills, and I invite participation by all members who provide accurate, useful, and concise descriptions of methods.

This is not a question forum, and all questions will be unceremoniously deleted. If you have a question, start a new thread and provide a link in the relevant post in this thread.

For starters, I highly commend http://www.cpearson.com/excel/Debug.htm as a point of departure.

The Evaluate function is a powerful capability of VBA to modify contiguous ranges without loops, and Forum Guru DonkeyOte is particularly adept in its use. Here's an example of mine from http://www.excelforum.com/excel-prog...-function.html.

Sub x()
    Dim r As Range
    Dim sAdr As String
    Dim sFrm As String
    
    Set r = Selection.Cells
    r.NumberFormat = "@"
    sAdr = r.Address
    sFrm = "if(row(" & sAdr & "), text(" & sAdr & ",""dd-mm-yyyy""))"
    'Debug.Print sFrm
    r.Value = Evaluate(sFrm)
End Sub

The point of the example is not to explain the problem it solves. Rather, the purpose it to show that the formula to be evaluated is first constructed as a string, which is then (in the statement that is commented out) printed to the Immediate window to verify that it is well-formed and appropriate. It's easier to look at the formula in the Immediate window than to try to sort out a run-time 1004 error without ever seeing the problematic formula.