Excel VBA output a specific cells value

Question

I can't seem to get something so simple to work in excel VBA

I have data on cell A1

Sub TestBox()
ActiveSheet.Activate
MsgBox (Cells(0, 0).Value)
End Sub

I want to output said data using basically a msgBox

Run-time error:'1004' Application -defined or object-defined error

Unrelated to your problem, but why do you use ActiveSheet.Activate? By definition, the ActiveSheet is the one that is active, so that seems to be a pointless piece of code. — YowE3K
– YowE3K, Commented Aug 2, 2017 at 21:19
I have no idea I guess i wanted to be implicit in describing which sheet I was using — Vincent Tang
– Vincent Tang, Commented Aug 2, 2017 at 21:22
If you want to be explicit in defining which sheet you are using, you should start your code with something like Dim ws As Worksheet: Set ws = ActiveSheet and then use MsgBox ws.Cells(1, 1).Value. If you don't, the user could change the active sheet between when you set the active sheet to be the active sheet and when the MsgBox is displayed. — YowE3K
– YowE3K, Commented Aug 2, 2017 at 21:25
Possible duplicate of Error for using cells() for string in vba? — YowE3K
– YowE3K, Commented Aug 2, 2017 at 21:42
I meant *explicit. I will start using this thanks so much :) — Vincent Tang
– Vincent Tang, Commented Aug 3, 2017 at 13:30

Scott Holtzman · Accepted Answer · 2017-08-02 21:05:53Z

3

A more reliable answer is to use something like this:

Sub TestBox()

Dim sVal as String
sVal = ThisWorkbook.Worksheets("Sheet1").Cells(1,1).Value
'sVal = ActiveSheet.Cells(1,1).Value 
'above can be used as well, but its not 100% reliable.

Msgbox sVal

End Sub

Notice how i explicitly declared my objects and I avoided Activate.

answered Aug 2, 2017 at 21:05

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Vincent Tang Over a year ago

What is so bad with "ActiveSheet.Activate" anyhow?

Scott Holtzman Over a year ago

1) ActiveSheet.Activate is redundant, since ActiveSheet already refers to the activated sheet. 2) At any given point in the code the ActiveSheet may not be what you expect it to be. In this simple code you have in your question it won't throw an error or mess up your code, but it's good practice to avoid it unless absolutely needed (it's rare that it is, but at times it is). @Kagerjay

YowE3K Over a year ago

@Kagerjay ^^ and (unless I am completely confused - which is not unusual - and hopefully Scott will correct me if I am wrong) Activate (and Select) is quite slow in processing terms.

Scott Holtzman Over a year ago

@YowE3K -- when used over and over yes. When used once or twice when needed, not so much.

Vincent Tang · Accepted Answer · 2017-08-02 20:58:22Z

1

Cells are indexed at 1 as the initial value when referencing the entire sheet (as opposed to ActiveCell.Offset, those are indexed at 0)

A1= Cells(1,1)

so its

Sub TestBox()
ActiveSheet.Activate
MsgBox (Cells(1, 1).Value)
End Sub

to output the current sheet's A1 value

answered Aug 2, 2017 at 20:58

Vincent Tang

4,1966 gold badges52 silver badges72 bronze badges

Collectives™ on Stack Overflow

Excel VBA output a specific cells value

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related