[resolved] Please help with VBA, for Excel 2007: working with objects

[needhelpooo]

Hi, I have attempted to create a code for the following described purpose, but my codes do not produce my desired results. Without reallizing the difference between VB.NET and VBA, I had a thread over at the VB.NET forum.
My version of VBA is: "Microsoft Visual Basic 6.5", VBA: Retail 6.5.1053


I am attempting to create a program to take account of several cells in a column like so (example):
123
234
123
123
345
005
007
123

1. filter out any 005, 007, 006, and 009 values,

2. then store everything else into an array without repetition, so the array would now be:
123
234
345

3. then convert integers to strings
4. lastly combine the strings
5. and output the combined string:
"123, 234, 345"

My codes are:

Public Function CollectSources(Sheetname As String, CellCol As Integer, CellRow As Integer, SelectLength As Integer) As String

' This is a non-coded note
' input asks for Sheetname, the initial cell position (CellCol, CellRow), and the number of cells to account for (including initial cell)
' Dim = command to set variable type
Dim CellRowNow, CurrVal As Integer ' CellRowNow is the current position of Cell row, CurrVal is the value of the current cell
Dim SourceArray(), ArrayLength, ArrayPosition As Integer ' SourceArray(ArrayPosition) is the value of the current array variable
Dim NoSimilar As Boolean ' NoSimilar is whether the CurrVal is the same value as any numbers already stored in SourceArray()
Dim result, StringTemp As String ' result is the accumulating string of numbers, StringTemp is used to store string converted from integer

ArrayLength = 0

    Do While SelectLength > 0
        ' loops for "SelectLength" numbers of times
        SelectLength = SelectLength - 1 ' if initial cell at (1,1) w/ SelectLength of 2, then first processed position is at (1,2) when SelectLength should now be 1 (2-1=1), second position (which is the initial cell at (CellCol, CellRow)) is at (1,1) when SelectLength is 0 (1-1=0)
        CellRowNow = CellRow + SelectLength ' sets current cell row, and current cell column never changes
        CurrVal = Worksheets(Sheetname).Cells(CellCol, CellRowNow).value ' gets current cell value from current position
        ' ---------------
        If CurrVal <> 5 Or CurrVal <> 6 Or CurrVal <> 7 Or CurrVal <> 9 Then
            ' if CurrVal does not equal 5, 6, 7, or 9
            ' ---------------
            If ArrayLength > 0 Then
                ' if not first value in array
                ArrayPosition = 0 ' reset array position
                NoSimilar = False
                ' ---------------
                Do While ArrayPosition < ArrayLength
                    ' loops for "ArrayLength" numbers of times
                    If SourceArray(ArrayPosition) = Worksheets(Sheetname).Cells(CellCol, CellRowNow).value Then
                        ' if current cell value equal any of array values
                        NoSimilar = False ' not similar is not true
                        Exit Do ' thus exit loop
                    End If
                    NoSimilar = True ' if still in loop then there hasn't been match between cell value and array value
                    ArrayPosition = ArrayPosition + 1 ' advance to next array position
                Loop
                ' ---------------
                ' ---------------
                If NoSimilar = True Then
                    ReDim Preserve SourceArray(0 To ArrayPosition) ' ArrayPosition numerically 1 less than ArrayLength to account for 0
                    SourceArray(ArrayPosition) = Worksheets(Sheetname).Cells(CellCol, CellRowNow).value ' insert current value into current array slot
                    ArrayLength = ArrayLength + 1 ' increase record of array length
                Else
                    ' If NoSimilar = False, then nothing happens here
                End If
                ' ---------------
            ElseIf ArrayLength = 0 Then
                ' if first value in array
                ReDim SourceArray(0) ' initialize first array slot
                SourceArray(0) = Worksheets(Sheetname).Cells(CellCol, CellRowNow).value ' insert value into first array slot
                ArrayLength = 1 ' set array length to 1
            Else
                result = "Error" ' impossible case where ArrayLength value is absent or < 0
            End If
            ' ---------------
        Else
            ' If CurrVal does equal 5, 6, 7, or 9, then nothing happens here
        End If
        ' ---------------
        If result = "Error" Then
            Exit Do ' exit loop if there is an error
        End If
    Loop
    
    ArrayPosition = 0
    Do While ArrayPosition < ArrayLength
        StringTemp = str(SourceArray(ArrayPosition))
        result = result + ", " + StringTemp
        ArrayPosition = ArrayPosition + 1
    Loop
    CollectSources = result
End Function

There are no error messages from my codes, but it does not do as it is intended.
Currently, it displays: ", 0"
no matter what data was given.

Kevininstructor kindly revamped my entire code into the following .NET code:

Private Sub GetDistinctValues()
    Using cn As New System.Data.OleDb.OleDbConnection
        ' Change to your worksheet
        Using cmd As OleDbCommand = New OleDbCommand With _
        { _
                .Connection = cn, _
                .CommandText = "SELECT DISTINCT F1 FROM [Sheet1$] " _
        }
            ' Change to use your file
            Dim FileName As String = IO.Path.Combine(Application.StartupPath, "book1.xlsx")
            ' Change if not using Excel 2007
            Dim Builder As New OleDbConnectionStringBuilder With _
                { _
                    .DataSource = FileName, _
                    .Provider = "Microsoft.ACE.OLEDB.12.0" _
                }
            ' Change if not using Excel 2007
            Builder.Add("Extended Properties", "Excel 12.0; HDR=No;")

            cn.ConnectionString = Builder.ConnectionString
            cn.Open()

            Dim dt As New DataTable
            dt.Load (cmd.ExecuteReader)
            Dim Result = String.Join(",", _
            ( _
                From T In dt.AsEnumerable _
                Select Value = CStr(T.Field(Of Integer)("F1"))).ToArray _
            )

            Console.WriteLine (result)
        End Using
    End Using
End Sub

Which led to my reallization that VB.NET is entirely incompatible with VBA.

Thanks to anyone for taking a look at this.