Beginner's attempt at VBA!! Not sure what is going on!!

[j6g]

Hello Team,
Greetings!! This is my first attempt at VBA!! I am reading Excel 2007 VBA Programming for Dummies by Mr. John Walkenbach. Excellent read for a complete novice like me!!

However, I am trying to figure out exactly what is going on the with the code below. Just wanted to write a basic VBA code to make sure that I am understanding what I am reading!!

When I am typing the following code the error pops out as follows:

Sub myfirstmac()
Application.Workbooks("Book1.xls").Worksheets("Sheet1").Range("A1").Value
End Sub

The corresponding error:

Run-time error '438' Object doesn't support this property or method!!


However, when I am modifying the code above with the one maintained below, the code just runs perfectly!!

Sub myfirstmac()
x = Application.Workbooks("Book1.xls").Worksheets("Sheet1").Range("A1").Value
MsgBox x
End Sub

Can some one help me as to why is this happening?? Why am I getting the run time error, in the first case??

Any help, will be highly appreciated!!
Regards,
Jai

[Kenneth Hobs]

Welcome to the forum!

The first is reading or getting the value. That is fine for a MsgBox. The 2nd is assigning a read value to a string for use in a MsgBox. I recommend doing it like this:

Option Explicit

Sub myfirstmac()  
  Dim x As String
  'x = Application.Workbooks("Book1.xls").Worksheets("Sheet1").Range("A1").Value
  x = ThisWorkbook.Worksheets("Sheet1").Range("A1").Value
  MsgBox x
End Sub

TIP: Compiling first as you did is a best practice. You can add the debug Compile button on the VBE toolbar. I also recommend always using Option Explicit as first line of code in a Module or sheet object. You can force that added by requiring variables to be declared in VBE's Options. Dim is used to declare the object type. When not declared, all variable are of Variant type. If you do that as a generally practice, which I do not recommend, do not use Option Explicit. Neat thing about Dimming a variable is that the capitalization of your variable will be the same when used anywhere else in the code. There are other advantages as well.

[Aflatoon]

Your first code doesn't say what to do with the value, which is the problem.

[j6g]

Compiling first as you did is a best practice.

Hello Kenneth. Many thanks for your pretty insightful reply!! Right now I am following as the book has asked me to do. However, it also suggests certain shortcuts for weeding out the unnecessary out of our VBA codes, which at this point I feel would be dangerous, if followed, hence I am taking the long arduous path of writing the entire code, which would help install the concepts properly!!

Highly appreciate your response.
Regards,
Jaideep

[j6g]

Your first code doesn't say what to do with the value, which is the problem.

Gee!!! I thought the code would just run and just select the value and stop there, which I wanted it to!! Anyways this was a first!!

Highly appreciate your help, Aflatoon
Regards,
Jaideep

[SamT]

"Value" is a Property of a Range. You cannot just mention a Value. It doesn't make sense.

For example I have a HairColor property. It doesn't make sense to say "SamT's HairColor." You have to do something with the Property, ie. Change SamT's HairColor; Copy SamT's HairColor; What Is SamT's HairColor.

[j6g]

"Value" is a Property of a Range. You cannot just mention a Value. It doesn't make sense."

Got it!! It all makes sense now!!
Gee, what a lovely place to be!! I have learnt so much!!