Solved

Error of variable not defined (TimeZoneRecordCount)

Posted on 2013-06-15
15
472 Views
Last Modified: 2013-07-08
After I upgraded to Excel 2010 from 2003 and saved as xlsm I am getting an error msg upon starting this spreadsheet:
Variable not defined

The line highlighted is:
(TimeZoneRecordCount)

The Subroutine:
Private Sub InitializeTimeZoneRecords()

' Initialize the time zone record array.
'
' Syntax
'
' InitializeTimeZoneRecords()

   Dim ScriptObject As Object
   Dim Registry As Object
   Dim KeyList As Variant
   Dim KeyName As Variant
   Dim Value As Variant
   Dim ByteArray() As Byte
   Dim TZI As tTZI
   Dim TimeZoneDisplayName As String
   
   Const HKEY_LOCAL_MACHINE = &H80000002
   Const TimeZonesPath = "SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones"
   
   If mTimeZoneRecordCount > 0 Then Exit Sub

   Set ScriptObject = CreateObject("WScript.Shell")
   Set Registry = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
   Registry.EnumKey HKEY_LOCAL_MACHINE, TimeZonesPath, KeyList
   mTimeZoneRecordCount = 1
   For Each KeyName In KeyList
      'With mTimeZoneRecords(mTimeZoneRecordCount)****************ORIGINAL
      'PROBLEM WITH LINE BELOW
      With mTimeZoneRecords(TimeZoneRecordCount)
         .TimeZoneName = KeyName
         Registry.GetStringValue HKEY_LOCAL_MACHINE, TimeZonesPath & "\" & KeyName, "Display", TimeZoneDisplayName
         .TimeZoneDisplayName = TimeZoneDisplayName
         Registry.GetStringValue HKEY_LOCAL_MACHINE, TimeZonesPath & "\" & KeyName, "Dlt", Value
         ByteArray = Value
         CopyMemory .TimeZoneInformation.DaylightName(0), ByteArray(0), Len(Value) * 2
         Registry.GetStringValue HKEY_LOCAL_MACHINE, TimeZonesPath & "\" & KeyName, "Std", Value
         ByteArray = Value
         CopyMemory .TimeZoneInformation.StandardName(0), ByteArray(0), Len(Value) * 2
         Registry.GetBinaryValue HKEY_LOCAL_MACHINE, TimeZonesPath & "\" & KeyName, "TZI", Value
         ByteArray = GetByteArrayFromVariantByteArray(Value)
         CopyMemory TZI, ByteArray(0), Len(TZI)
         .TimeZoneInformation.Bias = TZI.Bias
         .TimeZoneInformation.DaylightBias = TZI.DaylightBias
         .TimeZoneInformation.DaylightDate = TZI.DaylightDate
         .TimeZoneInformation.StandardBias = TZI.StandardBias
         .TimeZoneInformation.StandardDate = TZI.StandardDate
         mTimeZoneRecordCount = mTimeZoneRecordCount + 1
      End With
   Next KeyName

End Sub

Any idea what might be causing this? I'm hoping this isn't the result of another issue that just happens to show up in this instance.

Thanks
0
Comment
Question by:dgd1212
  • 10
  • 5
15 Comments
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39250389
You don't seem to have TimeZoneRecordCount Dim-ed anywhere. If it's dim-ed someplace else is it defined as Public?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39250393
The same is true for mTimeZoneRecordCount. Are one or both typos? Do you use Option Explicit? If not you should.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39269879
Did either of the above help you?
0
 

Author Comment

by:dgd1212
ID: 39271014
I tried both to no avail. As for placement, it followed other Dim statements but no change in result. Same error message.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39271577
Can you attach your workbook?
0
 

Author Comment

by:dgd1212
ID: 39273672
Workbook attached. Module 13 is where the subroutine is located. I appreciate your looking at this. Thank you.
TEST-PLATFORM-ExpertsExch.xlsm
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39273707
You are getting that error because as it says, you are using a variable (TimeZoneRecordCount) that isn't defined. To stop from getting that error use mTimeZoneRecordCount which is defined or do a Private/Public/ or Dim TimeZoneRecordCount as Integer someplace, but I don't think that's what you want because TimeZoneRecordCount is not used anyplace else whereas mTimeZoneRecordCount is.

