User defined function returns an error on a standard function used in it.

**pb48** · 01-22-2013, 03:24 AM

I am new to this site and I am looking for help. I have programmed in VB before but not in applications such as Excel.

I have this code in a VBA module attached to a sheet.

Please Login or Register  to view this content.

This is supposed to remove brackets around variable length numeric string data but when run from a cell "=RB(B2)", where B2 contains the string it produces a compiler error pointing to the word "Value" and saying Sub or Function not defined.

The string would start off like this "[1234567891234]" from a point of sale terminal device database as a reference to a bar code and I need to convert this to a printable bar code. I have another function that generates the printer codes and a font that will print bar codes from the printer codes but I am having trouble with removing the brackets from the database data. I thought I would use another function but this error has cropped up. I know this can be done in other ways but the error is a problematic (annoying).

What would cause this error?

Thanks in advance and I hope I have presented it in accordance with the rules.

**AB33** · 01-22-2013, 03:49 AM

There may be a number of reasons for the error, but I thought this line should be

Please Login or Register  to view this content.

Change the number to "Numeric"

It is also assumed that you have a range of strings on one of your columns

**Marcol** · 01-22-2013, 04:01 AM

Try this

Please Login or Register  to view this content.

ISNUMBER() is an EXCEL function, it only evaluates numbers
IsNumeric() is VBa, it evaluates both numbers and strings

Value is not a VBa function, the function Evaluate() is the one to use, however it isn't required in this instance.

Consider this standard Excel formula

Formula:

Please Login or Register  to view this content.

So as a UDF, but why?

Please Login or Register  to view this content.

**jbg** · 01-22-2013, 04:06 AM

Hi,

Please allow me the following correction to Marcol's formula:
If IsNumeric(Mid(Code, i, 1)) Then
RB = RB + Value(Mid(Code, i, 1))
End If

With all due respect Marcol...

**jbg** · 01-22-2013, 04:07 AM

Hi,

Please allow me the following correction to Marcol's formula:
If IsNumeric(Mid(Code, i, 1)) Then
RB = RB + Value(Mid(Code, i, 1))
End If

With all due respect Marcol...

**Marcol** · 01-22-2013, 04:22 AM

Originally Posted by jbg

Hi,

Please allow me the following correction to Marcol's formula:
...

You might have missed my edited post ...

**jbg** · 01-23-2013, 09:40 AM

Probably... Mea culpa

**pb48** · 01-23-2013, 01:35 PM

Thanks to everybody, I have learned that VBA functions and Excel functions are not the same. I have found the VBA reference but I am surprised that I could not get it to show up through help. I have a lot to learn. I will use the formula solution Marcol, thanks. Thanks again for the rapid response.