VBA Bubble Sort Method on Values Formatted as Currency

powera86 · Oct 24th 2012

Good morning,

I have been using the bubble sort method with some success for sorting arrays etc. I have however run into a bit of a snag where it appears for some reason that if the cells containing the values that I am building my array with are formatted as currency, or accounting, then the sort "fails" and just spits out the array as it was originally built.

Any idea why? or how this could be resolved?

Thanks,

**rory** · Oct 24th 2012

Re: VBA Bubble Sort Method on Values Formatted as Currency

What's the code you are using for the bubble sort?

powera86 · Oct 24th 2012

Re: VBA Bubble Sort Method on Values Formatted as Currency

Hey Rory,

See below:

Code

Sub SortIntegerArray(ByRef paintArray As Variant)
    Dim lngX As Long
    Dim lngY As Long
    Dim intTemp
    
    For lngX = LBound(paintArray) To (UBound(paintArray) - 1)
        For lngY = LBound(paintArray) To (UBound(paintArray) - 1)
            If Val(paintArray(lngY)) > Val(paintArray(lngY + 1)) Then
                'exchange the items
                intTemp = paintArray(lngY)
                paintArray(lngY) = paintArray(lngY + 1)
                paintArray(lngY + 1) = intTemp
            End If
        Next
    Next
End Sub

Display More

**rory** · Oct 24th 2012

Re: VBA Bubble Sort Method on Values Formatted as Currency

Use CDbl instead of Val (Val will return 0 due to the currency symbol) or use Value2 to populate the array you pass to the function

powera86 · Oct 24th 2012

Re: VBA Bubble Sort Method on Values Formatted as Currency

Perfect, worked like a charm!

Thanks as always

powera86 · Oct 24th 2012

Re: VBA Bubble Sort Method on Values Formatted as Currency

Doesn't seem to like the existence of blank cells, and will give a type mismatch error. Any suggestions?

**rory** · Oct 24th 2012

Re: VBA Bubble Sort Method on Values Formatted as Currency

Either test the array values using IsNumeric or skip the conversion and populate the array with numbers rather than currency data (using the Value2 property as I mentioned)

powera86 · Oct 24th 2012

Re: VBA Bubble Sort Method on Values Formatted as Currency

Hey Rory,

I'm not actually building these arrays, I am pulling Autofilter criteria into an array, so unless I'm mistaken I don't have the Value2 option.

To your first point, if I understand, I should test the array values before I compare them, and if the Isnumeric returns false...then what, assume the non-numeric value is 0 perhaps?

**rory** · Oct 24th 2012

Re: VBA Bubble Sort Method on Values Formatted as Currency

Quote from powera86;634095

I'm not actually building these arrays, I am pulling Autofilter criteria into an array

Say what??

Quote

To your first point, if I understand, I should test the array values before I compare them, and if the Isnumeric returns false...then what, assume the non-numeric value is 0 perhaps?

Depends - how do you want them treated?

powera86 · Oct 24th 2012

Re: VBA Bubble Sort Method on Values Formatted as Currency

Lol...

This is what is building the array to be tested:

Code

Function AutoFilterCriteria(lngFilterIndex As Long, objAFilter As AutoFilter) As String
    Dim strCri1 As String, strCri2 As String
    Dim rng As Range
    Dim var As Variant
    Dim lng As Long
    Dim criObj As Variant
    Dim blnIsDate As Boolean
    
    With objAFilter
        blnIsDate = IsDate(.Range.Columns(lngFilterIndex - 1).Cells(2).Text)
        With .Filters(lngFilterIndex - 1)
            If Not .On Then AutoFilterCriteria = "": Exit Function
            On Error Resume Next
            criObj = .Criteria1
            Err.Clear: On Error GoTo 0: On Error GoTo -1
            If Not IsEmpty(criObj) Or Not blnIsDate Then
                If IsArray(.Criteria1) Then
                    strCri1 = Replace(Join(.Criteria1, ","), "=", "")
                Else
                    strCri1 = .Criteria1
                    If blnIsDate Then
                        strCri1 = NumReplDate(strCri1)
                    End If
                End If
                If .Operator = xlAnd Then
                    strCri2 = " AND " & .Criteria2
                    If blnIsDate Then
                        strCri2 = NumReplDate(strCri2)
                    End If
                ElseIf .Operator = xlOr Then
                    strCri2 = " OR " & .Criteria2
                    If blnIsDate Then
                        strCri2 = NumReplDate(strCri2)
                    End If
                End If
            Else
                For Each rng In .Parent.Range.Columns(lngFilterIndex - 1).Cells(2).Resize(.Parent.Range.Rows.count - 1)
                    If rng.Rows.Hidden = False Then
                        strCri1 = strCri1 & "," & CLng(CDate((rng.Text))) 'FormatDateTime(CDate(rng.Text), vbGeneralDate)
                    End If
                Next rng
                strCri1 = Mid(strCri1, 2)
                var = Split(strCri1, ",")
                SortIntegerArray var
                ConvertValToDate var, vbGeneralDate
                strCri1 = Join(var, ",")
            End If
        End With
    End With
    If Len(strCri1 & strCri2) > 2000 Then
        AutoFilterCriteria = "TOO MANY CRITERIA TO DISPLAY ALL"
            ElseIf Len(strCri1 & strCri2) <= 2000 Then
                AutoFilterCriteria = strCri1 & strCri2
    End If
    
End Function

Display More

And I would ideally want blanks treated as 0's

powera86 · Oct 24th 2012

Re: VBA Bubble Sort Method on Values Formatted as Currency

This seems to have fixed it...but some feedback would be appreciated

Code

Private Sub SortIntegerArray(ByRef paintArray As Variant)
    Dim lngX As Long
    Dim lngY As Long
    Dim intTemp
    
    For lngX = LBound(paintArray) To (UBound(paintArray) - 1)
        For lngY = LBound(paintArray) To (UBound(paintArray) - 1)
        
            If IsNumeric((paintArray(lngY))) And IsNumeric((paintArray(lngY + 1))) Then
                If CDbl(paintArray(lngY)) > CDbl(paintArray(lngY + 1)) Then
                    'exchange the items
                    intTemp = paintArray(lngY)
                    paintArray(lngY) = paintArray(lngY + 1)
                    paintArray(lngY + 1) = intTemp
                End If
            ElseIf IsNumeric((paintArray(lngY))) And Not (IsNumeric((paintArray(lngY + 1)))) Then
                If CDbl(paintArray(lngY)) > 0 Then
                    'exchange the items
                    intTemp = paintArray(lngY)
                    paintArray(lngY) = paintArray(lngY + 1)
                    paintArray(lngY + 1) = intTemp
                End If
            ElseIf Not (IsNumeric((paintArray(lngY)))) And IsNumeric((paintArray(lngY + 1))) Then
                If 0 > CDbl(paintArray(lngY + 1)) Then
                    'exchange the items
                    intTemp = paintArray(lngY)
                    paintArray(lngY) = paintArray(lngY + 1)
                    paintArray(lngY + 1) = intTemp
                End If
            End If
        Next
    Next
End Sub

Display More

**rory** · Oct 24th 2012

Re: VBA Bubble Sort Method on Values Formatted as Currency

I'd change

Code

strCri1 = strCri1 & "," & CLng(CDate((rng.Text)))

to

Code

strCri1 = strCri1 & "," & IIF(rng.Value2="",0,rng.Value2)

VBA Bubble Sort Method on Values Formatted as Currency

Participate now!

Share

Users Viewing This Thread