Also after getting the variable not defined error I did Debug|Compile VBA project and found several other errors in your code. They are shown here with the 3 corrections marked with 'new:
Sub RenameTabs()
    
  For i = 1 To Sheets.Count
    If Worksheets(i).Range("B3").Value <> "" Then
        Sheets(i).Name = Worksheets(i).Range("B3").Value
        Range("B3").Select
    End If
  Next
    
End Sub
'CREATE HYPER-LINK COLUMN DIRECTORY TO WORKSHEETS
'Sub CreateListOfSheetNames()
'   'Dim i As Integer
'   Dim a, i As Integer
'   a = 0
'   For i = 1 To ThisWorkbook.Worksheets.Count
'   If (Worksheets(i).Visible = True) Then
'        With Worksheets("Demographics").Range("B57")
'            .Hyperlinks.Add .Offset(a, 0), "", "'" & Worksheets(i).Name & "'!B3", , Worksheets(i).Name
'        End With
'       a = a + 1
'    End If
'   Next
'new - commented
'End Sub

Open in new window


Private Sub DoCalculations2()

Dim DayDelta As Long
   
'If mileage btn 65 and 100 miles, subtract 1 day lodging per tech
        'If ['Demographics'!G11] >= 65 And ['Job Details'!G11] <= 100 Then
    If Sheets(1).Range("G11") >= 65 And Sheets(1).Range("G11") <= 100 Then
           DayDelta = -1
    'new added End If
    End If
           
'If mileage btn 101 and 224 miles, 1 day lodging per tech
        'ElseIf ['Demographics'!G11] >= 101 And [B39] <= 224 Then
    'ElseIf Sheets(1).Range("G11") >= 101 And [B39] <= 224 Then
           'DayDelta = 0
           
''If mileage Greater than 225 miles, add 1 day lodging per tech
        'ElseIf ['Demographics'!G11] >= 225 Then
    'ElseIf Sheets(1).Range("G11") >= 225 Then
           'DayDelta = 1
    'End If
   
'I62 is the product of [(# of Days + DayDelta) * (# of Trips) * (# of Techs)] = (PHASE OF INSTALL)
        '[I62] = ((([K5] + DayDelta) * [K7] * [K6]) + _
                 '(([K8] + DayDelta) * [K10] * [K9]) + _
                 '(([K11] + DayDelta) * [K13] * [K12]) + _
                 '(([K14] + DayDelta) * [K16] * [K15]) + _
                 '(([K17] + DayDelta) * [K19] * [K18]))
'I62 is the Total cost incurred for lodging per diem
        '[K62] = [I62] * [J62] 'J62 is per night allotted hotel cost
   
'If mileage is 64 or less than miles, No Lodging cost
        'If ['Demographics'!G11] <= 64 Then
        'If Sheets(1).Range("G11") <= 64 Then
             '[I62] = 0 'Lodging per diem
             '[K62] = 0 'Lodging per diem
        'End If
'new - uncommented
End Sub

Open in new window

0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 45

Expert Comment

by:Martin Liss
ID: 39280130
Did that help?
0
 

Author Comment

by:dgd1212
ID: 39280167
Thanks for your help. My apologies for delayed response. End of quarter rush. Not enough hours in a day.

