Precisa ser em VBA ? Quando diz que "pretende introduzir numa célula do excel a data do ultimo dia do mes em questão" seria atravez de uma rotina ou uma Function ?

Enquanto aguardamos, de uma olhada no link abaixo, tem até um modelo para baixar :
RETORNA O ÚLTIMO DIA DO MÊS VBA EXCEL
http://guiadoexcel.com.br/retorna-o-ultimo-dia-do-mes

Aqui não temos como baixar, mas a function é esta :

'Função que retorna o último dia do mês
Function gfUltimoDiaMes(ByVal vCel As String) As Date
    Dim lData As Date 'Dia do mês subsequente
    
    If Month(vCel) < 12 Then
        lData = "1/" & (Month(vCel) + 1) & "/" & Year(vCel)
    
    Else
    
        lData = "1/1" & "/" & Year(vCel) + 1
        
    End If
    
    'Dia do mês subsequente -1, ou seja, último dia do mês anterior
    lData = lData - 1
    
    'Retorna informação
    gfUltimoDiaMes = lData
    
End Function

A chamada da função =gfUltimoDiaMes(A1), sendo A1 = 01/02/2012 por exemplo e o retorno será 29/2/2012
A chamada da função =gfUltimoDiaMes(A1), sendo A1 = 01/02/2013 por exemplo e o retorno será 28/2/2013

Qual a aplicação exata que você deseja e como estão os dados?
Minha sugestão é utilizar a função nativa FIMMÊS. Veja o modelo na planilha anexa.

Se quiser fazer com formula =fimmes(hoje();0)

Usa a função abaixo, caso não preciso do VBA

=DATA(ANO(HOJE());MÊS(HOJE())+1;1)-1