VBA code in 1 line

[Formula11]

How can you place the following VBA code in 1 line.
Tried using semi-colon ":" but does not work.

Counter = 0
For i = 1 To Len(Item)
   If IsNumeric(Mid(Item, i, 1)) = True Then
    Counter = Counter + 1
   End If
Next i

Counter = 0: For i = 1 To Len(Item): If IsNumeric(Mid(Item, i, 1)) = True Then: Counter = Counter + 1: End If: Next i

 

[mole999]

remove the colon after THEN and try it

 

[CephasOz]

I think the use of "For ... Next" is a problem here, and this approach won't work. However ... if you turn your multi-line version into a function, like so:

Function NumCount(ByVal strToCheck As String) As Integer
    Dim intNdx As Integer
    NumCount = 0
    For intNdx = 1 To Len(strToCheck)
        If IsNumeric(Mid(strToCheck, intNdx, 1)) Then
            NumCount = NumCount + 1
        End If
    Next intNdx
End Function

then you use a single-line piece of code like so:

counter = NumCount(item)

 

[Formula11]

CephasOz, thanks for confirming.
I'm sure the function would work but I wanted to apply 1-line code in general everywhere.

 

[Rick Rothstein]

If you really want a one-liner, how about this one...

Counter = Evaluate("SUM(0+ISNUMBER(-MID(""" & Item & """,ROW(1:" & Len(Item) & "),1)))")

Note: This single line of code would replace all of the code you posted as there is no need to initialize the Counter variable to zero beforehand.

 

[Formula11]

Rick, thanks this seems to work. It looks like it's for a cell formula though. Surprising.
Would have been good if there was a generic way to use in all instances with For/Next statements.

 

[Rick Rothstein]

It is VBA code, but it is using the Evaluate function which, among other things, evaluates Excel formulas. So my code constructs a text string argument for the Evaluate function that is an Excel formula that counts digits in a text string and then executes it and assigns the answer to the Counter variable. No actual formulas are placed on the Excel worksheet though... everything happens in the computers memory.

 

[footoo]

Would have been good if there was a generic way to use in all instances with For/Next statements.

How about 2 lines :

For i = 1 To Len(Item): If IsNumeric(Mid(Item, i, 1)) = True Then counter = counter + 1
Next i

 

[Formula11]

Thanks this good too.

 

[Rick Rothstein]

@footoo,

You can still do the For..Next in one line of code by eliminating the If..Then statement...

For i = 1 To Len(Item): Counter = Counter - IsNumeric(Mid(Item, i, 1)): Next