Solved

How to I maintain a variables scope in VBA until the workbook closes?

Posted on 2015-02-20
12
90 Views
Last Modified: 2016-02-10
I want the variable myInteger to have a permanent scope so that I can set it to whatever I want when the workbook opens, and it will be ready for use for whenever I run a separate macro. I noticed that the variable loses its value as soon as the macro below is complete. Is there any way to keep it in Excel's memory as the value I want, until I close the workbook?

Public myInteger As Integer

Public Sub Workbook_Open()

    myInteger = 1

End Sub

Open in new window

0
Comment
Question by:shampouya
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Move the Public myInteger definition in a code module.
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
Comment Utility
Be warned.
Even if you make it Public in scope in a code module, ANY run-time error will set it to null.

You are better off to put it in a cell in a 'very hidden' sheet, and then retrieve it from the cell.
0
 
LVL 2

Expert Comment

by:Rossano Praderi
Comment Utility
The comment of Nick67 is the best choice, for your convenience I've attached a sample code.

Bregs
Rossano Praderi
Public-Variables.xlsm
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Nick67's remark does not seem to be true. Run Sub1 and then Sub2.

Option Explicit

Public MyVar As Integer

Sub sub1()
MyVar = 99
End Sub
Sub sub2()
Dim i As Integer
On Error GoTo ErrorRoutine
MsgBox "Myvar's value before the error is " & MyVar
' Generate a runtime error (division by zero)
i = 7 / 0
Exit Sub
ErrorRoutine:
MsgBox "Myvar's value after the error is " & MyVar

End Sub

Open in new window

0
 
LVL 9

Expert Comment

by:Ramanhp
Comment Utility
Try using the concept of global variables.

Steps : Insert New Module from VBA Project UI and declare variables using Global

Declation Example :
Global iRaw As Integer
Global iColumn As Integer
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
@Ramanhp: While Global still works, it was replaced by Public a long time ago and in any case they both work the same way.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 9

Expert Comment

by:Ramanhp
Comment Utility
Thanks Martin.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
@Martin Liss
You successfully handled the error.
On Error Resume Next and unhandled errors, can be persnickety.

Murphy's law will apply.
The odds that a global variable will get dumped are proportional to the annoyance it's unanticipated dumping will cause.
You can build code that will check a global variable for validity before using it...
Or just not use a global variable, and store what is needed in a cell.

It's the kind of thing that, if you've encountered it, you remember and don't get fooled again.
YMMV

Nick67
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
On Error Resume Next and unhandled errors, can be persnickety.
There's no On error Resume Next in the code I posted.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Right
On Error GoTo ErrorRoutine
You handled the error.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
I see now that you are right about it becoming 0 and it even happens if you use a Private Property for the variable, which is really surprising to me. - thanks.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
You're welcome.
It isn't that Public variables in a global scope don't have their uses.
There are times when you need to preserve and pass a value from a routine that you know is going to end.
You just have  to make damn sure that between the time you set it and the time you consume it, that nothing can go wrong.

From opening until closing is a long time to ensure nothing squirrels up the works :)
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now