September 22, 2018

Variable Scope

In the last few videos we have learnt how to create and manage a database on a single worksheet with VBA. Here we have used the variables like currentrow, firstrow and lastrow repetitively in the various modules. Using public variables and the property of their scope, we need to declare the variables only once at the top of our first sub. In this manner our coding is easier to understand and quicker to execute. Watch the video below.

Watch this video on YouTube.

Here’s the complete VBA code for the different modules using the scope of the variables:

    1. VBA code for the ‘Add’ button:

Public currentrow As Long
Public lastrow As Long
Public Const firstrow As Long = 13
Sub addData()
Dim i As Long, nextBlankRow As Long
myLastRow
‘MsgBox lastrow
nextBlankRow = lastrow + 1
If Range(“E2”) = “” Then
MsgBox “You didn’t enter the first name of the customer!”
Range(“E2”).Select
Exit Sub
End If
If Range(“E3”) = “” Then
MsgBox “You didn’t enter the last name of the customer!”
Range(“E3”).Select
Exit Sub
End If                                                                                                      Cells(nextBlankRow, 3) = Range(“E2”)
Cells(nextBlankRow, 4) = Range(“E3”)
Cells(nextBlankRow, 5) = Range(“E4”)
Cells(nextBlankRow, 6) = Range(“E5”)
Cells(nextBlankRow, 7) = Range(“E6”)
Cells(nextBlankRow, 8) = Range(“E7”)
Cells(nextBlankRow, 9) = Range(“E8”)
‘MsgBox nextBlankRow
Dim p As Long, q As Long
p = 13
q = p + 1
Do While Cells(p, 3) <> “”
Do While Cells(q, 3) <> “”
If Cells(p, 3) = Cells(q, 3) And Cells(p, 4) = Cells(q, 4) Then
MsgBox “Duplicate Data! Will be removed from database!”
Range(Cells(q, 3), Cells(q, 9)).ClearContents
Else
q = q + 1
End If
Loop
p = p + 1
q = p + 1
Loop
End Sub

2. Code for ‘Clear’ button:

Sub resetData()
Range(“E2:E8”).ClearContents
End Sub

3. Code for ‘Search’ button:

Sub checkExistingData()
Dim i As Long
myLastRow
If Range(“E2”) = “” Then
MsgBox “You didn’t enter the first name of the customer!”
Range(“E2”).Select
Exit Sub
End If
If Range(“E3”) = “” Then
MsgBox “You didn’t enter the last name of the customer!”
Range(“E3”).Select
Exit Sub
End If

For i = 13 To lastrow
If Cells(i, 3) = Range(“E2”) And Cells(i, 4) = Range(“E3”) Then
Range(“E4”) = Cells(i, 5)
Range(“E5”) = Cells(i, 6)
Range(“E6”) = Cells(i, 7)
Range(“E7”) = Cells(i, 8)
Range(“E8”) = Cells(i, 9)
currentrow = i
Exit Sub
End If
Next i

MsgBox “Record doesn’t exist”

End Sub

4. ‘Pic’ button VBA code:

Sub displayPic()
Application.ScreenUpdating = False
Dim myObj
Dim Pictur
Set myObj = ActiveSheet.DrawingObjects
For Each Pictur In myObj
If Left(Pictur.Name, 7) = “Picture” Then
Pictur.Select
Pictur.Delete
End If
Next

Dim EmployeeName As String, T As String

myDir = “C:\Users\takyar\Pictures\employees\”

EmployeeName = Range(“E2”) & Range(“E3”)

T = “.jpg”‘Range(“F2”).Value = EmployeeName

On Error GoTo errormessage:
ActiveSheet.Shapes.AddPicture Filename:=myDir & EmployeeName & T, linktofile:=msoFalse, savewithdocument:=msoTrue, Left:=440, Top:=20, Width:=80, Height:=80errormessage:
If Err.Number = 1004 Then
MsgBox “File does not exist.” & vbCrLf & “Check the name of the employee!”
End If
Application.ScreenUpdating = True
End Sub

