CODEWITHSUNDEEP
excelvba
user1971598
user1971598

Reputation:

Using VBA to make function, not sure what's wrong

Say I have a column of entries where the entries are grades, say for this example 4th grade. I only need to have the numerical value. 

Function CleanCode(entry) As Integer
    If entry Like "[4]" Then entry = 4
End Function

why doesn't this function do it?

Upvotes: 0

Views: 82

Answers (2)

brettdj
brettdj

Reputation: 55692

Use a to efficiently clean your string. To clean a string in A1, in B1 you could enter

=CleanString(A1)

Sub Tester()
     MsgBox CleanString("3d1fgd4g1dg5d9gdg")
End Sub

Function CleanString(strIn As String) As String
    Dim objRegex
    Set objRegex = CreateObject("vbscript.regexp")
    With objRegex
     .Global = True
     .Pattern = "[^\d]+"
    CleanString = .Replace(strIn, vbNullString)
    End With
End Function

Upvotes: 0

ron tornambe
ron tornambe

Reputation: 10780

Firstly, to "clean" a string the starts with a numeric character, you use the "Val(" function. I think you are also expecting the "entry" parameter to be changed by the CleanCode function, however you must explicitly return the value.

Function CleanCode(entry) As Integer
    entry = Val(entry)
    If entry Like "[4]" Then
       entry = 4
    Else
       entry = -1
    End If
    CleanCode = entry
End Function

We can call this function from another function to see how it operates:

Sub CallCC()
  Dim entry As Variant
  entry = "9th"
  Dim Result As Integer
  Result = CleanCode(entry)
  MsgBox "Result = " & Result
  'Result is -1
  entry = "4th grade"
  Result = CleanCode(entry)
  MsgBox "Result = " & Result
  ' Result = 4

End Sub

Upvotes: 3

Related Questions