Solved

Global Varable vs Global Constant

Posted on 2013-06-14
19
586 Views
Last Modified: 2013-06-24
Please help me to create a global variable so I can use "YearRef" throughout all functions and sub routines.

I am using global constants throughout my code:
EXAMPLE of global constant:
     Global Const InvNo As String = "R8570584"

I know I cannot do the same with a variable like
     Global YearRef As String = Format(Now(), "yyyy")
or even
     Global YearRef As Variant = Format(Now(), "yyyy")

I have tried creating a Public sub function this but it doesn't work.
Public Sub Init_Globals()
' Access global variable initialization
     Dim YearRef  As String
     YearRef = Format(Now(), "yyyy")
End Sub
0
Comment
Question by:CFMI
  • 5
  • 4
  • 4
  • +3
19 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39248129
It would need to be a function, not a sub if you are returning a value.

Public function CurrentYear() as String
   return Format(Now(), "yyyy")
end function
0
 
LVL 57
ID: 39248148
The declare for the variable:

Dim YearRef  As String


Can't be within the procedure.  As soon as the procedure terminates, the variable goes out of scope unless you declare it with the keyword STATIC (meaning it retains it's value between calls - basically turns it into a global variable).

 You need to move the DIM to the declarations section of a module (at the top).

Also, when using globals, it's often good to do a procedure like this:

Private gstrYearRef as string

Public  Function GetYearRef() as string

   If IsNull(gstrYearRef) then gstrYearRef = Format(Now(), "yyyy")

  GetYearRef = gstrYearRef

End Function

  This is both init and fetching the value.  Note that gstrYearRef won't change unless you restart the program.

  Many times you'll also want to do a corresponding set:

Public  Sub SetYearRef(varDate as Date)

   gstrYearRef = Format(varDate, "yyyy")

End Sub

  You can use a mix of the two above and your init for whatever you need to accomplish.

Jim.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39248152
Don't use Global - it's old-fashioned. Use Public instead.

Public YearRef As String
YearRef = Format(Now(), "yyyy")
0
 
LVL 1

Author Comment

by:CFMI
ID: 39248157
so if I put this at the top, I can create endless functions thereafter which call CurrentYear?

Example NEW:
DoCmd.TransferText acImportFixed, "R_RPT_SPON_INVOICE Import Spec", "RPT_SPON_CONFIRM", ExportDir & CurrentYear & InvNo & ".RPT.SPON_INVOICE.txt", False, ""

vs OLD
DoCmd.TransferText acImportFixed, "R_RPT_SPON_INVOICE Import Spec", "RPT_SPON_CONFIRM", ExportDir & YearRef & QRef & InvNo & ".RPT.SPON_INVOICE.txt", False, ""
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39248171
You should be able to do that, yes.
0
 
LVL 1

Author Comment

by:CFMI
ID: 39248295
ged325:  I added your Public function to the top.  Of course I had already tried "Public Sub" as noted above.  So I changed it to exactly what you wrote.
Public function CurrentYear() as String
   return Format(Now(), "yyyy")
end function

VBA didn't like the "return" part.  It turned red the moment I pasted it.  So I changed it to CurrentYear = Format(Now(), "yyyy") and ran

DoCmd.TransferText acImportFixed, "R_RPT_SPON_INVOICE Import Spec", "RPT_SPON_CONFIRM", ExportDir & CurrentYear & InvNo & ".RPT.SPON_INVOICE.txt", False, ""

It failed to read CurrentYear.

JDettman:  I'm kind of confused with respect to "GetYearRef" vs "gstrYearRef."  Remember, mine was a simple "YearRef."  So I copied / pasted your code to the top but couldn't figur out what to put where with respect to "GetYearRef", "gstrYearRef", or "YearRef."  

I'm lost. . .
Public Function GetYearRef() As String
    If IsNull(gstrYearRef) Then gstrYearRef = Format(Now(), "yyyy")
        YearRef = gstrYearRef
End Function
Public Sub SetYearRef(varDate As Date)
   YearRef = Format(varDate, "yyyy")
End Sub
0
 
LVL 75
ID: 39248455
"Don't use Global - it's old-fashioned. Use Public instead."

Well,  if you exclude Public ... and just use Dim ... it's still Public - that's the default.

Meanwhile ....

IF ... you are using >= A2007, then TempVars are the way to go.
They call be called from anywhere (including queries), and do *not* get reset if your app encounters an error wherein regular Global Variables would get reset.

Very easy to use:

Short tutorial on TempVars by Access MVP Juan Soto

http://blogs.office.com/b/microsoft-access/archive/2010/09/27/power-tip-maximize-the-user-of-tempvars-in-access-2007-and-20

Note ... you may have to log into MSDN to get to the Access Blog (or quickly create an account - definitely WORTH it)
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 300 total points
ID: 39248458
<<I'm kind of confused with respect to "GetYearRef" vs "gstrYearRef.">>

 GetYearRef is the name of a function.   gstrYearRef is a variable and is short for "Global String, with name of YearRef".

 The prefix of 'gstr' is just a naming convention to keep things straight when your coding.  You could have just as easily called it simply YearRef.   It's still a avariable though.

 <<I'm lost. . .>>

  Take out what you tried.

  Now open a brand new module.

  Paste in:

  Private gstrYearRef as string

  and you have a global variable.   However, because it is referenced with the "Private" keyword, it will only be able to be referenced by procedures in this module.

  If you declared it with Public, it could be referenced anywhere.    

  Now paste in:

