Outlook VBA, how to add reference library ["User defined type not defined" ERROR]

I'm trying to use a template in visual basic to ask for user input, then lookup that value in an excel spreadsheet, and enter the corresponding value into the template.
I am receiving th error - "User defined type not defined" and the Dim x As Range line is highlighted.
I have a feeling it is because Outlook VBA doesn't have access to the excel Range library.
I've tried to go to tools -> references but there is not option for that in VBA, can anyone show me how to do it programatically?

Please Login or Register  to view this content.

There should be an option in References that looks like

Microsoft Excel 15.0 Object Library

It is possible to code this using late binding without the reference if you prefer. But that does detract from readability of the code because you cannot use any of the Excel object types, like Range and Workbook. You must declare them as Object.

You must also create an Excel application object in your code, rather than starting right in to use Excel code. Something like this, but I haven't tested it because it is dependent on your environment and files.

Please Login or Register  to view this content.

I have never used VLOOKUP (or any other worksheet functions) in a late binding situation. It may not be able to resolve an Object type when Excel is expecting a Range. If I find anything out I'll come back.

Alternative is to set the reference. Are you sure Excel is not in the list?

Hi,

A Type Mismatch error would suggest that VLookup is returning something that cannot be coerced to a Long value- either an error value or a text value most likely.

Hello kelseygueldalewis,

The code below should be copied and pasted into a new VBA module in an Excel Workbook. One of the macro listed can then be added to your Word Document macros to load the Excel type library giving you full access to the objects and methods in Excel.

Add this code to an Excel Workbook
Run the macro "List_References". This will provide you with the GUID, major, and minor version numbers needed for the macro "Load_Reference" in your Word Document.

Please Login or Register  to view this content.

Add this macro to your Word Document

Please Login or Register  to view this content.

Example of calling the macro in Word
In your Word Document before you make any reference to Excel or it's objects, call this macro with the information you obtained about Excel. For example, in Windows 10 the call looks like this...

Please Login or Register  to view this content.

There's quite a large problem with that, namely

Please Login or Register  to view this content.

is true for any number (in fact pretty much any value other than Null). You need two separate tests

Please Login or Register  to view this content.

The former code has two possible paths
1. If 2 < t then the evaluation of 2 < t < 5 is true because 2 < t = True, which coerces to -1 which is less than 5.
2. If 2 >= t, then it's true because False coerces to 0 which is also less than 5.