#value! error in custom functions

**juergen(barcelona)** · 09-16-2010, 06:34 AM

I have created a excelsheet with lots of custom functions.

Everything worked perfect but suddenly all the cells with custom functions display #value!.

When I go to the cells and press enter (maintaining the custom formula), the result is again displayed correctly.

How can I avoid this and what is the reason for this to happen?

Thanks!

**Andrew-R** · 09-16-2010, 06:41 AM

Did you open the sheet with macros disabled, maybe?

**juergen(barcelona)** · 09-16-2010, 06:45 AM

No, macros are enabled. Anyways, if I go to the cell and press enter, the #value message dissappears and the result is displayed. But I don't want to do this manually for all the cells and it's a sheet for customers, so I don't want this to happen again in the future !!

**romperstomper** · 09-16-2010, 06:45 AM

What's the code for the functions? (does it refer to Activecell or activesheet for example? that's a big no-no.)

If you had macros disabled, you should get a #NAME error, rather than #VALUE.

**juergen(barcelona)** · 09-16-2010, 06:50 AM

Phhuuuuu,

there are plenty of formulas, but basically they just do calculations picking some values from other worksheets, but I don't use Activecell / Activesheet.

**juergen(barcelona)** · 09-16-2010, 06:51 AM

example:

Function g(num)
g = Worksheets("Materiales").Cells(14 + num, 3).Value
End Function

**romperstomper** · 09-16-2010, 06:58 AM

It is never a good idea to hardcode worksheet names in UDFs. You would also need to make that formula volatile for it to react to changes on the other sheet.