A VBA vlookup function

If I had columns like:

ColA     ColB
----     ----
emp      NUMBER
dept     DATE
name     VARCHAR

I need a vba function that I could use as follows:

If IS_NUMBER(A1) = "T" Th开发者_Go百科en
  //stuff
End If

and could be simply modified for:

If IS_VARCHAR(A3) = "T" Then
  //stuff
End If

Actually it might be better if it worked like (basically the 2nd parameter is doing a lookup on the cell to the right):

If DataType(A3, "VARCHAR") = "T" Then
  //stuff
End If

If you named your lookup table as a named range, say to map, you could use the following code:

Option Explicit

Function get_type(field As Variant)

  Dim map As Range
  Set map = Range("map")

  On Error Resume Next
    get_type = WorksheetFunction.vlookup(field, map, 2, 0)
  On Error GoTo 0

End Function

Sub test_get_type()

  Debug.Print get_type("emp")
  Debug.Print get_type("dept")
  Debug.Print get_type("name")

End Sub

The function get_type() simply acts as a passthrough to the vlookup() function.

Here's how I ended up coding it:

Public Function get_data_type(Cell As Range, Text As String) As String 

  Dim dtype As String  
  dtype = Cell.Offset(0, 1).Value  
  If dtype = Text Then     
    get_data_type = "T" 
  Else     
    get_data_type = "F" 
  End If  

End Function  

Sub test() 

  If get_data_type(Range("F5187"), "VARCHAR") = "T" Then  
    '// do stuff 
  End If 

End Sub 

Another method:

Public Function get_data_type(Cell As Range) As String 

  Dim dtype As String  
  dtype = Cell.Offset(0, 1).Value 
  get_data_type = dtype  

End Function  

Sub test() 

  If get_data_type(Range("F5187")) = "VARCHAR" Then  
    '//stuff
  End If 

End Sub