vba excel - probably easy question

[mark_T]

Can excel's vlookup function be used within a vba excel function?


trying to learn vba and can do basic functions with +-*/ and If statements but can't get vlookup to work.

Cheers
Mark

[atom290]

Yes you can

in the VBA you need to specify the worksheetfunction then use the associated member of that class the vlookup. Use the same syntax as you would on the on screen functionality

[atom290]

here you go a bit of code for you to play with

Sub CheckTable()

Dim vRet As Variant
Dim oRng As Range

Const clVal As Long = 2
Const clColInd As Long = 2
Const cblookup As Boolean = False

Set oRng = Range(Cells(1, 1), Cells(4, 3))

vRet = WorksheetFunction.VLookup(clVal, oRng, clColInd, cblookup)

End Sub

based on a table on the active sheet

that will look like

a b c
1 45 23
2 63 43
3 98 12

have a in cell a1

[Liszt]

If you are looking to use vba rather than excel, try to use an array in the code, which is non application specific. If you can do that in excel, then you'll be able to do it in word etc which is where VBA becomes useful

[mark_T]

thanks for the replies.

Liszt - Still in the early stages so i can only just cope with excel!

Atom290 - yep i had missed the worksheetfunction bit. thanks for the eg. can just about follow it but can't work out how to apply it.

What I was trying to do was simplify an excel formula, that I know works, by creating a function in vba

Excel formula in G1
=IF(ISERROR(VLOOKUP(F1,A1:C1000,3,FALSE)),0,VLOOKUP(F1,A1:C1000,3,FALSE)) ie return a zero where a value doesn't exist in the table range.

Vba so far that doesn't work

Function xlookup(a, b, c, d) ' normal arguments for vlookup

p = WorksheetFunction.IsError(WorksheetFunction.VLookup(a, b, c, d)) 'should be true or false

If p = True Then
xlookup = 0
Else
xlookup = WorksheetFunction.VLookup(a, b, c, d)
End If

End Function

Works ok when the vlookup would return a value but doesn't cope with the zero on error.

Any ideas?

Mark

[atom290]

Mark you have mail