0

I have a UDF that looks for special characters in a cell (whose value I pass in) and return a BOOLEAN (true if some characters are found).

I was able to test it from a worksheet passing it a direct reference to a cell value.

I was also able to successfully pass an INDIRECT reference to validate the contents of a cell in another Workbook/Worksheet .

I'm trying to call this UDF from another UDF that iterates through a range and call the first UDF. The goal is to find if any one of the cells in the range has any of the special characters in it, and if so, return a boolean.

Testing the VBA module works ok.

But when I call it from excel spreadsheet, it gives me a #VALUE! error

Here is the code for the two functions.

Function SpCharsInCellRange() As Boolean

Dim WrkBookSrs As Workbook
Dim WrkSheetSrs As Worksheet
Dim MyRange As Range
Dim ii As Integer
Dim result As Boolean
SpCharsInCellRange = False

    
     Workbooks("MySpreadsheet.xlsm").Activate
     Worksheets("MyWorsheet").Activate
     
Set MyRange = Range("E20:E29")

    ii = 20
    
    While Len(Range("E" & CStr(ii)).Value) > 0
    
       MsgBox Len(Range("E" & CStr(ii)).Value)
        MsgBox ContainsSpecialCharacters(Range("E" & CStr(ii)).Value)
        
            If ContainsSpecialCharacters(Range("E" & CStr(ii)).Value) Then
               SpCharsInCellRange = True
        End If
        ii = ii + 1
     Wend

MsgBox SpCharsInCellRange
End Function

Function ContainsSpecialCharacters(str As String) As Boolean
For I = 1 To Len(str)
 ch = Mid(str, I, 1)
 Select Case ch
  Case Chr(64)
   ContainsSpecialCharacters = True
   Exit For
   Case Chr(34)
   ContainsSpecialCharacters = True
   Exit For
   Case Chr(38)
   ContainsSpecialCharacters = True
   Exit For
   Case Chr(39)
   ContainsSpecialCharacters = True
   Exit For
   Case Chr(10)
   ContainsSpecialCharacters = True
   Exit For
   Case Chr(13)
   ContainsSpecialCharacters = True
   Exit For
   Case "0" To "9", "A" To "Z", "a" To "z", " "
   ContainsSpecialCharacters = False
   Case Else
   ContainsSpecialCharacters = False
 End Select
Next
End Function
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
Josh Tyler
  • 1
  • 2
  • 1
    You cannot `.Activate` worksheets in a UDF called from the worksheet. – Scott Craner Sep 29 '22 at 16:11
  • See: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba for avoiding using `.Activate` and `.Select` – Scott Craner Sep 29 '22 at 16:12
  • Your next problem is going to be that since you are not passing the range as an argument to the UDF it will not recalc if there is a change to the range automatically. – Scott Craner Sep 29 '22 at 16:13

0 Answers0