Error of variable not defined (TimeZoneRecordCount)

You don't seem to have TimeZoneRecordCount Dim-ed anywhere. If it's dim-ed someplace else is it defined as Public?

The same is true for mTimeZoneRecordCount. Are one or both typos? Do you use Option Explicit? If not you should.

Did either of the above help you?

I tried both to no avail. As for placement, it followed other Dim statements but no change in result. Same error message.

Can you attach your workbook?

Workbook attached. Module 13 is where the subroutine is located. I appreciate your looking at this. Thank you.
TEST-PLATFORM-ExpertsExch.xlsm

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

Select allOpen 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

Select allOpen in new window

Did that help?

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

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.

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.

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

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

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.

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