Solved

Trouble assigning value to private variable using property let

Posted on 2011-09-09
7
278 Views
Last Modified: 2012-05-12
I'm having difficulty assigning a value to a private variable within a class.  When the code runs pImportKWH retains it's zero value.  If I convert the value into a local currency variable within the property I am able to confirm that the conversion from string to currency is occurring correctly. When I try to change the value in the immediate window with "pImportKWH = 4" the locals window shows the value of pImportKWH remaining at zero.  Let me know if I can provide any more information.

Private pImportKWH As Currency

Property Let KWH(ByVal value As String)
   If IsNumeric(value) Then
      pImportKWH = CCur(value)
   Else
      pImportKWH = 0
   End If
End Property
0
Comment
Question by:AndyH79
  • 5
  • 2
7 Comments
 
LVL 80

Expert Comment

by:byundt
ID: 36514321
I put the following code in a regular module sheet:

Sub Test()
Dim Cust As New Class1      'The property code is in a class module with default name Class1
Cust.KWH = "a"
Cust.KWH = "984"
End Sub

Open in new window


and modified your Property code like this:
Private pImportKWH As Currency

Property Let KWH(ByVal value As String)
   If IsNumeric(value) Then
      pImportKWH = CCur(value)
   Else
      pImportKWH = 0
   End If
   MsgBox pImportKWH         'Note this added statement
End Property

Open in new window


When I ran sub Test, two MsgBox displayed. The first had 0 and the second had 984.
0
 

Author Comment

by:AndyH79
ID: 36514437
It sound's like you were not able to duplicate the problem since that would have been the result that I expected.Would there be other factors that would cause this to occur?  The actual class is:

 
Option Explicit

Private pImportKW As Currency
Private p15minKW As Currency
Private pImportKWH As Currency
Private p15minKWH As Currency
Private pImportDuration As Integer
Public Units As String           '"KW" or "KWH"
Public Channel As Integer        'Used for data vendors where meter has multiple channels (kWh & kVar)
Public Direction As String       'This is used to indicate if the 12:15 interval represents usage from 12:00:01 to 12:15:00(PREVIOUS) or from 12:15:00 to 12:29:59 (NEXT)

Property Let KW(ByVal value As Variant)
   If IsNumeric(value) Then
      pKW = CCur(value)
   Else
      pKW = 0
   End If
End Property

Property Get KW() As Variant
   KW = pKW
End Property

Property Let KWH(ByVal value As String)
   Dim CurrencyValue As Currency
   Dim VariantValue As Variant

   If IsNumeric(value) Then
      VariantValue = CCur(value)
      CurrencyValue = VariantValue
      pImportKWH = 2
      pImportKWH = CCur(value)
   Else
      pImportKWH = 0
   End If
   MsgBox pImportKWH
End Property

Property Get KWH_Import() As Currency
   KWH = pImportKWH
End Property

Property Get KWH_15Min() As Currency
   KWH = p15minKWH
End Property

Property Let ImportDuration(value As Integer)
   pImportDuration = value
   Select Case pImportDuration
         Case 15
             p15minKWH = pImportKWH
             pKW = pImportKWH * 4
         Case 30
             p15minKWH = pImportKWH / 2
             pKW = pImportKWH * 2
         Case 60
             p15minKWH = pImportKWH / 4
             pKW = pImportKWH
   End Select
End Property

Property Get ImportDuration() As Integer
   ImportDuration = pImportDuration
End Property

Open in new window


Which is a child class of:

 
Option Explicit

Public Key As String                'Key for interval in the collection which it resides
Public IndexNumber As Long
Private pIntervalSource As Integer    'How interval was generated. Vendor Supplied, Duration Filled or Missing Interval
Private pIntervalSourceName As String
Public File As C90_File
Public Meter As C90_Meter
Public DateTime As C90_Interval_DateTime
Public Usage As C90_Interval_Usage
Public Export As C90_Interval_Export
Private pMeterCode As String     'Meter code provided by Data Vendor to identify meter

Public Sub Class_Initialize()
Set DateTime = New C90_Interval_DateTime
Set Usage = New C90_Interval_Usage
Set Export = New C90_Interval_Export
End Sub

Property Let MeterCode(ByVal value As String)
   pMeterCode = value
   Call SetMeter(value)
End Property

Property Get MeterCode() As String
   MeterCode = pMeterCode
