Excel VBA output a specific cells value

1
votes

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

2
vbaexcel
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
I have no idea I guess i wanted to be implicit in describing which sheet I was using - Vincent Tang
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
Possible duplicate of Error for using cells() for string in vba? - YowE3K
I meant *explicit. I will start using this thanks so much :) - Vincent Tang

2 Answers

3
votes

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.

1
votes

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