5. ‘Update’ button VBA code:
Sub updateRecord()
Dim reply As String
‘MsgBox currentrow
reply = InputBox(“Are you sure you wish to update the record? Type y to update”)
If reply = “y” And Range(“E2”) <> “” And Range(“E3”) <> “” Then
Cells(currentrow, 3) = Range(“E2”)
Cells(currentrow, 4) = Range(“E3”)
Cells(currentrow, 5) = Range(“E4”)
Cells(currentrow, 6) = Range(“E5”)
Cells(currentrow, 7) = Range(“E6”)
Cells(currentrow, 8) = Range(“E7”)
Cells(currentrow, 9) = Range(“E8”)
ElseRange(“E2”).Select
MsgBox “Data not updated!”
Exit Sub
End If
End Sub

6. Code for ‘Delete’ button:

Sub deleteRecord()
Dim reply As String
MsgBox currentrow
reply = InputBox(“Are you sure you wish to delete the record? This action cannot be undone! Delete anyway? Then type y”)
If reply = “y” Then
Cells(currentrow, 1).EntireRow.Delete
Else
Range(“E2”).Select
Exit Sub
End If
End Sub

7. Code for ‘Next’ record button:

Sub goToNextRecord()
myLastRow
‘MsgBox currentrow
If currentrow = lastrow Then
MsgBox “You are now in the last row!”
Exit Sub
End If
If currentrow >= firstrow And currentrow < lastrow Then
currentrow = currentrow + 1
Range(“E2”) = Cells(currentrow, 3)
Range(“E3”) = Cells(currentrow, 4)
Range(“E4”) = Cells(currentrow, 5)
Range(“E5”) = Cells(currentrow, 6)
Range(“E6”) = Cells(currentrow, 7)
Range(“E7”) = Cells(currentrow, 8)
Range(“E8”) = Cells(currentrow, 9)
End If
End Sub

8. VBA code for the ‘Back’ button:

Sub goBack()
myLastRow
If currentrow > firstrow And currentrow <= lastrow Then
currentrow = currentrow – 1
Range(“E2”) = Cells(currentrow, 3)
Range(“E3”) = Cells(currentrow, 4)
Range(“E4”) = Cells(currentrow, 5)
Range(“E5”) = Cells(currentrow, 6)
Range(“E6”) = Cells(currentrow, 7)
Range(“E7”) = Cells(currentrow, 8)
Range(“E8”) = Cells(currentrow, 9)
End If
If currentrow = firstrow Then
MsgBox “You are now in the first row!”
End If
End Sub

9. Code for ‘First Record’ button:

Sub goToFirstRow()
currentrow = firstrow
Range(“E2”) = Cells(firstrow, 3)
Range(“E3”) = Cells(firstrow, 4)
Range(“E4”) = Cells(firstrow, 5)
Range(“E5”) = Cells(firstrow, 6)
Range(“E6”) = Cells(firstrow, 7)
Range(“E7”) = Cells(firstrow, 8)
Range(“E8”) = Cells(firstrow, 9)
MsgBox “You are now viewing the first row of data!”
End Sub

10. Code for ‘Last Record’ button:

Sub goToLastRow()
myLastRow
currentrow = lastrow
Range(“E2”) = Cells(lastrow, 3)
Range(“E3”) = Cells(lastrow, 4)
Range(“E4”) = Cells(lastrow, 5)
Range(“E5”) = Cells(lastrow, 6)
Range(“E6”) = Cells(lastrow, 7)
Range(“E7”) = Cells(lastrow, 8)
Range(“E8”) = Cells(lastrow, 9)
MsgBox “You are now in the last row of data!”
End Sub

11. Code to assign ‘lastrow’ value:

Sub myLastRow()
lastrow = Cells.Find(What:=”*”, After:=Range(“A1”), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
‘MsgBox lastrow
End Sub

12. Code for the ‘Current Row’ button:

Sub displayCurrentRow()
MsgBox “The current row is ” & currentrow
End Sub

Now compare the above code with the code in this link and see the difference.

Download a sample file for practice:

2 thoughts on “Variable Scope

  1. Hello Sir
    Your videos are great. Learning a lot from these. One suggestion, Could you please also upload the excel file you work on in the video. This will help viewer/learner to practice.
    Thanks
    Regards
    Abdul

Comments are closed.