User Defined Function in Excel VBA not recognised

[Phil Davis]

Hi, I have been quite happily adding a series of UDFs to my collection in PERSONAL.XLS until today when for no apparent reason any new routine I add does not seem to get picked up by a call, either from within (PERSONAL.xls) or any other workbook, from a cell or from within a vba subroutine.

No error is given when called from a cell, the call is simply treated as a literal string in the cell used. For example calling my new UDF substr, instead of returning the desired result appears in the cell as =substr(A1, 1, 3). However when called from a subroutine I get "Sub or Function not defined"

All previously defined UDFs continue to work OK. If I copy an old working function and paste it into a new module, changing the function name (on all occurances) the new version gets the same problem. thus I think there must be some kind of registration failure?

I'm now folically challenged! Can anybody suggest what I'm missing here please?

[Phil Davis]

Have now found a work around!

Rename PERSONAL.xls, create a new one, copy each module from old to new. New functions now work!!

Slightly unsatisfactory solution, I wouldn't want to do that each time the problem arose if I had many more modules. However possibly not worth wasting any brain cycles unless you're as intrigued as I am as to what the root cause of the problem is!

Best Rgds, Phil

[Killer42]

Glad to see you found a solution (alright, a workaround) and thanks for sharing it with us.

I'm no Excel expert (I've dabbled a bit) so don't know exactly what the problem is. But in case anyone else does, I think it'd be a good idea to mention what version of Excel you're using.

By the way, why a substring function? I thought the Mid() function already provided that.

[Phil Davis]

Hi Killer42, I'm using Excel 2002 on Win 7 - I need to get full value!!

Regarding SUBSTR - yes that was one of the easy ones - just pass the arguments to MID() - I'm writing a series of functions in VBA to mirror REXX - a language I know inside out (from my IBM mainframe background). It gives me something to do and makes me productive.

All the best, Phil

[Killer42]

Ah, so it was written for my favourite reason... "because I can". :-)

[Phil Davis]

In one sense yes - keeps the grey cells ticking over. I also have REXX syntax etdched on my brain while I find VBA function names not quite so memorable. Not to be recommended in a business environment however!!