none
Excel VSTO Add-In using Application.VLookup with Early Binding RRS feed

  • Question

  • I'm wrecking my Brains...not getting anywhere.

    I am working on an Excel Add-in with VB.net and want to use early binding (Option Strict On). However, I am getting an error when trying to use Excel's worksheet function Application.VLookup(Target, WSobj.Range("A:D"), 4, False). The error is "Option Strict disallows late binding".

    However, I can not get this Thing to work...whatever I try for early binding does not seem to work. I've tried this, for instance:

    Dim xlApp as Excel. Application
    xlApp = Excel.Application
    xlApp.Visible = True
    tmp = xlApp.Vlookup(Target, WSobj.Range("A:D"), 4, False)

    No success - Option strict on disallows late binding. I've tried to declare xlApp outside the procedure on class Level - didn't work. I've tried a bunch of other things but to no avail. If I set Option Strict Off everything is fine...

    Any advice?

    Thanks in advance,
    Joe


    • Edited by JoeHK Monday, October 1, 2018 1:07 PM
    Monday, October 1, 2018 12:57 PM

Answers

  • Hi JoeHK,

    You need to add a reference to:

    Microsoft Excel 16.0 Object Library - you'll find it on the References COM tab

    Then:

    Imports Excel = Microsoft.Office.Interop.Excel
    

    Please try the code as below:

    Dim tmp As String
    Dim xlApp as New Excel. Application
    xlApp = Excel.Application
    xlApp.Visible = True
    tmp = xlApp.Vlookup(Target, WSobj.Range("A:D"), 4, False)

    If it still doesn't work, Please refer to the following code:

    Dim result As String 
    Dim sheet As Worksheet 
    Set sheet = ActiveWorkbook.Sheets("Data") 
    result = Application.WorksheetFunction.VLookup(sheet.Range("AN2"), sheet.Range("AA9:AF20"), 5, False)

    For more information, please refer to the link as below:

    VLOOKUP Week; Using VLOOKUP in VB

    Hopefully it helps you.

    Best Regards,

    Lina


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    • Marked as answer by JoeHK Tuesday, October 2, 2018 6:51 AM
    Tuesday, October 2, 2018 1:48 AM
  • Hi Lina

    thank you for your support - it worked :-)

    I already had set the reference to "Microsoft Excel 16.0 Object Library" and also the "Imports Microsoft.Office.Interop.Excel". All it needed in the end was to modify my original line of code "Application.VLookup(Target, WSobj.Range("A:D"), 4, False)" to the following:

    Application.WorksheetFunction.VLookup(Target, WSobj.Range("A:D"), 4, False)

    I don't know why I didn't pick up before on adding "WorksheetFunction"...I've messed with it some other way as far as I recall. 

    Anyway, thanks a lot for your help,

    Joe



    • Marked as answer by JoeHK Tuesday, October 2, 2018 6:51 AM
    • Edited by JoeHK Tuesday, October 2, 2018 9:41 AM
    Tuesday, October 2, 2018 6:51 AM

All replies

  • Hi JoeHK,

    You need to add a reference to:

    Microsoft Excel 16.0 Object Library - you'll find it on the References COM tab

    Then:

    Imports Excel = Microsoft.Office.Interop.Excel
    

    Please try the code as below:

    Dim tmp As String
    Dim xlApp as New Excel. Application
    xlApp = Excel.Application
    xlApp.Visible = True
    tmp = xlApp.Vlookup(Target, WSobj.Range("A:D"), 4, False)

    If it still doesn't work, Please refer to the following code:

    Dim result As String 
    Dim sheet As Worksheet 
    Set sheet = ActiveWorkbook.Sheets("Data") 
    result = Application.WorksheetFunction.VLookup(sheet.Range("AN2"), sheet.Range("AA9:AF20"), 5, False)

    For more information, please refer to the link as below:

    VLOOKUP Week; Using VLOOKUP in VB

    Hopefully it helps you.

    Best Regards,

    Lina


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    • Marked as answer by JoeHK Tuesday, October 2, 2018 6:51 AM
    Tuesday, October 2, 2018 1:48 AM
  • Hi Lina

    thank you for your support - it worked :-)

    I already had set the reference to "Microsoft Excel 16.0 Object Library" and also the "Imports Microsoft.Office.Interop.Excel". All it needed in the end was to modify my original line of code "Application.VLookup(Target, WSobj.Range("A:D"), 4, False)" to the following:

    Application.WorksheetFunction.VLookup(Target, WSobj.Range("A:D"), 4, False)

    I don't know why I didn't pick up before on adding "WorksheetFunction"...I've messed with it some other way as far as I recall. 

    Anyway, thanks a lot for your help,

    Joe



    • Marked as answer by JoeHK Tuesday, October 2, 2018 6:51 AM
    • Edited by JoeHK Tuesday, October 2, 2018 9:41 AM
    Tuesday, October 2, 2018 6:51 AM