UDF in Excel 2007

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
871
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
Code:
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
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

BiocideJ

Well-known Member
Joined
Jan 23, 2012
Messages
1,733
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

Well-known Member
Joined
May 25, 2013
Messages
871
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. :LOL:
 

BiocideJ

Well-known Member
Joined
Jan 23, 2012
Messages
1,733
We'll just see if they will allow it's use. :LOL:


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

Well-known Member
Joined
May 25, 2013
Messages
871

ADVERTISEMENT

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'.:LOL:

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

Well-known Member
Joined
Jan 23, 2012
Messages
1,733
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

Well-known Member
Joined
May 25, 2013
Messages
871
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.:LOL:
 

Watch MrExcel Video

Forum statistics

Threads
1,122,564
Messages
5,596,875
Members
414,106
Latest member
Tigretto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top