VBA Excel - Mantaining Public Variables With Button Press

Question

I'm trying to Create a document that has a bunch o constant strings.

I've declared then Public in a Module like this:

Public Abc As String

In "ThisWorkbook" I run the following code to initialize de variable

Private Sub Workbook_Open()

    Abc = "C5"

End Sub

I have Buttons coded to change some values like:

If Range(Abc) = "" Then
    Range(Abc) = 1
Else
    Range(Abc) = Range(Abc) + 1
End If

When I run a button with this code:

Sub BotaoNovoDia()

i = 3
While i <= 33
    If Cells(i, 11) = "" Then
        Cells(i, 11) = Range(Apresentacao)
        Cells(i, 12) = Range(Aceitacao)
        Cells(i, 13) = Range(Aceitou)
        Cells(i, 31) = Range("D41")

        Cells(i, 11).Interior.Color = Range(Apresentacao).Interior.Color
        Cells(i, 12).Interior.Color = Range(Aceitacao).Interior.Color


        If Range("K34") < 0.65 Then
            Range("K34").Interior.Color = vbRed
        Else
            Range("K34").Interior.Color = vbGreen
        End If

        If Range("L34") < 0.45 Then
            Range("L34").Interior.Color = vbRed
        Else
            Range("L34").Interior.Color = vbGreen
        End If

        Range(Aceitou) = 0
        Range(Rejeitou) = 0
        Range(NaoApres) = 0
        End
    Else
        i = i + 1
    End If
Wend
End Sub

And i try to run the first button again I get and error saying: "Run-Time error '1004': Method 'Range' of object '_Global' failed" the debug button takes me to the fisrt line that tries to access to the public variables value. What can i do to mantain the values in the Public variables?

You could use a `Name` in the workbook to store the values, which would persist beyond Run-time even. — David Zemens, Feb 19 '15 at 03:24

score 1 · Answer 1 · answered Feb 19 '15 at 05:06

1

When you call End (By itself, not as part of End If, etc) you clear your Globals.

Don't use End.

answered Feb 19 '15 at 05:06

Tim Williams

154,628
8
97
125

Thank you for your help. But how can I end a while loop without calling the "end" statement? – João Gonçalves Feb 20 '15 at 12:16
http://stackoverflow.com/questions/12200834/break-out-of-a-while-wend-loop-in-vba – Tim Williams Feb 20 '15 at 17:15

Mark Wickett · Answer 2 · 2015-02-20T01:11:37.893

Using a named range is a great idea; but there's no reason why your public declaration shouldn't work - except for that pesky End statement. (I missed that first read through...)

However, your scope isn't clear in each of your functions, e.g. which worksheet is the Range referring to, so if one function works on another worksheet, pushing the button that fires "Change" could refer to a different place that doesn't like that reference.

e.g. your ranges should be something like SomeWorkbook.TheWorksheet.Range(<range>) and when you're changing the cell value, you should use .Value to ensure there's no ambiguity - you'll know from searching through here that error 1004 is the least descriptive error code...

VBA Excel - Mantaining Public Variables With Button Press

2 Answers2