При открывании книги откройте текстовый файл и пишите лог туда. При закрытии книги закройте файл.  
Заведите глобальную переменную ProcedureName, в начале каждой процедуры присваивайте этой переменной название процедуры, при ошибке пишите в лог в т.ч. эту переменную.  
 
Еще по теме: http://programmersforum.ru/showthread.php?t=212666

Всем спасибо!  
Простите, что не отвечал. В общем, по косточкам:  
можно записать в ячейки ошибки + использовать переменную ProcedureName, + есть замечательная встроенная переменная Erl, но работает она только в том случае, если код пронумерован. Тогда в Debug будет отображаться номер строки, в которой возникла ошибка.  
Теперь трудности с логом. Здесь для меня - темный лес. Есть идея записывать всё в глобальный массив, а затем создавать текстовый файл и выгружать содержимое массива в него, но, поскольку я в них не разбираюсь, то дело будет долгим...  
Еще раз спасибо.

Для добавления/снятия нумерации строк кода можно использовать утилиту MZ-Tools  
 
Для записи в файл можно использовать, например, такую процедуру:  
 
Sub DebugLog(Msg)  
 With ThisWorkbook  
   Shell "cmd /c echo " & Msg & ">>""" & .Path & "\" & .Name & ".log"""  
 End With  
End Sub  
 
Тогда вместо, например, Debug.Print "MyLog test " & Now  
достаточно записать: DebugLog "MyLog test " & Now  
При этом отладочная информация будет дописываться в текстовый файл с таким же именем, что и excel-файл, но с расширением .log