Tried two things per your suggestion - see end.

  Dim ScriptObject As Object
   Dim Registry As Object
   Dim KeyList As Variant
   Dim KeyName As Variant
   Dim Value As Variant
   Dim ByteArray() As Byte
   Dim TZI As tTZI
   Dim TimeZoneDisplayName As String
   Dim mTimeZoneRecordCount As Integer  '******************ADDED EX_EXCH
   
   Const HKEY_LOCAL_MACHINE = &H80000002
   Const TimeZonesPath = "SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones"
   
   If mTimeZoneRecordCount > 0 Then Exit Sub

   Set ScriptObject = CreateObject("WScript.Shell")
   Set Registry = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
   Registry.EnumKey HKEY_LOCAL_MACHINE, TimeZonesPath, KeyList
   mTimeZoneRecordCount = 1
   For Each KeyName In KeyList
      'With mTimeZoneRecords(mTimeZoneRecordCount) ****************ORIGINAL
      'PROBLEM WITH LINE BELOW
      With mTimeZoneRecords(mTimeZoneRecordCount) '*************ALTERNATIVE TO ABOVE ORIGINAL
         .TimeZoneName = KeyName

Trial #1
Added: Dim mTimeZoneRecordCount As Integer  '******************ADDED EX_EXCH
Added "m": With mTimeZoneRecords(mTimeZoneRecordCount)

Error:
Run-time error '9':
Subscript out of range

Trial #2
With TimeZoneRecords(mTimeZoneRecordCount) '*************ALTERNATIVE TO ABOVE ORIGINAL

Error:
Compile error
Sub or Function not defined

Thanks
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39282379
Okay. Please don't be offended by this question but what is your level of programming experience? I ask because you seem to be flailing around a bit without a lot of understanding. I could be wrong about that and if I am I apologize but for example for some reason you added Dim mTimeZoneRecordCount As Integer when that field is already defined in Module13's Declarations section. Do you understand the concept of a variable's scope? I'm happy to work with you here but I need to understand what you know and what you don't.
0
 

Author Comment

by:dgd1212
ID: 39283822
No offence taken. Experience is basic. This subroutine is, I admit, well beyond my experience level. This routine had actually been created by another EE member quite some time ago.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39284465
I did a search and found what I think is your original thread here.

In that thread the sub you are having a problem with looks like this.

Private Sub InitializeTimeZoneRecords()

' Initialize the time zone record array.
'
' Syntax
'
' InitializeTimeZoneRecords()

   Dim ScriptObject As Object
   Dim Registry As Object
   Dim KeyList As Variant
   Dim KeyName As Variant
   Dim Value As Variant
   Dim ByteArray() As Byte
   Dim TZI As tTZI
   Dim TimeZoneDisplayName As String
   
   Const HKEY_LOCAL_MACHINE = &H80000002
   Const TimeZonesPath = "SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones"
   
   If mTimeZoneRecordCount > 0 Then Exit Sub

   Set ScriptObject = CreateObject("WScript.Shell")
   Set Registry = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
   Registry.EnumKey HKEY_LOCAL_MACHINE, TimeZonesPath, KeyList
   mTimeZoneRecordCount = 1
   For Each KeyName In KeyList
      With mTimeZoneRecords(mTimeZoneRecordCount)
         .TimeZoneName = KeyName
         Registry.GetStringValue HKEY_LOCAL_MACHINE, TimeZonesPath & "\" & KeyName, "Display", TimeZoneDisplayName
         .TimeZoneDisplayName = TimeZoneDisplayName
         Registry.GetStringValue HKEY_LOCAL_MACHINE, TimeZonesPath & "\" & KeyName, "Dlt", Value
         ByteArray = Value
         CopyMemory .TimeZoneInformation.DaylightName(0), ByteArray(0), Len(Value) * 2
         Registry.GetStringValue HKEY_LOCAL_MACHINE, TimeZonesPath & "\" & KeyName, "Std", Value
         ByteArray = Value
         CopyMemory .TimeZoneInformation.StandardName(0), ByteArray(0), Len(Value) * 2
         Registry.GetBinaryValue HKEY_LOCAL_MACHINE, TimeZonesPath & "\" & KeyName, "TZI", Value
         ByteArray = GetByteArrayFromVariantByteArray(Value)
         CopyMemory TZI, ByteArray(0), Len(TZI)
         .TimeZoneInformation.Bias = TZI.Bias
         .TimeZoneInformation.DaylightBias = TZI.DaylightBias
         .TimeZoneInformation.DaylightDate = TZI.DaylightDate
         .TimeZoneInformation.StandardBias = TZI.StandardBias
         .TimeZoneInformation.StandardDate = TZI.StandardDate
         mTimeZoneRecordCount = mTimeZoneRecordCount + 1
      End With
   Next KeyName

