VBA array Vlookup

Question

I wanted to create a vlookup straight from VBA editor (not referred to cells). I've tried a 2D array but it doesn't work.

Sub vl()
    Dim typ(1 To 5, 1 To 2) As Variant

    typ(1, 1) = A
    typ(2, 1) = B
    typ(3, 1) = C
    typ(4, 1) = D
    typ(5, 1) = E
    typ(1, 2) = 50
    typ(2, 2) = 40
    typ(3, 2) = 30
    typ(4, 2) = 20
    typ(5, 2) = 10

    MsgBox Application.WorksheetFunction.VLookup("A", typ, 2, 0)
End Sub

I was hoping to get 50.

I know this can be done using a cell range but was hoping to do it straight in VBA Editor.

Vityata · Accepted Answer · 2017-12-18 16:26:04Z

2

This is a good example why you should always use Option Explicit. Your code works, but because you did not have Option Explicit, the VBEditor thinks that A is a variable and not a String. Thus, it does not give anything on the MessageBox.

This works as expected:

Option Explicit

Sub vl()
    Dim typ(1 To 5, 1 To 2) As Variant

    typ(1, 1) = "A"
    typ(2, 1) = "B"
    typ(3, 1) = "C"
    typ(4, 1) = "D"
    typ(5, 1) = "E"
    typ(1, 2) = 50
    typ(2, 2) = 40
    typ(3, 2) = 30
    typ(4, 2) = 20
    typ(5, 2) = 10
    MsgBox Application.WorksheetFunction.VLookup("A", typ, 2, 0)

End Sub

MSDN Option Explicit

edited Dec 18, 2017 at 16:26

answered Dec 18, 2017 at 16:07

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

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

1 Comment

Chas Over a year ago

Thank you very much, will have to look into Option Explicit further, very much appreciated.

Collectives™ on Stack Overflow

VBA array Vlookup

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related