MS Access VBA to define User type

Can you pleqase share the correct syntax to define the User Type?  For example, I am trying to define a range and I keep getting, "User-defined type not defined".
Dim rng As Excel.Range
Set Excel.Range = A2: A10000
CFMIFinancial Systems AnalystAsked:
Who is Participating?
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Can you pleqase share the correct syntax to define the User Type?  >>

  Any time VBA sees something it doesn't understand, it assumes it is something that you need to define and refers to it that way.

   In this case, your refering to Excel methods and properties, and VBA doesn't know what that is, so you need to tell it.

  You can use late binding as show by iangoodacre, which uses the CreateObject method on a generic object variable or use early binding, were you do it like this:

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.WorkSheet
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)

  In this case however, you would need to set a reference to the Excel object library.  You do that in VBA by clicking tools, references in the VBA editor.  Then scroll down and find the Microsoft Excel lib and check it.

Dim xlWrkBk as Object
Dim xlSheet as Object

Set xlWrkBk = CreateObject("excel.sheet")
Set xlSheet = xlWrkBk.Worksheets(1)



CFMIFinancial Systems AnalystAuthor Commented:
Excellent, Thank you!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.