O nível mais básico de depuração de uma macro já é útil: é assim que começar;
Organize os pré-requisitos para executar a macro e, em seguida, pressione ALT + F11
Procure a macro em qualquer 'Módulo' (talvez seja necessário abrir um painel no menu Visualizar para vê-los), também pode haver 'Módulos' em vários locais - um deles é um local 'padrão' e menos um em cada folha aberta além disso.
Como você tem o código de macro visível; Clique para colocar o cursor, por exemplo em uma linha de interesse, ou em uma linha um pouco antes.
Note que o Excel não permite pontos de interrupção em linhas somente de comentário ou 'dim'.
F9 colocará um ponto de quebra na linha, o que torna execução parar lá como a macro está sendo executado.
Agora inicie a macro da maneira como você está acostumado. Ele será executado até atingir o ponto de interrupção. Como já foi feito, o editor irá aparecer - e estará no "modo de depuração"; isso faz com que mais opções no menu "Debug" sejam úteis (F9 acima é o mesmo que 'Toggle breakpoint' também no menu).
F8 é um atalho para 'Step into' que irá executar uma cláusula na macro por vez, também saltando para quaisquer chamadas de subrotina e funções que você definiu .
Como cada cláusula foi executada, você pode examinar as posições do cursor da planilha do Excel e as variáveis na sua macro.
Você também pode adicionar um 'Watch' em qualquer variável para ter seu conteúdo visível enquanto estiver debugando.
Boa sorte!