Public Function GetYearRef() As String
   
    If gstrYearRef = "" Then gstrYearRef = Format(Now(), "yyyy")
    GetYearRef = gstrYearRef

End Function

 below that (note that I screwed up before and the above procedure is changed slightly).  Save the module.

  Now anywhere you need the year as a string, you can do:

   =GetYearRef()

 and it will work.   If the variable gstrYearRef is blank, when the procedure is called, it's filled in with the current year.  The value is then returned.

 You could also set it up like this:

Public Function GetYearRef() As String
   
   GetYearRef = gstrYearRef

End Function

Public Sub SetYearRef(varDate As Date)
 
 gstrYearRef = Format(varDate, "yyyy")

End Sub

 You'd now need to call the SetYearRef() to set the value before you used the Get.    You could also use something like your Init procedure and forget about the set.

  However, to make it more handy and flexible, you can do something like this:

Public Function GetYearRef(Optional varDate As Variant) As String
   
    If IsMissing(varDate) Then
      If gstrYearRef = "" Then gstrYearRef = Format(Now(), "yyyy")
    Else
      gstrYearRef = Format(varDate, "yyyy")
    End If
   
    GetYearRef = gstrYearRef

End Function

 If you call it like this:

 =GetYearRef()

 either:

1. The last year you used is returned.
2. If never called, the current year is returned.

but with the optional argument, you can also call it like this:

 = GetYearRef(#01/01/11#)

 and the year 2011 will be returned and will be returned from this point forward for every call.

 Hope that clears things up a bit and makes a little more sense.

  With slight variations on the above (use of a Get/Set/Init or passing of optional arguments), you can do a number of things depending on your needs.

Jim.
0
 
LVL 1

Author Closing Comment

by:CFMI
ID: 39248498
Thanks.  This is great.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 21
ID: 39253483
@ JDettman, curious if there is some reason why you did not use a class?
0
 
LVL 57
ID: 39253576
<<@ JDettman, curious if there is some reason why you did not use a class? >>

 Because VBA does not support full inheritance and it's object based rather then object orientated, I find classes in VBA lacking and not worth the effort.

 There are a few cases when they are required (executing multiple instances of the same form) and some where they are nice, but I have tools/utilities that I've either purchased or developed that allow me to gain the functionality of what I would have in VBA if I used classes.

 With them I have the ability to make changes quickly and consistently across objects without the overhead or complexity of using a class (ie. having common code execute for all text controls within an application).

  Now with VFP or C#/.net, it's all classes and nothing but classes, but with VBA?   I just don't find it worth while enough.

Jim.
0
 
LVL 21
ID: 39253954
@Jim, Thanks for sharing your thoughts. I agree with you.
0
 
LVL 1

Author Comment

by:CFMI
ID: 39254003
<<@ JDettman, curious if there is some reason why you did not use a class?>>

Folks, I'm pretty ignorant.  I'm a systems analyst rather than a programmer but often am required to develop code.  That said, the reason I "did not use a class" is because I'm not educated about them.

Want to clarify?  If you're asking why the code isn't part of an Access form, it's because I've broken certain functionality in the database into FUNCTIONS which I call in certain sequences.  They're easier to manage that way without having to work in a bunch of different forms.  What is a class?  Did I answer the question?
0
 
LVL 57
ID: 39254052
CFMI,

 The question of "why not a class" was directed at me in regards to the answer that I gave you.


Jim.
0
 
LVL 75
ID: 39254084
I've made very good use of Class Modules in Access, and the lack of Inheritance has never been an issue.
0
 
LVL 21
ID: 39266887
MX,

I have also made very good use of Class Modules in Access. I have been working with them a lot more lately in Access. I think they are probably very under-utilized in Access because they seam overly complex.
0
 
LVL 75
ID: 39266906
Well, anyone that has put code behind a form or report has 'worked' with Class Modules (and my not know it) ... since Form/Report modules are ... Class Modules.

Lots of folks don't know you can add custom Properties (like in a Class module) to Forms/Reports..
Main and pretty much the only difference is no Instantiate/Terminate in Form/Report modules ... instead, Load/Unload :-)

mx
0
 
LVL 21
ID: 39268182
Mx,

Yeah ... most people don't realize when adding a Macro or VBA code to a form/report event they working with Class. Also the the new Tempvars collection.

I was looking at some of the cool stuff Stephen Lebans did and he used classes.
0
 
LVL 57
ID: 39271446
<<pretty much the only difference is no Instantiate/Terminate in Form/Report modules ... instead, Load/Unload >>

 Actually there is, their just not exposed for Forms and Reports.  

 I myself use custom form properties to control subforms, but I need to keep a form "template" somewhere because I can't inherit from a base class that I've previosuly defined.

 That's why classes in VBA are good for some things (multiple instances) and lousy at others (event sinking).

  Say I had a printer class (and I do BTW), that works great because you often work with multiple printers.   But if I want to sink the AfterUpdate event of every text control in my application, the work I have to go through is incrediable because I can't define a base text box control and sub-class from it.   I have to hook up the text control class each and every time I use it.

  Being that VBA is object based and not object orientated, it makes a vast difference in the usability of classes.  That's why you don't see more widespread use of them in VBA.  For some things, their just too much effort for the return you get.

Jim.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

747 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

9 Experts available now in Live!

Get 1:1 Help Now