Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 597
  • Last Modified:

Global Varable vs Global Constant

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
CFMI
Asked:
CFMI
  • 5
  • 4
  • 4
  • +3
1 Solution
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
Martin LissRetired ProgrammerCommented:
Don't use Global - it's old-fashioned. Use Public instead.

Public YearRef As String
YearRef = Format(Now(), "yyyy")
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
CFMIFinancial Systems AnalystAuthor Commented:
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
 
Kyle AbrahamsSenior .Net DeveloperCommented:
You should be able to do that, yes.
0
 
CFMIFinancial Systems AnalystAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
 
CFMIFinancial Systems AnalystAuthor Commented:
Thanks.  This is great.
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
@ JDettman, curious if there is some reason why you did not use a class?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<@ 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
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
@Jim, Thanks for sharing your thoughts. I agree with you.
0
 
CFMIFinancial Systems AnalystAuthor Commented:
<<@ 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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
CFMI,

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


Jim.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
I've made very good use of Class Modules in Access, and the lack of Inheritance has never been an issue.
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 5
  • 4
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now