I have a function as follows in my Excel module:

Function LASTINCOLUMN(rngInput As Range)
Dim WorkRange As Range
Dim i As Integer, CellCount As Integer
Application.Volatile
Set WorkRange = rngInput.Columns(1).EntireColumn
Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange)
CellCount = WorkRange.Count
For i = CellCount To 1 Step -1
If Not IsEmpty(WorkRange(i)) Then
LASTINCOLUMN = WorkRange(i).Row
Exit Function
End If
Next i
End Function



If I call this function an Excel worksheet with the formula
"=lastincolumn(sheet1!c:c)" it works!

However, this doesnt work when I call this function from inside VBA.

for instance when I say x = lastincolumn("Sheet1!c:c")

Excel throws an error msg saying Type Mismatch

How do I call this function within VBA and assign this to a variable?

Please help!