Call my function

Question

I have written a function in VBA and I have no idea how to call this.

Public Function FindTwoStrings(rng As Range, s1 As String, _
  s2 As String) As Integer
    'Application.Volatile
    If TypeName(rng) <> "Range" Then Exit Function
    Dim cell As Range
    Dim accumulator As Integer
    For Each cell In rng.Cells
        If (InStr(1, UCase(cell.Value), UCase(s1), _
          vbTextCompare) > 0) Or (InStr(1, UCase(cell.Value), _
          UCase(s2), vbTextCompare) > 0) Then _
          accumulator = Me.Cells(cell.Row, 5) + accumulator
    Next cell
End Function

How can I call this function in my cell? I have tried to do =Find.... but it cannot find my function. Is there a setting I'm missing?

and in my cell I wanna call it in order to provide the parameters — Nerd in Training
– Nerd in Training, Commented Apr 8, 2013 at 19:36
Paste this line FindTwoStrings = accumulator just before End Function and move the entire code to a module. — Siddharth Rout
– Siddharth Rout, Commented Apr 8, 2013 at 19:37

Brad · Accepted Answer · 2013-04-09 13:03:24Z

2

You need to put user defined function in Modules. Not in Classes or in objects like forms, worksheets, or Thisworkbook.

And you need to set a return value for your function before it exits otherwise it will always have the default value of 0.

You might need to rethink your line

accumulator = Me.Cells(cell.Row, 5) + accumulator

~~because you cannot access anything other than what was passed into a UDF. So references to Me will not be allowed.~~

Edit: You cannot access objects using the Me keyword in a module (where a UDF is required to reside) because Me refers to the non-static object in which the code resides, but modules are static this Me is illogical. As Chris points out in the comments you are allowed to access objects that were not passed in but instead of doing so through the Me keyword you need to reference them explicitly such as ThisWoorkBook.Sheets("sheetname").Cells(cell.row,5)

edited Apr 9, 2013 at 13:03

answered Apr 8, 2013 at 19:43

Brad

12.4k4 gold badges51 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

chris neilsen Over a year ago

Your explanation of why Me is a problem isn't quite right. A UDF can access pretty much anything, it just can't change anything. The issue is that in a Module, Me has no meaning and doesn't exist.

Brad Over a year ago

@chrisneilsen You're right about the modifying state issue. So yes, if the OP wants to refer to those cells he'll need to do so explicitly through Sheets("SheetName").Cells(Cell.row,5). Modules are static and Me is only relevant to non-static objects. I'll change what I wrote

Gojira · Accepted Answer · 2013-04-08 19:47:25Z

A few things...

An Excel custom function (the kind you're trying to create where it shows up as an available keyword in the cell's formula) can only change the value of one cell. You'll note that all of the built-in Excel functions (e.g., =LEFT) only affect the value of the cell that they're used in.
The method signature that Excel is looking for is a range... I don't know if it will accept multiple parameters as in your example.
It doesn't look like you are setting the value of FindTwoStrings anywhere. That's how the method knows what value to return.

Following is an example of a little function I wrote to concatenate a range into a single string delimited by commas. You call it with =cvsRange( [ some range ])

Function csvRange(myRange As Range)
    Dim csvRangeOutput As String
    For Each entry In myRange
      If Len(entry) > 0 Then
        csvRangeOutput = csvRangeOutput & entry.Value & ","
      End If
    Next
    If Len(csvRangeOutput) > 2 Then
         csvRange = Mid(csvRangeOutput, 1, Len(csvRangeOutput) - 1)
     Else
         csvRange = ""

      End If

End Function

HTH...

Collectives™ on Stack Overflow

Call my function

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related