How to Get Cell Value with VBA

Visual Basic for Applications or VBA for short is a programing language of Microsoft Office Programs. Most of the people that use it, use it in Excel and Access.

This is a super useful tool that helps us to automate our work. We explained a couple of things from VBA already and given the introduction on how to use it in Excel.

In the text below, we will show how to get cell value with VBA code.

Get Cell Value with VBA code

To show this useful code, we will use a list of movies, with the release date, box office revenue, and budget.

Now, if we want to get the value of the movie „Doctor Strange“ with VBA, we can use the following code:

Sub Get_Cell_Value()
Cells(10, 1).Select
MsgBox ActiveCell.Value
End Sub

As seen we use the function Cells. This function has two parameters that need to be populated: row number and column number. „Doctor Strange“ is located in the 10th row of the first column, which is exactly what we typed in in the code above. When we execute our code, this will be shown on our screen:

This is the desired result.

There is another way to the defined desired cell, with the same result:

Sub Get_Cell_Value2()
Range("A10").Select
MsgBox ActiveCell.Value
End Sub

This code will give the same result, but it is using the range property instead.

Get Current Cell Value with VBA code

If for any reason, we would want to get the currently active cell displayed, we can use the following code:

Sub Get_Active_Cell_Value()
Dim i As Range
Set i = ActiveCell
MsgBox i
End Sub

In this code, we declared a variable i as a range. After that, we will set our variable to be equal to our active cell. Finally, we show this value with a message box.

If we go on and select the cell A17 and then execute the code, we will get the following result:

This is exactly the result that we were expecting.

Find Cell Value of Predefined Variable with VBA code

If you want to find out the cell value of a certain cell, you can use this simple code:

Sub Find_Cell_Address()
ActiveSheet.Cells.Find("Thor").Select
MsgBox ActiveCell.Address
End Sub

The code below finds the value “Thor” in our sheet. Then it gives out the location of this value in our sheet. When we execute this code, the value retrieved will be:

Which is the exact cell where the movie called „Thor“ is located.

This type of coding is rather lazy, as you have to input the exact word in your code every time, but it can be useful as first aid.

Find Cell Value with VBA code

To work around this issue, and not to predefine the values of our movies, we can use the following code:

Sub Find_Movie_Address()
    Dim movie As String
    Dim cell As Range
    movie = InputBox("Choose a movie")
    Set cell = Cells.Find(What:=movie, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
    If cell Is Nothing Then
        MsgBox ("Not found")
        Else
        MsgBox (cell.address)
    End If
End Sub  

In this code, we first have to declare two variables, one as a string, and another one as a range. Then, we have to let a user input the movie from which he/she wants to find a cell address. To do that, we use the line of code:

movie = InputBox("Choose a movie")

Now we have the desired movie stored in this variable.

Then we have to find where this movie is located in our sheet. We do that with the Find function.

For the last step, we have the If function, just in case the user misspelled the name of the movie or he typed some information that cannot be found in our list.

In that case, a message “Not found” will be shown. Otherwise, we will be shown the address of the cell where the movie is located.

If we execute our code we will have the following message:

We declared the text “Choose a movie” in our code, so it conveniently appears in our pop-up as well.

We will input „Iron Man 2“. Then, after we click OK, the following message will appear:

This is the cell where „Iron Man 2“ is located on our sheet.

Now, if we would execute the code again, and go on and input something that cannot be found in our table, like “Batman”, we would get the following message:

Which is exactly the message that we defined.

Posted in vba