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
aws148Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

StephenJRCommented:
I think you have to include a reference, e.g. =personal.xls!CtoF(...).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
StephenJRCommented:
Or you could put the UDFs in an add-in.
0
aws148Author Commented:
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
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

aws148Author Commented:
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
0
StephenJRCommented:
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?
0
aws148Author Commented:
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
0
aws148Author Commented:
very grateful, thanks
0
StephenJRCommented:
OK. By the way you can use a formula if you are trying to find the last non-zero value in a column.
0
aws148Author Commented:
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
0
StephenJRCommented:
Try this formula:

=LOOKUP(2,1/(T:T<>0),T:T)
0
aws148Author Commented:
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
0
StephenJRCommented:
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

0
aws148Author Commented:
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
0
StephenJRCommented:
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!
0
aws148Author Commented:
Yes, OK.  Thanks all your help and sorry to detain you

Tony
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.