asked on

Can I locate UDFs in personal.xls?

I use Excel 2002

I run many VBA procedures - all sub procedures in fact - for my business. Because I need certainty of backup, and cannot control multiple versions of the same code, I place all these into general modules in Personal.xls. My Security is set to Low, and all the VBA procedures work from whichever spreadsheet I call them.

I need to start working with User Defined Functions. I have used a one line textbook Fahrenheit to Centigrade Function as a tester.

Function CtoF(Centigrade)
CtoF = Centigrade * 9 / 5 + 32
End Function

By experimentation I have discovered that I can only get this Function to work if I place it in a general module in the particular spreadsheet from which I am calling it.

I have not yet been able to get this or any other Function to work if it is placed in Personal.xls. I have tried calling it a Public Function, but have seen no difference in result - which is always the #Name error with the message saying that the function name is unrecognised text.

Could anyone guide me to a way of getting a UD Function to work in any spreadsheet by calling it only from a module in Personal.xls?

Thanks
Tony

ASKER CERTIFIED SOLUTION

StephenJR

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

StephenJR

Or you could put the UDFs in an add-in.

aws148

ASKER

preliminary tests are showing that including a reference to personal.xls does get the UDFs working from that file. Thanks very much.

I'm just going to pause overnight to double check myself before closing the question.

regards
Tony

aws148

ASKER

I've been working with the UDF and have concluded that your suggestion of referencing them to personal.xls ought to be the full answer. Such a reference either works or it doesn't, it seems to me. And it definitely works at least most of the time, so it works, is my conclusion.

But not without some doubt because from time to time, unpredicatbely, and without obvious pattern or causation, the cells calling the UDF have recalculated to some very odd, and usually very small number. Do you have any insight as to why this is happening?

Here is the UDF I've been testing with - it returns the value of the last cell in the input column.

Function LastRVal(Col As String)
Dim Rws As Long
Application.Volatile

For Rws = Cells(65536, Col).End(xlUp).Row To 1 Step -1
If Cells(Rws, Col).Value <> "" And Cells(Rws, Col).Value <> 0 Then
LastRVal = Cells(Rws, Col).Value
Exit For
End If
Next Rws
End Function

Thanks
Tony

StephenJR

Tony - it sounds a bit odd and I'm not sure it can have anything to do with the referencing, but I don't know. Could you post a sample workbook which illustrates this?

aws148

ASKER

Thanks, I just needed to know whether there was something staring out, and there clearly isn't.

I'm working intensively developing a new trading worksheet, and there's any number of odd things that I might be doing myself while I'm working on it. Operator error, especially as this is the first time I've worked with UDFs, is a likely candidate.

So I'll close the question, if I may, and if I continue to have this problem I'll try and establish the circumstances and results with a bit more precision and open a new question.

Thanks very much for your help.
Tony

aws148

ASKER

very grateful, thanks

StephenJR

OK. By the way you can use a formula if you are trying to find the last non-zero value in a column.

aws148

ASKER

Your reference to using a formula took me by surprise? I'd love to. What formula? Figures are in Col T, starting at T16 and going on down an indeterminate length.

Tony

StephenJR

Try this formula:

=LOOKUP(2,1/(T:T<>0),T:T)

aws148

ASKER

Thanks. I'm working on Excel 2002, so can't use T:T, and I think this causes problems with the formula in this format. In Excel 2002 I need to enter a more precise formulation of the target range, but that's a problem because it constantly varies.

If I enter the formula =LOOKUP(2,1/(T16:T2000<>0),T16:T2000) on the basis that rows are always going to be <2000, I get a blank cell as the result. It's as if I'd formatted the cell with ;;; but I deduce that it's actually returning the lowest cell's value, which is "" produced by an IF formula in that cell.

On my sample sheet T16:T57 is the precise range in question, and using that range in the formula gets the correct result.

Is there any way of getting round this?
Tony

StephenJR

Perhaps we can combine the two methods, viz:

Function LastRVal(col As String)

Dim r As Range

Application.Volatile

Set r = Range(Cells(16, col), Cells(Rows.Count, col).End(xlUp))
LastRVal = Evaluate("=Lookup(2, 1 / (" & r.Address & "<> 0), " & r.Address & ")")

End Function

Open in new window

aws148

ASKER

Thanks. Very interesting and you've really advanced my understanding of a number of possibilities.

Bottom line is that this returns me to using a Function, which I was trying to avoid by using a formula.

I expect the more sensible solution is for me finally to upgrade out of Excel 2002. I've not done so because I work long hours, 2002 pretty much does what I need, and I fear having to get used to a new user interface, and worry whether my old VBA procedures are running correctly in the new environment. I'm on VBA 6.5 which must be well out of date by now.

Do you think those sound like sensible points or should everything work fine if I upgrade to the latest MS Office and get the latest Excel?
Tony

StephenJR

You will need to adjust, but it might be worth updating to at least xl 2007, though there is always an argument for going for the most up to date - xl 2010 I think now, hard to keep up - if you can afford it. You could ask another question if you want more detailed responses, and more expertise - I'm not really the best person to ask!

aws148

ASKER

Yes, OK. Thanks all your help and sorry to detain you

Tony