Converting VBA Arrays to VB.NET

Question

I've been trying to create a VB.NET VTSO addin, where excel columns are rearranged depending on the column header value.

I found (online) VBA code that does exactly that, but Visual Basic does not recognise the "Dim v = ..." line.

Does anyone know how I can remedy this.

Dim v As Object, x As Object, findfield As Object
    Dim oCell As Excel.Range
    Dim iNum As Long

    Dim v = Array("First Name", "Middle Name", "Last Name", "Date of Birth", "Phone Number", "Address", "City", "State", "Postal (ZIP) Code", "Country")
    For x = LBound(v) To UBound(v)
        findfield = v(x)
        iNum = iNum + 1

        oCell = ActiveSheet.Rows(1).Find(What:=findfield, LookIn:=Excel.XlFindLookIn.xlValues, LookAt:=Excel.XlLookAt.xlWhole, SearchOrder:=Excel.XlSearchOrder.xlByRows, SearchDirection:=Excel.XlSearchDirection.xlNext, MatchCase:=False, SearchFormat:=False)

        If Not oCell.Column = iNum Then
            ThisApplication.ThisWorkbook.Columns(oCell.Column).Cut
            ThisApplication.ThisWorkbook.Columns(iNum).Insert(Shift:=Excel.XlInsertShiftDirection.xlShiftToRight)
        End If
    Next x

Dim v As String() = { "First Name", "Middle Name", "Last Name", "Date of Birth", "Phone Number", "Address", "City", "State", "Postal (ZIP) Code", "Country" }. How to: Initialize Array in Variable in Visual Basic — Tim
– Tim, Commented Oct 26, 2015 at 21:12

NoAlias · Accepted Answer · 2015-10-26 21:21:40Z

1

Tim nailed it on the head with his comment on the fix.

    Dim v As String() = New String(9) {"First Name", "Middle Name", "Last Name", "Date of Birth", "Phone Number", "Address", "City", "State", "Postal (ZIP) Code", "Country"}

The Array Class is an Abstract Class, meaning you can't initialize it directly, but rather must do so through one of its children (another Class that inherits from it). In .Net you must specify the underlying Type of an Array (Object counts too), typically by adding parenthesis after the Type in the variable declaration.

answered Oct 26, 2015 at 21:21

NoAlias

9,2032 gold badges30 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3424543 Over a year ago

Thanks Guys! Worked perfectly!

rheitzman Over a year ago

` New String(9) ` not needed.

Collectives™ on Stack Overflow

Converting VBA Arrays to VB.NET

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related