Non-Zero Min in VBA?

[mswoods1]

I seem to know a lot of ways to calculate the non-zero min in Excel... but no slick way in VBA.

Problem: Want to find the first instance of either the ":" or "-" characters in a string. The user always enters either ":" or "-" but usually not both.

So far the best I got is:

lngColonPosition = InStr(1, Text, ":")
lngHyphenPosition = InStr(1, Text, "-")
If lngColonPosition = 0 And lngHyphenPosition = 0 Then
     ' we're in trouble
ElseIf lngColonPosition = 0 And lngHyphenPosition <> 0 Then
    lngFirstInstance = lngHyphenPosition
ElseIf lngColonPosition <> 0 And lngHyphenPosition = 0 Then
    lngFirstInstance = lngColonPosition
Else
    lngFirstInstance = Min(lngColonPosition, lngHyphenPosition)
End If

 

[pgc01]

Hi

I'd use:

Dim sText As String
 
sText = "abcd:efgh_ijkl"
 
MsgBox InStr(Replace(sText, ":", "_"), "_")

Remark: you should avoid using variable names that could be names of keywords, even if if you don't have problems now you may have them in the future, for ex. when you load a library. Better to avoid variable names like Text, Integer, Less, Database, etc.

 

[MickG]

Perhaps:-
For Text in "A1"

[COLOR=navy]Sub[/COLOR] MG14May38
[COLOR=navy]Dim[/COLOR] i [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] x() [COLOR=navy]As[/COLOR] Byte
[COLOR=navy]Dim[/COLOR] txt [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Dim[/COLOR] Num [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
txt = [a1]
x = StrConv(txt, vbFromUnicode)
[COLOR=navy]For[/COLOR] i = 0 To UBound(x)
    [COLOR=navy]If[/COLOR] x(i) = 58 Or x(i) = 45 [COLOR=navy]Then[/COLOR]
        Num = i
        [COLOR=navy]Exit[/COLOR] For
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR]
[COLOR=navy]If[/COLOR] Num = 0 [COLOR=navy]Then[/COLOR]
 MsgBox "No Chr found"
[COLOR=navy]Else[/COLOR]
  MsgBox "Chr found Position " & Num + 1
[COLOR=navy]End[/COLOR] [COLOR=navy]If[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[mswoods1]

Thanks guys. Interesting solutions. I decided to go a different way because I would also like the solution to:

1) Be able to handle being fed any number of possible strings and find the first instance
2) Be able to look for the first instance of words (in addition to one character)
3) Return both the string that was found and the position of the string

Below is a little function I wrote to accomplish this. You feed it one string to search, and then an array of strings to look for. It returns a dictionary object that contains one item. The one item is the string that was found (with key = position of string)

Function FindFirstInstance(sText As String, arrStringsToLookFor) As Dictionary
    ' Searches sText and finds the first instance of one of the
    ' strings in arrStringsToLookFor.
    ' Takes into account that not all the strings in arrStringsToLookFor
    ' may be present.
    ' Returns a dictionary object of one item
    ' Key = String that was found
    ' Item = Position of string
    
    Dim v, lPosition As Long
    Set FindFirstInstance = New Dictionary
    
    For Each v In arrStringsToLookFor
        lPosition = InStr(1, sText, v)
        If lPosition <> 0 And FindFirstInstance.Count <> 0 Then
            If FindFirstInstance.Items(0) > lPosition Then
                FindFirstInstance.RemoveAll
                FindFirstInstance.Add v, lPosition
            End If
        ElseIf lPosition <> 0 Then
                FindFirstInstance.Add v, lPosition
        End If
    Next
    
    ' Defaults to 0 if we didn't find any of the strings
    If FindFirstInstance.Count = 0 Then FindFirstInstance.Add "Nothing", 0
    
End Function

 

[pgc01]

Hi

I'm glad you have your problem solved.

I'm curious, why would you want to return a Dictionary object? Why use a type from a library that you have to load and not a simpler solution using the vba native data types, like for ex. use an array or return the values in the parameters?

 

[mswoods1]

Hi

I'm glad you have your problem solved.

I'm curious, why would you want to return a Dictionary object? Why use a type from a library that you have to load and not a simpler solution using the vba native data types, like for ex. use an array or return the values in the parameters?

Good point. A new version is below.

Sub FindFirstInstance(sText As String, arrStringsToLookFor, arrReturnArray)
    ' Searches sText and finds the first instance of one of the
    ' strings in arrStringsToLookFor.
    ' Takes into account that not all the strings in arrStringsToLookFor
    ' may be present.
    ' Returns an array of two items
    ' arrReturnArray(1) = String that was found
    ' arrReturnArray(2) = Position of string
    
    Dim vCurrentString, lPosition As Long, lNumberFound As Long
    ReDim arrReturnArray(1 To 2)
    
    For Each vCurrentString In arrStringsToLookFor
    
        lPosition = InStr(1, sText, vCurrentString)
        If lPosition = 0 Then GoTo NextString
        
        If lNumberFound <> 0 Then
            If lPosition < arrReturnArray(2) Then
                arrReturnArray(1) = vCurrentString
                arrReturnArray(2) = lPosition
                lNumberFound = lNumberFound + 1
            End If
        Else
            arrReturnArray(1) = vCurrentString
            arrReturnArray(2) = lPosition
            lNumberFound = lNumberFound + 1
        End If
        
NextString:
    Next
    
    ' Defaults to 0 if we didn't find any of the strings
    If arrReturnArray(2) = 0 Then arrReturnArray(1) = "Nothing"
    
End Sub

 

[Rick Rothstein]

2) Be able to look for the first instance of words (in addition to one character)[/code]

You have to be careful with this one... you might search for a word and find an instance of it where it is embedded in a longer word. For example, let's say the word was "the" and your text was "He was a brother of the bride"... you would end up find the "the" inside the word "brother" instead of the "the" that stands alone. Also, as written, your InStr function call is performing a case-sensitive search.