MS Excel + Visual Basic: How can I obtain the value of a cell into a variable?

Question

For example, let's pretend in cell A1 the total value is 100

When I create a button in Excel, and Assign Macro (which opens up Visual Basic) , how can I get the value of the cell into a variable I declared?

Answer 1

' //
' // Start Code
' //
Sub b1911()
szCell = Workbooks("Book1"). Worksheets("Sheet1"). Cells(1, 1). Value
MsgBox szCell
End Sub
' //
' // End Code
' //

Answer 2

In your VBA code, write
Sub GetValue()
ThisWorkbook.Activate
Sheets("xxxx").select
myVar = Range("A1")
End Sub
Where xxxx is the name of the sheet on which you have the value in cell A1.

Further you can write msgbox "Value of my var is " & myVar to show a pop up message displaying the value of myVar.

Hope this helps

Answer 3

I don't see my favorite way to refer to cells in the other answers:

dim MyA1Var
MyA1Var = [A1]

I capitalize the A for appearances. It works in any case.

Answer 4

Use either of the two below:

=Range("A1")
or
= Cells(1,1)

Note that when using 'Range' you have to use inverted commas, and hence cannot be used with a formula that returns the Rows or column numbers.

whereas when using 'Cells' you do not use any inverted commas and therefore is more versatile.
Cells(RowNum,ColNum)

Answer 5

oCell = Workbooks(""). Worksheets(""). Cells(1, 1)