Infinite Loop

[tiredofit]

I have a column of data in column A. All I want to do is add zeros if the length is less than 5, eg 1 becomes 00001.

This code gets stuck in an infinite loop:

Public Sub UsingArray()
     
    Dim OrigVals As Variant
 
    OrigVals = Sheet1.Cells(1, 1).CurrentRegion.Value
 
    Dim a As Class1
    Set a = New Class1
 
    With a
     
        Set .ws = Sheet1
     
        .DataArray = OrigVals
     
        Call .CreateList

    End With
 
End Sub

This is Class1:

Private pDataArray As Variant
 
    Private pws As Worksheet

Public Property Get DataArray() As Variant

    DataArray = pDataArray
 
End Property

Public Property Let DataArray(ByVal DArray As Variant)

    pDataArray = DArray
 
End Property

Public Property Get ws() As Worksheet

    Set ws = pws
 
End Property

Public Property Set ws(ByVal w As Worksheet)

    Set pws = w
 
End Property

Public Sub CreateList()
    
    Dim DataArrayRows As Long
 
    DataArrayRows = UBound(Me.DataArray, 1)
 
    Dim Counter As Long
 
    For Counter = 1 To DataArrayRows
 
        If Len(Me.DataArray(Counter, 1)) < 5 Then
     
            Do Until Len(Me.DataArray(Counter, 1)) = 5
         
                Me.DataArray(Counter, 1) = "0" & Me.DataArray(Counter, 1)
         
            Loop
     
        End If
    
    Next Counter
 
    Me.ws.Cells(1, 3).Resize(DataArrayRows, 1).Value = Me.DataArray
 
End Sub

I have discovered I have to change this line from:

    Me.DataArray(Counter, 1) = "0" & Me.DataArray(Counter, 1)

to:

    pDataArray(Counter, 1) = "0" & Me.DataArray(Counter, 1)

to make it work.


However, the following code (using strings) does work:

Public Sub UsingString()

    Dim Val As String
 
    Val = Sheet1.Cells(1, 1).Value

    Dim b As Class2
    Set b = New Class2
 
    With b
 
        Set .ws = Sheet1
     
        .Val = Val
     
        Call .CreateList
 
    End With
 
End Sub

Class2:

    Private pVal As String
    Private pws As Worksheet

Public Property Get Val() As String

    Val = pVal
 
End Property

Public Property Let Val(ByVal V As String)

    pVal = V
 
End Property

Public Property Get ws() As Worksheet

    Set ws = pws
 
End Property

Public Property Set ws(ByVal w As Worksheet)

    Set pws = w
 
End Property

Public Sub CreateList()

    If Len(Me.Val) < 5 Then
     
        Do Until Len(Me.Val) = 5
     
            Me.Val = "0" & Me.Val
     
        Loop
 
    End If
    
    Me.ws.Cells(1, 5).Value = Me.Val
 
End Sub

Can someone pleae explain why I needed to change:

Me.DataArray(Counter, 1) = "0" & Me.DataArray(Counter, 1)

to

p.DataArray(Counter, 1) = "0" & Me.DataArray(Counter, 1)

to make it work using arrays but I didn't have to change anything if I use string?

Thanks

 

[RoryA]

You will notice that this line:

               Me.DataArray(Counter, 1) = "0" & Me.DataArray(Counter, 1)

does not call your Property Let routine. It calls the Property Get twice, which should tell you that your code is working with copies of the array. Your property Let only allows for assigning the entire array, not individual items.

 

[tiredofit]

You will notice that this line:

               Me.DataArray(Counter, 1) = "0" & Me.DataArray(Counter, 1)

does not call your Property Let routine. It calls the Property Get twice, which should tell you that your code is working with copies of the array. Your property Let only allows for assigning the entire array, not individual items.

Thanks for the explantion.

So how would you assign individual items? Change it to pDataArray(Counter, 1) as I have suggested or should something else be done?

 

[RoryA]

Since it's a purely internal process, you might as well just use the variable directly as you have, unless there might be any time you want external code to be able to set a specific value (without having to extract the entire array, manipulate it, then put it back).

 

[tiredofit]

Thanks.

I tried adding something like this:

Property Get DataArrayItem(RowIndex, ColIndex) As Variant

    DataArrayItem = DataArray(RowIndex, ColIndex)
    