End Sub

Open in new window


When I use that Sub and start the wb, I get subscript out of range error at line 28 because mTimeZoneRecordCount is 101 and mTimeZoneRecords is defined as an array with 100 records (Private mTimeZoneRecords(1 To 100) As tTimeZoneRecord). Changing the upper bound of that array to 150 fixed that problem (it actually only needed 104 records on my PC) and the wb opens with no errors. However I can't tell if it does what you want it to do so my suggestion is that you rename your current InitializeTimeZoneRecords to something else so as to preserve it and then copy/paste the above one into Module13. After that change the upper bound of the array to 150, save and close the wb and reopen it. Does the wb now do what you want?

Let's talk about variable names and variable scope for a minute, The programmer who wrote the code for you used good programming practices and used a variable naming convention where prefixing an "m" to the beginning of a a variables's name indicates that that variable is defined in the declarations section of the module where it is used. It's a good idea to do that because then if you see a variable named TimeZoneDisplayName that doesn't have the m you know to look for it's definition in the current sub, but with mTimeZoneRecordCount you know that it is defined in the declarations section of the module where you find it used. You may ask what difference does it make where you define a variable and the answer to that is that it affects the variables scope. Here's the basics of that.

The scope of a variable defined in a Sub or Function is local to that Sub or Function. In other words as far as the rest of the code is concerned it doesn't exist outside of that sub or function and is only available in that sub or function.

The scope of a variable defined as Private in the declarations section of a module is available to all subs and functions in that module, but it is not available outside of that module.

The scope of a variable defined as Public in the declarations section of a module is available everywhere in the workbook. Using the same naming convention that the original programmer used, variables defined as Public should be prefixed with a "g" (meaning global scope) like gMyVarThatsAvailableEverywhere.

Note that if there is a duplications of names that the code assumes that you are referring to the variable that is local to the sub or function where it's found. In other words when you did Dim mTimeZoneRecordCount As Integer  '******************ADDED EX_EXCH in the sub and assuming you left Private mTimeZoneRecordCount As Long in the Declarations section you created a second, distinct, variable and the code in your sub would refer to the sub's variable but everyplace else would refer to the Declarations section variable and they both most likely would have different values.
0
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 39284487
BTW hard-coding the upper bound of an array at 150 is fast and easy to do but you take the chance that someday you may get an out of bounds error because you need 151 and if you really only need 6, you waste some space. There's another, slightly slower, way of handling the upper bound and if you're interested we can talk about it.
0
 

Author Closing Comment

by:dgd1212
ID: 39309098
MartinLiss
My apologies for not getting to this sooner. A much needed long holiday weekend. I am so very happy to say that your solution has worked - and worked perfectly. Wish I could give you 50k points for your assistance on this. Thank you for the explanation of variable names and scope. I'll have to read that a few more times to sink in.
Point taken on the upper limit of 150 where it may take 151. What would be a circumstance that causes that?
Thank you again for your assistance. It is greatly appreciated.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39309114
Point taken on the upper limit of 150 where it may take 151. What would be a circumstance that causes that?
I don't remember your wb very well but there are two common ways of allocating space in an array. One way is to define it originally as MyArr(0) which will hold 1 record and then add 1 to the upper boundary via Redim Preserve each time there's another record to be added. At modern computer speeds the processing overhead required is minimal but some people worry about it so they choose instead to allocate space for what they feel is the most records they ever need by doing something like Dim MyArr(150) which is enough space for 151 records. That eliminates the processing overhead at the cost of wasted memory since for example if they have only 100 records, then 1/3 of the allocated space is wasted. Another more serious problem with this approach is that some day there may be a need for more than 151 records and if so the program will crash when you try to add the 152nd. I don't know your wb well enough to say if that would ever be possible in your case but it's something to think about.

In any case you're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

758 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

19 Experts available now in Live!

Get 1:1 Help Now