MS Access VBA to define User type

Hello,
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
LVL 1
CFMIFinancial Systems AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

iangoodacreCommented:
Dim xlWrkBk as Object
Dim xlSheet as Object

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

xlSheet.Cells("A1")

or

xlSheet.Range("A2","A10000")
0
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.

HTH,
JimD.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CFMIFinancial Systems AnalystAuthor Commented:
Excellent, Thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.