End Property

but got into a right mess!

 

[RoryA]

Not much I can say to that.

 

[tiredofit]

Not much I can say to that.

I think I got it to work using this:

Public Sub UsingArray()
 
    Dim a As Class1
    Set a = New Class1
 
    Set a.ws = Sheet1
 
    Dim i As Long
 
    For i = 1 To 5
 
        a.DataArrayItem(i, 1) = Sheet1.Cells(i, 1).Value
     
    Next i
 
    Call a.CreateList

End Sub

Class1:

    Private pDataArrayItem() As Variant
    Private pws As Worksheet

Private Sub Class_Initialize()

    ReDim pDataArrayItem(1 To 5, 1 To 1) As Variant

End Sub

Property Get DataArrayItem(ByVal RowIndex As Variant, ByVal ColIndex As Variant) As Variant

    DataArrayItem = pDataArrayItem(RowIndex, ColIndex)

End Property

Public Property Let DataArrayItem(ByVal RowIndex As Variant, ByVal ColIndex As Variant, MArray As Variant)

    pDataArrayItem(RowIndex, ColIndex) = MArray
 
End Property


Public Property Get ws() As Worksheet

    Set ws = pws
 
End Property

Public Property Set ws(ByVal w As Worksheet)

    Set pws = w
 
End Property

Public Sub CreateList()
 
    Dim Counter As Long

    For Counter = 1 To 5

        If Len(Me.DataArrayItem(Counter, 1)) < 5 Then

            Do Until Len(Me.DataArrayItem(Counter, 1)) = 5

                Me.DataArrayItem(Counter, 1) = "0" & Me.DataArrayItem(Counter, 1)

            Loop

        End If

    Next Counter

    For Counter = 1 To 5
 
        Me.ws.Cells(Counter, 5).Value = Me.DataArrayItem(Counter, 1)
 
    Next Counter
 
End Sub

Note this line:

 Me.DataArrayItem(Counter, 1) = "0" & Me.DataArrayItem(Counter, 1)

DOESN'T use p (as I wanted all along).

but it seems I have to loop to return the values to the worksheet, as opposed to using Resize.

So is this an alternative to my original solution?

 

[RoryA]

It's an alternative, certainly. I don't see why it would be preferable though. All you really needed was to add properties to the original to read/write specific array elements, not remove the ability to read/write the array in one go.

 

[tiredofit]

It's an alternative, certainly. I don't see why it would be preferable though. All you really needed was to add properties to the original to read/write specific array elements, not remove the ability to read/write the array in one go.

"All you really needed was to add properties to the original to read/write specific array elements, not remove the ability to read/write the array in one go."

Can you show me how that's done?

 

[RoryA]

For example:

Option Explicit

Private pDataArray As Variant
 
    Private pws As Worksheet

Public Property Get DataArray() As Variant

    DataArray = pDataArray
 
End Property

Public Property Let DataArray(ByVal DArray As Variant)

    pDataArray = DArray
 
End Property
Property Get DataArrayItem(ByVal RowIndex As Variant, ByVal ColIndex As Variant) As Variant

    DataArrayItem = pDataArray(RowIndex, ColIndex)

End Property

Public Property Let DataArrayItem(ByVal RowIndex As Variant, ByVal ColIndex As Variant, MArray As Variant)

    pDataArray(RowIndex, ColIndex) = MArray
   
End Property
Public Property Get ws() As Worksheet

    Set ws = pws
 
End Property

Public Property Set ws(ByVal w As Worksheet)

    Set pws = w
 
End Property

Public Sub CreateList()
    
    Dim DataArrayRows As Long
 
    DataArrayRows = UBound(Me.DataArray, 1)
 
    Dim Counter As Long
 
    For Counter = 1 To DataArrayRows
 
        If Len(Me.DataArrayItem(Counter, 1)) < 5 Then
     
            Do Until Len(Me.DataArrayItem(Counter, 1)) = 5
         
                Me.DataArrayItem(Counter, 1) = "0" & Me.DataArrayItem(Counter, 1)
         
            Loop
     
        End If
    
    Next Counter
 
    Me.ws.Cells(1, 3).Resize(DataArrayRows, 1).Value = Me.DataArray
 
End Sub