End Property

Private Sub SetMeter(ByVal value As String)
Dim tempMeter As C90_Meter

For Each tempMeter In Globals.colMeters
   If tempMeter.MeterCode = value Then
      Set Meter = tempMeter
      Exit For
   End If
Next tempMeter
End Sub

Property Let IntervalSource(ByVal value As String)
   pIntervalSourceName = value
   pIntervalSource = Sql.LoadTypeKey(1, "Interval Source", value)
   
End Property

Open in new window


Which is used by multiple data vendor specific classes similar to:

 
Sub Convert(ByRef File As C90_File)
   Dim Interval As C90_Interval
   Dim objPrevInterval As C90_Interval
   Dim ImportDuration As Integer
   
   With File.Data
      .GetNewLine
      File.Units = UCase(.GetLineArrayMember(2))
      .GetNewLine
      
      File.EndOfFile = False
      Do
         If File.Units = "KWH" Then
            Set Interval = New C90_Interval
             
            Interval.MeterCode = File.Data.GetLineArrayMember(0)
            Select Case Interval.MeterCode
               Case "JCP-2055"
                  'Do nothing.
                  'AEI does not need to provide us with data
                  'for JCP-2055 since is harvested by FirstPoint
               Case Else
                 Interval.DateTime.DateTime = CDate(.GetLineArrayMember(1))
                 Interval.Usage.KWH = .GetLineArrayMember(2)
                 Interval.Key = CStr(Interval.MeterCode & "," & Interval.DateTime.YYYYMMDDHHMMSS)
                 Call .Intervals.Add(Interval, "Imported")
            End Select
         End If
         Call .CheckForEndOfFileBeforeGettingNewLine
         
         Set Interval = Nothing
      Loop Until File.EndOfFile = True
   End With
End Sub

Open in new window

0
 
LVL 80

Expert Comment

by:byundt
ID: 36517238
What are the names of the three classes that you posted?
What are you trying to do with the classes that you have been unable to succeed with?
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.

 

Author Comment

by:AndyH79
ID: 36519281
First class is C90_Interval_Usage
Second class is C90_Interval
Third class is a C22_Data_AEI  which is an data vendor specific class called by:
 
Public Sub AddItemsFromAllFiles(ByRef DataVendor As C90_DataVendor)
    Dim File As C90_File

    If DebugFlag = False Then On Error GoTo ErrHandler:
    
    For Each File In DataVendor.colConvertFiles
        Call Debugger.DisplayFileStatus(File, DataVendor.colConvertFiles)
        
        Call File.Data.LoadFileIntoArray(File.Path)

        ' Begin import of Lines() array
        Select Case File.DataVendor.DataFormat.Key
            Case 1
               Call Data_AEI.Convert(File)
            Case 2
               Call Data_Allegheny.Convert(File)
            Case 3
               Call Data_Elutions.Convert(File)
            Case 4
               Call Data_Enerwise.Convert(File)
            Case 5
               Call Data_FirstPoint.Convert(File)
            Case 6
               Call Data_Flint.Convert(File)
            Case 7
               Call Data_ItronIEE_V4.Convert(File)
            Case 8
               Call Data_ItronIEE_V6.Convert(File)
            Case 9
               Call Data_ItronMVWeb.Convert(File)
            Case 11
               Call Data_ItronEEM_V3_5.Convert(File)
            Case 12
               Call Data_MeterSmartPPW_V5.Convert(File)
            Case 13
               Call Data_PGE.Convert(File)
            Case 14
               Call Data_Aclara.Convert(File)
            Case 15
               Call Data_ProgressEnergy.Convert(File)
            Case 16
               Call Data_SCE.Convert(File)
            Case 17
               Call Data_Schneider_EPO.Convert(File)
            Case 18
               Call Data_Schneider_Ion.Convert(File)
            Case 19
               Call Data_Schneider_IonEEM.Convert(File)
            Case 20
               Call Data_SunPower.Convert(File)
            Case 21
               Call Data_WE_Energy.Convert(File)
            Case 22
               Call Data_Xcel.Convert(File)
            Case 37
               Call Data_InsightView.Convert(File)
        End Select
        Call Debugger.DisplayFileIntervals(File, DataVendor.colConvertFiles)
        
End Sub

Open in new window


