Solved Trying to create a VBA user defined function.

April 6, 2013 at 23:22:53
Specs: Windows 7
The function is pretty straight forward, it reads a text string and extracts the specified character or characters. I have it working as a formula in individual cells but would rather have the function so I can use it easily, and where needed including other workbooks.

It gives a #Value error and I don't know why?

Sample input string "Each M from 6pm to 7:30pm"
Extract the "M" for Monday, "Tu" for Tuesday, "Sa" for Saturday, etc

Public Function getday(eachDay As String) As String
    Dim lenIndex As Integer
    Dim dow As String
    
'   IF(FIND("Each ",X2,1),MID(X2,6,IF(FIND("from",X2,1)=9,2,1)))

If (WorksheetFunction.Find("from", eachDay, 1) = 9) Then
        lenIndex = 2
    Else
        lenIndex = 1
End If
    
If (WorksheetFunction.Find("Each", eachDay, 1)) Then
        dow = WorksheetFunction.Mid(eachDay, 6, lenIndex)
    Else
        dow = ""
End If

getday = dow
        
End Function


See More: Trying to create a VBA user defined function.

Report •

#1
April 7, 2013 at 07:21:15
✔ Best Answer
Your problem is that WorksheetFunction.Find Is not the Find you think it is. The most direct function would be VBA's InStr. Still, from your description, there are easier ways to go about what you're attempting:
Public Function getday(eachDay As String) As String
  Dim regex, matches
  Set regex = CreateObject("VBScript.RegExp")

  regex.Pattern = "\b(M|Tu|W|Th|F|Sa|Su)\b"
  Set matches = regex.Execute(eachDay)
  If matches.Count Then _
    getday = matches(0).Value
End Function

How To Ask Questions The Smart Way


Report •

#2
April 7, 2013 at 11:32:51
Thank You ! This works great, I'll have to learn regular expressions next.

Report •
Related Solutions


Ask Question