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
– David Zemens, Commented Feb 19, 2015 at 3:24

Tim Williams · Accepted Answer · 2015-02-19 05:06:02Z

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, 2015 at 5:06

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

João Gonçalves Over a year ago

Thank you for your help. But how can I end a while loop without calling the "end" statement?

Tim Williams Over a year ago

stackoverflow.com/questions/12200834/…

Mark Wickett · Accepted Answer · 2015-02-20 01:11:37Z

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...

Collectives™ on Stack Overflow

VBA Excel - Mantaining Public Variables With Button Press

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related