Essentially what is happening is each data vendor 's folder is reviewed for new files to convert since each data vendor's format is different.  Based upon the Select Case in the subroutine AddItemsFromAllFiles the file is directed to be processed by a specific data vendor's conversion routine. In this instance the conversion routine is Data_AEI.Convert which is the Convert subroutine in C22_Data_AEI. During the conversion of the file a new C90_Interval object is created for each line in the file. The Interval object contains child objects of type C90_Interval_DateTime which has a child C90_Interval_DateTime_DatePart and C90_Interval_Usage and C90_Interval_Export both of which have no child objects.  
The original application that I wrote a couple of years ago had all the variables associated with these 5 objects contained within just the C90_Interval object which made for a confusing debugging process due to there being > 50 variables that needed to be maintained per Interval, some with very similar names. In addition they were all implemented as Public variables so that all of my format validation had to be done in each individual data vendor's conversion subroutine.
In this new version of the application I've moved all the format validation and data lookup processes to within property let statements to set values on private variables in the interval parent and children objects.  I was able to successfully implement the C90_Interval_DateTime and C90_Interval_DateTime_DatePart objects in this manner and don't understand why I'm having difficulties setting values in C90_Interval_Usage since they have been implemented in a similar fashion(Created as part of the C90_Interval's Class_Initialize subroutine).  All I'm trying to do is have the value passed to "Property Let KWH" in C90_Interval_Usage set the value of the private variable pImportKWH to either the value passed or zero based upon the IsNumeric check. When I run the code, the sample file's first KWH value is "13.704".  I've verified with Len(Value) that this is equal to 6 so I know that there isn't a tab in there messing up my CCur datatype conversion.  When I run the Property Let KWH the IsNumeric(value) test passes so it should be setting pImportKWH = 13.704 but when the line pImportKWH=CCur value executes the value of pImportKWH is still zero. ?CCur(value) in the immediate window does result in 13.704 so it doesn't appear to be a conversion problem. I've also tried implementing this as Double rather than Currency with no better success.

I can't send the whole application since it wouldn't run on a machine that didn't have the necessary directory structure and can't release our connection string.  However, I could set up a goto meeting so that you can watch the execution run live if that would be helpful.
0
 

Author Comment

by:AndyH79
ID: 36523298
I tried copying the following code from C90_Interval_Usage to C90_Interval.
 
Private pImportKWH As Currency
Property Let KWH(ByVal value As String)
   Dim CurrencyValue As Currency
   Dim VariantValue As Variant

   If IsNumeric(value) Then
      pImportKWH = CCur(value)
   Else
      pImportKWH = 0
   End If
   'MsgBox pImportKWH
End Property

Open in new window


I then changed the Convert subroutine to enable the setting of the value in the interval object and the usage object with:

 
Interval.KWH = .GetLineArrayMember(2)
Interval.Usage.KWH = .GetLineArrayMember(2)

Open in new window


For some reason setting the value to Interval.KWH works correctly. Setting the value to Interval.Usage.KWH does not work correctly.  The IsNumeric test passes but pImportKWH = CCur(value) leaves the value of pImportKWH as zero rather than changing it to 13.704 like it should do.  If I change pImportKWH to a public variable in C90_Interval_Usage and change the code in the C90_Interval to Usage.pImportKWH = CCur(value) the value of Interval.Usage.pImportKWH does not change.

I've never ran into a problem before where VBA will have identical code work in one class and not the other.  It's seems like the C90_Interval_Usage object which was created is locked and cannot be edited.  Never heard of this being possible. I would think it was a problem related to nested objects if I wasn't able to successfully set values to Interval.DataTime.DatePart properties and public variables.  Any ideas?
0
 

Accepted Solution

by:
AndyH79 earned 0 total points
ID: 36523496
Just discovered that the issue appears to be related to the 2 get statements:
 
Property Get KWH_Import() As Currency
   KWH = pImportKWH
End Property

Property Get KWH_15Min() As Currency
   KWH = p15minKWH
End Property

Open in new window


The should have been listed as:
 
Property Get KWH_Import() As Currency
   KWH_Import = pImportKWH
End Property

Property Get KWH_15Min() As Currency
   KWH_15Min = p15minKWH
End Property

Open in new window


After this change the code works correctly.
0
 

Author Closing Comment

by:AndyH79
ID: 36553459
Solved my own issue.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

746 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

13 Experts available now in Live!

Get 1:1 Help Now