Calling a function from a User Defined Add-In

[garybug]

My spreadsheet sources data from the US, which includes US formatted dates (mm/dd/yyyy). We have a CorrectDate function (e.g. CorrectDate (F4) ) which comes as an Addin to change this to a UK format (dd/mm/yyyy), but I want to add a button to the toolbar so that when a cell is highlighted, & the button clicked, a Macro runs to automatically re-format this.

I tried using

ActiveCell.Value = CorrectDate(ActiveCell.Name)

where ActiveCell.Name is the cell highlighted e.g. A2

Ta

Gary

 

[SkipVought]

Hey Gary -- across the pond ;-)

Why bother with a special function. A date is a date. Today is 37896.

I can Format/Cells/Number - and select UK or US date format conventions -- OR even make up my own.

Just format the selection dd/mm/yyyy!

I M perplexed????

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[garybug]

Well, I agree! But I suppose there are some lazy types here for whom its One Click Good, Four Clicks Bad, and he who pays the Piper etc.. Wouldstill like to find out the Syntax for differentiating between calling a 'function' coded in VBA, and one that is included within an Add In.

Ta, G

 

[SkipVought]

A function returns a value.

The VALUE of the date does NOT have to be changed.

Now if you want a subroutine run by a button that does the FORMATTING, I can see that.

But a function does nothing to an Excel date. Today is 37896 both here and accross the pond.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[TonyJollans]

Hi garybug,

Skip is absolutely correct, a date is a date (unless it's explicitly entered as a string) and I would be very intested to know what your function does.

Apart from that, it sounds like a recipe for disaster having some dates formatted as dd/mm/yyyy and some as mm/dd/yyyy. Although it would be possible to differentiate them by checking the cell formats, your Users who don't want to make one click more than absolutely necessary aren't going to do that. Either do them all as a one-time single operation - or none at all.

Enjoy,
Tony