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.

Function RB(ByVal Code As String) As String

' Define variables first

    Dim i As Integer  ' loop counter
 
' Go through the Code string character by character
' copying only the numbers to RB
  
  For i = 1 To Len(Code)
  
    If IsNumber(Value(Mid(Code, i, 1))) Then
       RB = RB + Mid(Code, i, 1)
    End If
  
  Next i
  
  
End Function

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

If IsNumeric(Value(Mid(Code, i, 1))) Then

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

Function RB(ByVal Code As String) As String
    Dim i As Integer
    
  For i = 1 To Len(Code)
    If IsNumeric(Mid(Code, i, 1)) Then
       RB = RB + Mid(Code, i, 1)
    End If
  Next i
  
End Function

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:

=MID(A2,2,LEN(A2)-2)

So as a UDF, but why?

Function RB(ByVal Code As String) As String
    RB = Mid(Code, 2, Len(Code) - 2)
End Function

**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.