UDF in Excel 2007

[welshgasman]

Hi everyone,


Would appreciate a little help if possible please.


I was trying to help a colleague in work collating some data.
The sheet is a copy of all bank statements including name address and other irrelevant data (to us).


The data he required all had a valid date in column B, so I created a udf as below

Public Function IsADate(rngCell As Range) As Boolean
If IsDate(rngCell) Then
    IsADate = True
Else
    IsADate = False
End If


End Function

to use the Isdate VBA function.


It worked great.


Just before finishing time he came over and told me it no longer worked on another sheet in the same workbook.


I checked, the function was still in a module in that workbook, and if you keyed it in on a sheet, it came up in the list of functions presented, yet after completing the formula the cell contains #NAME. In the little time possible I was unable to find the cause.


Does anyone have an idea what has gone wrong. He did rename the workbook, but is using the same workbook.


As an aside, I tried to make the function global in my setup by


a. Putting it in Personal.xlsm where I have various macros I use in all my workbooks
b. As an addin, saving as a .xla and .xlam file, and selecting the file in addins


It was still not recognised, did not even come up on the function list.


I've come home and tried option b in Excel 2003 (which is all I have) and it works fine in another workbook.


Puzzled to say the least. :D


TIA

 

[BiocideJ]

If you place it in your Personal.xlsb workbook you need to call the UDF as
=Personal.xlsb!IsADate(A1)

This is probably the easiest route to go although the same convention would be true for the name of the .xla/.xlam file I believe.

 

[welshgasman]

If you place it in your Personal.xlsb workbook you need to call the UDF as
=Personal.xlsb!IsADate(A1)

This is probably the easiest route to go although the same convention would be true for the name of the .xla/.xlam file I believe.

Thank you. I'll try that on Monday when I have time. The subroutines work fine in my Personal file.

I have now written a macro to help him do the work a lot easier. We'll just see if they will allow it's use.

 

[BiocideJ]

We'll just see if they will allow it's use.

In my experience, if you can demonstrate both the accuracy and efficiency of macros well, you can usually push through any resistance. They key is to make sure you have the accuracy part ironed out well before showcasing them.

 

[welshgasman]

Unfortunately they do not think like that where I work. I wrote a small vbs program to open all the required programs, websites each morning. One of the team leaders *thought* it would be good to use. I was asked several questions about it, one of which was 'Does it contain any computer code'.

He used it anyway and did all the work in about 2 hours. Friday it took him and another person all their working day.

I didn't get a chance to try using the filename as well, targets to meet. Will try and have a go tomorrow, if I remember.

Thanks for posting.

 

[BiocideJ]

One of my earliest jobs was at a call center and I had a similar experience where managers were 'scared' to endorse something they didn't understand. Eventually, the productivity difference was noticed though, ironically, by a third-party contractor that was hired to improve productivity within our department. Eventually, minds were changed, but it is a slow process.

I did have to laugh at the 'does it contain any computer code' question though. Apparently, they are unaware that EVERYTHING involving a computer 'contains computer code'.

 

[welshgasman]

I felt like telling them 'No, I wrote the computer a letter asking it to do these things just like someone writes to Santa'. That way anyone can 'program' a computer.