VBA Function Returning #VALUE! Error

**GazB** · 01-21-2013, 08:09 PM

Hi All,

Could some help me with the below code please?

If I the following macro, it returns the value as expected (an integer).

Please Login or Register  to view this content.

But it I call it within a worksheet, it throws the #VALUE! error.

Formula:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Thanks
Gareth

**domfootwear** · 01-21-2013, 09:03 PM

What is BCIS_DB, this is the name range or worksheet name? If it is worksheet name, you have to change from BCIS_DB to [BCIS_DB$]

**TMS** · 01-21-2013, 09:05 PM

My understanding has always been that a UDF used as a worksheet function can only return a value, it cannot make changes to the structure or content of other cells. You try to put something in a cell, hence the issue.

Regards, TMS

**GazB** · 01-22-2013, 03:29 PM

Thanks for the response guys'!

Originally Posted by domfootwear

What is BCIS_DB, this is the name range or worksheet name? If it is worksheet name, you have to change from BCIS_DB to [BCIS_DB$]

It's a name range.

Originally Posted by TMShucks

My understanding has always been that a UDF used as a worksheet function can only return a value, it cannot make changes to the structure or content of other cells. You try to put something in a cell, hence the issue.

Thanks, I removed it from the function and when using it now in the worksheet, it calculates the amount. The problem is then that it repeats the procedure up until the execute command two or three times for some reason?! Once finished, it then throws the #VALUE! error again?!

Thanks
Gareth

**TMS** · 01-22-2013, 04:21 PM

Can you post the sample workbook with the code in place. I'd like to step through the code to understand what is happening in relation to the data.

Two steps forward, one back ... ?

Regards, TMS

**GazB** · 01-22-2013, 04:26 PM

Thanks TMS.

Just to note, removing the part of the VBA that amended the cell certainly appears to have rid the problem. I have a feeling that it's infact something to do with the worksheet_change procedure that's being used throughout the sheet. Should I start a s new question as I don't think it's related now?

Thanks
Gareth

**TMS** · 01-22-2013, 04:35 PM

OK, if you have a worksheet change event and, in that event handler, you make (more) changes to the worksheet, it will cause the event to fire again, and possibly, again.

If/where you are making changes, you need:

Please Login or Register  to view this content.

Regards, TMS

**GazB** · 01-22-2013, 04:39 PM

Ok thanks, when I have access to the workbook again, I'll have a go.

Many thanks
Gareth

**GazB** · 01-23-2013, 04:16 PM

Example.xlsm

Right so I've had a look and it looks like it was trying to call an event whilst enableevents was false. I've attached an example of the workbook, any ideas please as it still doesn't refresh?

Thanks
Gareth