Autosize cell after return of function

Question

I have an xla with a bunch of functions. Basically each of these functions perform a vertical lookup on another worksheet. Here's a basic example of one of them:

Function ax2Project_Address(projectnumber)
If (projectnumber = "?") Then
    ax2Project_Address = "Unknown address"
Else
    ax2Project_Address = Application.VLookup(projectnumber, Sheets("AX Data").Range("A:K"), 11, False)
End Function

This allows users to simply call the function with the project number on the worksheet and it gives them the address, without having to write the vertical lookup themselves.
What I want to do now is have the row of the current cell to autofit its height to its content. The problem is that this obviously should be done after the cell has been filled with the adress, but since the function ends after the return, the autofit command won't be executed.

Is there a way to execute the autofit command in a function after the return?
Or is there a completely different approach I could use?

Answer 1

Functions cannot change anything in excel except for the cell it's called in. That being said, I'm pretty sure a function can trigger a routine -

Option Explicit

Public Function eieio(numb As Long) As Long
    eieio = Int(numb / 2)
    Test
End Function


Sub Test()
MsgBox ("hi")
End Sub

eieio(12) = 6 and msgbox "hi"

That being said, what you can't do is something like this -

Option Explicit

Public Function eieio(numb As Long) As Long
    eieio = Int(numb / 2)
    Test
End Function


Sub Test()
Dim str As String
str = Application.Caller.Address
Dim rng As Range
Set rng = Range(str)
MsgBox (rng.Address)
rng.EntireRow.AutoFit
End Sub

So the routine can't change the application.caller as far as I know. It's always been like that.

Answer 2

If you don't want to autofit the entire sheet use this in the Worksheet code module:

Private Sub Worksheet_Calculate()
    Dim a As Range
    For Each a In Me.UsedRange
        If a.Formula Like "=ax2Project_Address(*" Then a.EntireRow.AutoFit
    Next a

End Sub