CODEWITHSUNDEEP
vbaexcel
jurassicparkcoffee
jurassicparkcoffee

Reputation: 323

Excel VBA script run in 2010 Office does not recognize the Workbook object. But it does work on 2007

I wrote some scripts in VBA Excel 2007 which I am now trying to run on Excel 2010. However, the simplest of objects, Workbooks, is not being recognized in the 2010 version:

Dim myValue As Variant
Dim myWksheet As Worksheet

Set myValue = Workbooks("myWorkbook").Sheets("Sheet1").Range("A1").Value
Set myWksheet = Workbooks("myWorkbook").Sheets("Sheet2")

Both of these lines are giving me errors on 2010 but not on 2007. Why is this happening and how can I fix it?

Thanks in advance, Lakeand

Upvotes: 0

Views: 1879

Answers (1)

jurassicparkcoffee
jurassicparkcoffee

Reputation: 323

Found the answer to my own question!!!

It can happen sometimes, for no good reason, that the compiler is not able to detect the Workbook object. So in order to avoid errors whatsoever, just point it to its parent classes. That way there's no room for error.

In the case of VBA 2007 and 2010, preface Workbook with Excel. Like so:

Dim myValue As Variant
Dim myWksheet As Worksheet

Set myValue = Excel.Workbooks("myWorkbook").Sheets("Sheet1").Range("A1").Value
Set myWksheet = Excel.Workbooks("myWorkbook").Sheets("Sheet2")

Thanks! Lakes

Upvotes: 1

Related Questions