MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Im Stuck VBA UDF Formatting


Posted by Jack in UK on December 22, 2001 4:05 AM

Sorry i know i should know but ive lost it, need from 1 2 3 4 5
A0001 A0002 A0003

Funtion will be AddA() wiich offet one to right and returns, i really cant remember
Many thanks
Code is

Function AddA(Offset_Cols_by, _
Offset_Rows_by As Integer) As Variant
Dim MyFormat
MyFormat = "A####"

AddA = Format(MyFormat & Application.Caller.Offset _
(Offset_Rows_by, Offset_Cols_by).Value)

End Function


Posted by Gary on December 22, 2001 12:21 PM

Change to

AddA = Format(Application.Caller.Offset _
(Offset_Rows_by, Offset_Cols_by).Value,MyFormat)

You should have Application.Volatile in the function too - unless you pass the range to be offsetted from as an argument. The latter is more efficient because otherwise Excel will have to recalculate the UDF when anything on the sheet changes.

Gary

Posted by Jack in UK on December 22, 2001 1:19 PM

Gary

Gary,
I see and read your postes, you work well and at a high level, please except my great thanks, thats PERFECTION.

Thankyou, and ive learnt a bit too:

I never though i would get an answer as i beleived it was one of them funny ones. Cheers mate!