CODEWITHSUNDEEP
excelvbaexcel-2010
NoErrorNoCry
NoErrorNoCry

Reputation: 75

Excel - Using IF and OR

![enter image description here

Here is my code as text:

 Function NurZahl(ByVal Text As String) As Long
    Dim i%, tmp
    Dim Val As String
    
    For i = 1 To Len(Text)
      Val = Mid(Text, i, 1)
        If(OR(IsNumeric(Val),Val=","),TRUE, FALSE) Then tmp = tmp & Mid(Text, i, 1)
    Next i
    NurZahl = tmp
End Function

Complete Beginner here:

Got the solution now with your help (thanks to everyone who replied) - I wanted to extract a number with decimal from a string:

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

Views: 104

Answers (2)

John Williams
John Williams

Reputation: 264

The "If" line is written like an Excel formula. This is what is should look like in basic.

If IsNumeric(Val) Or Val = "," Then tmp = tmp & Mid(Text, i, 1)

The red text is a syntax error. If you go to the Debug menu and click Compile VBA Project, you'll get the error message.

The link that you included is for functions that are typed into a cell. You need a VBA reference. Here's a link to MS's reference, but a decent book would make your life a lot easier. Just search for "Excel VBA".

https://learn.microsoft.com/en-us/office/vba/api/overview/

Upvotes: 1

zedfoxus
zedfoxus

Reputation: 37059

You can try something like this:

Function NurZahl (ByVal MyText As String) As Long

    ' Set up the variables
    Dim i as Integer
    Dim tmp as String
    Dim MyVal As String

    ' Start tmp with an empty string
    tmp = ""
    
    ' Loop through each character of the input MyText
    For i = 1 To Len(MyText)

      ' Read the character
      MyVal = Mid(MyText, i, 1)

      ' Check whether the character is a number or a comma
      ' and take reasonable action
      If IsNumeric(MyVal) or MyVal = "," then
          tmp = tmp & Mid(MyText, i, 1)
      End if

    Next i

    NurZahl = tmp

End Function

You'll have to change the code above to do what you want to do. The illustration above is to show how VBA code can be written.

In your VBA editor, when you see a red color on a line that means the editor has detected some issue with it.

If you were writing this function in Excel, you would typically use that function in a cell like this: =NurZahl(A1)

Upvotes: 1

Related Questions