User define function

L

Lee

Public Function leesort(s As String) As String
Dim c As Integer
Dim d As Integer
Dim temp As String
Dim ss(1000) As String

For c = 1 To Len(s)
ss(c) = Mid(s, c, 1)
Next c
For c = 1 To Len(s)
For d = 1 To Len(s) - 1
If ss(d) > ss(d + 1) Then
temp = ss(d + 1)
ss(d + 1) = ss(d)
ss(d) = temp
End If
Next d
Next c

leesort = ""
For c = 1 To Len(s)
leesort = leesort + ss(c)
Next c


End Function
 
D

Dave Peterson

You mean a new workbook?

Lots of people put this kind of thing in their personal.xls and store that file
in their XLStart folder. (But you don't need to.) But by having the file in
the XLStart folder, then excel will open it each time it starts--so those
functions are always available.

In any case, you'll have to have the workbook with the function open to use it
in other workbooks.

Then you can use it like this:
=personal.xls!leesort(a1)

Another option is to save that personal file as an addin (I use personal.xla)
and keep it in your XLStart folder.

Then you can use this:
=leesort(a1)

Just like a function built into excel.

====
Just a warning note.

If you're going to share this workbook with this function with others, don't
call it personal.xls (or personal.xla). The other users may have stuff in their
own personal.xl* files and need their files open instead.

Call your workbook:

LeeUtils.xls
or
LeeUtils.xla

Then tell your users to store that file in a specific folder (C:\ExcelUtils,
say). Then you'll be able to share files that use these functions with no
problem.

If you end up using an addin, tell the users to install the addin via:
tools|addins|browse to
c:\ExcelUtils
and turn it on.
 
G

Gord Dibben

You can place it in your Personal Macro Workbook, more commonly known as
Personal.xls

When stored in the XLSTART folder, Personal.xls will open whenever you start
Excel.

You can save it "hidden" so it stays in the background with your macros and
functions available for all open workbooks.

When using Personal.xls and assigning macros to buttons you have to precede the
macroname with the filename.

i.e. Personal.xls!mymacro

Same thing for User Defined Functions

=Persoanl.xls!funcname(args)

If you store all macros and UDF's in an Add-in(*.xla) you don't need the
filename.

=funcname(args) is sufficient.


Gord Dibben MS Excel MVP
 
Top