CODEWITHSUNDEEP
excelvba
Nerd in Training
Nerd in Training

Reputation: 2220

Call my function

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?

Upvotes: 1

Views: 596

Answers (2)

Brad
Brad

Reputation: 12245

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)

Upvotes: 2

Gojira
Gojira

Reputation: 3041

A few things...

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

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

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

Upvotes: 0

Related Questions