General Functions

L

Lynxbci3

Can you write functions that will be available for whatever workshee
you happen to open.

We have a date format from our AS/400 system and it gives us a lon
integer (1040701 = 1st Jul) I have a statement that changes this into
user friendly format (01/07/2004) this i would like to have as
function because i use it all the time.

Any ideas please.

Thank
 
B

Bob Phillips

You could put the macro in Personal.xls and add a button to a toolbar linked
to that macro. Probably the easiest way.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
J

Jerry W. Lewis

If you will be the only user of these functions, then use Personal.xls
as Bob suggested. If multiple users will share the functions, then put
them in a workbook, save the workbook, then save it as an Add-In (.xla).
Put the Add-In on a common drive and use Tools|Add-Ins and browse to
the .xla location to install on each users's computer. When asked
whether to copy to the library directory, answer "No", so that you can
update all users by simply overwritting the .xla at the shared location.

Jerry
 
J

Jim May

I recently added a User-defined-Function to my Personal.xls file.
When I'm ready to use it I can (of course) use it like any other function
(like Sum())
by entering it in a cell =sumcolor(A1:A10,6) << which totals only the
yellow cells.
but actually I think I have to enter it as =
personal.xls!sumcolor(A1:A10,6).
Is this normal expected behavior, or did I do something unnecessary to
create the additionally required personal.xls! part?
Thanks,
Jim May


Bob Phillips said:
You could put the macro in Personal.xls and add a button to a toolbar linked
to that macro. Probably the easiest way.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
Click to expand...
 
L

Lynxbci3

I don't appear to have a personal.xls, i know i should have, and i kno
i am being a bit blonde, but where the heck is it
 
B

Bob Phillips

You don't have one automatically, you have to create it. The simplest way is
to go into Tools>Macro>Record New macro and change the 'Store macro in'
value to Personal Macro Workbook. Just do nothing and stop the recorder,
then you have one. Save it in the XLStart directory and you are away.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
B

Bob Phillips

Jim,

No that is normal behaviour. If you call a function in another workbook, you
have to qualify it with workbook name. This is where an add-in comes in.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Jim May said:
I recently added a User-defined-Function to my Personal.xls file.
When I'm ready to use it I can (of course) use it like any other function
(like Sum())
by entering it in a cell =sumcolor(A1:A10,6) << which totals only the
yellow cells.
but actually I think I have to enter it as =
personal.xls!sumcolor(A1:A10,6).
Is this normal expected behavior, or did I do something unnecessary to
create the additionally required personal.xls! part?
Thanks,
Jim May
Click to expand...
 
D

Dave Peterson

I've saved my personal.xl* as an addin. Then I don't have to use the workbook
name in worksheet function calls.

But I do have to have a way to get to the Sub's. (The subs in addins aren't
visible via Tools|macro|macros.)

I used John Walkenbach's menumaker for my Subs.
http://j-walk.com/ss/excel/tips/tip53.htm

Another option would be to separate your functions from your subs:

MyFuncs.xla and mySubs.xls
(and put both in your XLStart folder -- and move personal.xl* to a nice safe
spot for backup.)

There's nothing really special about the name personal.xl*.


Jim said:
I recently added a User-defined-Function to my Personal.xls file.
When I'm ready to use it I can (of course) use it like any other function
(like Sum())
by entering it in a cell =sumcolor(A1:A10,6) << which totals only the
yellow cells.
but actually I think I have to enter it as =
personal.xls!sumcolor(A1:A10,6).
Is this normal expected behavior, or did I do something unnecessary to
create the additionally required personal.xls! part?
Thanks,
Jim May
Click to expand...
 
Top