CODEWITHSUNDEEP
excelvbaglobal-variables
stanigator
stanigator

Reputation: 10944

VBA global variables

I have the following code for handling userforms on my spreadsheet VBA macros:

Option Explicit
Public saved_vocab As String
Public saved_num As String
Public saved_def As String
Public saved_ex As String

Private Sub Save_Click()
    Dim low As Integer
    Dim high As Integer
    Dim selected As Integer

    low = 1
    high = Cells(1, 1).End(xlDown).Row
    Range(Cells(low, 1), Cells(high, 1)).Find(what:=vocab.Text, After:=ActiveCell, LookIn:=xlFormulas, _
          LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
          MatchCase:=False, SearchFormat:=False).Activate
    selected = ActiveCell.Row

    saved_vocb = Cells(selected, 1).Text
    saved_num = Cells(selected, 2).Text
    saved_def = Cells(selected, 3).Text
    saved_ex = Cells(selected, 4).Text
    Cells(selected, 1) = vocab.Text
    Cells(selected, 2) = num.Text
    Cells(selected, 3) = definition.Text
    Cells(selected, 4) = example.Text
End Sub

Private Sub undo_Click()
    Cells(selected, 1) = saved_vocab
    Cells(selected, 2) = saved_num
    Cells(selected, 3) = saved_def
    Cells(selected, 4) = saved_ex
End Sub

What I want to do is save the information properly in saved_??? string values so that I can access it properly when I decide to click the "Undo" button to execute the second function. However, when I execute the second function, information previously saved in saved_??? variables are gone perhaps b/c of myself handling variable scopes improperly. What would be the easiest way to accomplish what I want to do? Thanks in advance for the advices.

Upvotes: 2

Views: 6532

Answers (2)

Jose Basilio
Jose Basilio

Reputation: 51548

At a glance, I can see that the variable selected is declared inside the Save_Click() Subroutine and it is used in the undo_Click() subroutine.

The variable selected needs to be declared outside the subroutines along with your other global variables such as saved_vocab, saved_num, etc.

Option Explicit
Public saved_vocab As String
Public saved_num As String
Public saved_def As String
Public saved_ex As String
Public selected As Integer 'declaration added

Then remove the existing declarion for selected inside Save_Click()

Upvotes: 4

Lance Roberts
Lance Roberts

Reputation: 22842

I think you want to put the Global Variables in a 'normal' Module, not a Sheet Module.

Upvotes: 3

Related Questions