Programming in VBA reading file into dynamic array

[kharri5]

Hello,

I am currently writing a powerpoint presentation that is an interactive survey. It takes in a file of data, and adds it to a big dynamic array. When the user answers questions it picks the data from the array and populates an excel spreadsheet for a final output of a spreadsheet of that data. The problem I am having is taking in the data. I take file line by line, and each line is separated by commas another function rips that line apart to store each string chunk in the class Entitlements variables. I then want to put this Entitlements object into the array. It always puts only the last line for all the lines when it finishes. What am I doing wrong?

'Name: GetLinesFromText
'Returns: nothing
'Description: This opens the file reads it line by line and sends it to be parsed. It then
'stores the parsed results into the permissionslist array for later use
'
Sub GetLinesFromText()

    Dim sFileName As String
    Dim iFileNum As Integer
    Dim sBuf As String
    Dim curEnt As New Entitlements
    
    'For parsiing the line into separate strings
    ReDim Substr(0) As String
    Dim SubStrCount As Integer
    Dim lineCount As Integer
    Dim wordCount As Integer
    Dim i As Integer
    lineCount = 0
    wordCount = 0

    ' edit this:
    sFileName = REPORT_DATA_FILE

    ' does the file exist?  simpleminded test:
    If Len(Dir$(sFileName)) = 0 Then
        MsgBox ("You didn't input a file at all. Please make sure the package was unzipped to the C: drive directly")
        Exit Sub
    End If

    iFileNum = FreeFile()
    Open sFileName For Input As iFileNum

    'while not at the end of the file
    Do While Not EOF(iFileNum)
        
        'up the line counter to one (we use this for the dynamic array's size)
        're dimension preserve the array to up its size by 1 more in prep for a new item
        'as well as keep all the items its accumulated so far
        ReDim Preserve permList(lineCount) As Entitlements
        'Set permList(lineCount) = Nothing
        'use line input to get the line
        Line Input #iFileNum, sBuf
        ' now you have the next line of the file stored in sBuf
        ' rip apart sBuf into individual strings, storing the count of them
        ' in SubStrCount. This will be needed to iterate to the right number
        SubStrCount = ParseString(Substr(), sBuf, ",")
        ' the below process will store the sub-strings in permList:
        ' Get the first string. That's a heading topic entitlement
        If (SubStrCount = 5) Then
            curEnt.Category = Substr(1)
            curEnt.Title = Substr(2)
            curEnt.Id = Substr(3)
            curEnt.Enttlmnt = Substr(4)
            curEnt.EntType = Substr(5)
            'plug in the item at the count - 1 position for the 0 based array
        Else
            MsgBox ("Your source text file contains a line with more items than allowed" _
                    & "Please check the source file or use another one in proper format" _
                    & "This presentation will not work as expected unless you start over")
            Exit Sub
        End If
        
        If (lineCount = 1) Then
            MsgBox ("XXXfor permList(0) the first three elems are: " & permList(0).Category & " :: " _
                & permList(0).Title & " :: " & permList(0).Id)
        End If
        MsgBox (lineCount)
        
        'Me thinks the problem lies right here, but cannot figure out a solution at all
        Set permList(lineCount) = curEnt
        
        MsgBox ("for permList(0) the first three elems are: " & permList(0).Category & " :: " _
                & permList(0).Title & " :: " & permList(0).Id)
        
        lineCount = lineCount + 1
        
        'IT JUST KEEPS OVERWRITING ALL MY ROWS TO THE ONE THAT WAS LAST
        
        'MsgBox ("The Current ent details are: " & curEnt.Category & " :: " _
        '        & curEnt.Title & " :: " & curEnt.Id & " :: " _
        '        & curEnt.Id & " :: " & curEnt.Enttlmnt & " :: " _
        '        & curEnt.EntType)
    Loop ' we've reached the end of the file now
    
    ' close the file
    Close iFileNum
    
    'after all of this the array now stores the values we will need to fill in the
    'excel sheet with data
    MsgBox ("for permList(0) the first three elems are: " & permList(0).Category & " :: " _
            & permList(0).Title & " :: " & permList(0).Id)
    MsgBox ("for permList(1) the first three elems are: " & permList(1).Category & " :: " _
            & permList(1).Title & " :: " & permList(1).Id)
    
End Sub

If anyone can help me I'd greatly appreciate it. I've been banging my head on it for a week or two now.

kharri5,

[kharri5]

I see that a number of people viewed this but didn't care enough about (or have a solution to) the problem I posted (i admit it wasn't a ton of time true since the initial inquiry). Anywhooo, I figured it out so for those in the future who wish to know.

It was setting the array values to whatever curEnt was last time in the loop, so regardless
of what I did, it would set all array items to curEnt's last value. For instance if I did this

If (SubStrCount = 5) Then
            curEnt.Category = Substr(1)
            curEnt.Title = Substr(2)
            curEnt.Id = Substr(3)
            curEnt.Enttlmnt = Substr(4)
            curEnt.EntType = Substr(5)
        Else
            MsgBox ("Your source text file contains a line with more items than allowed" _
                    & "Please check the source file or use another one in proper format" _
                    & "This presentation will not work as expected unless you start over")
            Exit Function
        End If
        
        'Me thinks the problem lies right here, but cannot figure out a solution at all
        Set permList(lineCount) = curEnt

        curEnt.Category = ""
        curEnt.Title = ""
        curEnt.Id = ""
        curEnt.Enttlmnt = ""
        curEnt.EntType = ""
        
        lineCount = lineCount + 1

And after that loop was finished if I printed this:

MsgBox ("for permList(0) the first three elems are: " & permList(0).Category & " :: " _
            & permList(0).Title & " :: " & permList(0).Id)
    MsgBox ("for permList(1) the first three elems are: " & permList(1).Category & " :: " _
            & permList(1).Title & " :: " & permList(1).Id)
    MsgBox ("for permList(2) the first three elems are: " & permList(2).Category & " :: " _
            & permList(2).Title & " :: " & permList(2).Id)

I'd get "" :: "" :: ""

Despite having set this AFTER I put curEnt into the array and not setting the array's values to those blanks at all. WTF???

The solution?

I set curEnt = Nothing at the top of the loop

Do While Not EOF(iFileNum)

        'CRUCUIAL SETTING THIS TO NOTHING...DON'T KNOW WHY
        Set curEnt = Nothing
        'up the line counter to one (we use this for the dynamic array's size)
        're dimension preserve the array to up its size by 1 more in prep for a new item
        'as well as keep all the items its accumulated so far
        ReDim Preserve permList(lineCount) As Entitlements

I have no earthly clue why that fixed it...which is terrible I know...someone want to shed some light on me??

Because tho it's fixed, I'm lost as to the reason for it.

kharri5

[jpeguet]

Hello

it looks like you just give a reference to your curEnt object and not save a copy of it in your array. I did'nt know VBA worked liked that in this case but this behaviour is frequent in object programming languages. For example, it is the same for arguments of a VBA function: you can choose between ByVal and ByRef. ByVal gives a copy af the given variable to the function. Any changes made to this copy don't affect the variable. ByRef gives a reference of the object (you can see it as its adress in memory), the changes made by the function to the variable are permanent, even after the function finishes.

Look in VBA help (F1) the "Set" instruction, it explains that it creates a new instances of a class or if it is used (as you did) with "Nothing" like "Set xyz = Nothing" it deletes the instance. Then when you write "curEnt.Category = ..." a new instance is created. I think your code should work if you replace "set curEnt = Nothing" by "Set curEnt = New Entitlements" and it would be easier to understand.

I was'nt able to reproduce this behaviour. Is "Entitlements" a class or a struct that you defined?

Jean-Hugues

I see that a number of people viewed this but didn't care enough about (or have a solution to) the problem I posted (i admit it wasn't a ton of time true since the initial inquiry). Anywhooo, I figured it out so for those in the future who wish to know.

It was setting the array values to whatever curEnt was last time in the loop, so regardless of what I did, it would set all array items to curEnt's last value. For instance if I did this

If (SubStrCount = 5) Then
            curEnt.Category = Substr(1)
            curEnt.Title = Substr(2)
            curEnt.Id = Substr(3)
            curEnt.Enttlmnt = Substr(4)
            curEnt.EntType = Substr(5)
        Else
            MsgBox ("Your source text file contains a line with more items than allowed" _
                    & "Please check the source file or use another one in proper format" _
                    & "This presentation will not work as expected unless you start over")
            Exit Function
        End If
        
        'Me thinks the problem lies right here, but cannot figure out a solution at all
        Set permList(lineCount) = curEnt

        curEnt.Category = ""
        curEnt.Title = ""
        curEnt.Id = ""
        curEnt.Enttlmnt = ""
        curEnt.EntType = ""
        
        lineCount = lineCount + 1

And after that loop was finished if I printed this:

MsgBox ("for permList(0) the first three elems are: " & permList(0).Category & " :: " _
            & permList(0).Title & " :: " & permList(0).Id)
    MsgBox ("for permList(1) the first three elems are: " & permList(1).Category & " :: " _
            & permList(1).Title & " :: " & permList(1).Id)
    MsgBox ("for permList(2) the first three elems are: " & permList(2).Category & " :: " _
            & permList(2).Title & " :: " & permList(2).Id)

I'd get "" :: "" :: ""

Despite having set this AFTER I put curEnt into the array and not setting the array's values to those blanks at all. WTF???

The solution?

I set curEnt = Nothing at the top of the loop

Do While Not EOF(iFileNum)

        'CRUCUIAL SETTING THIS TO NOTHING...DON'T KNOW WHY
        Set curEnt = Nothing
        'up the line counter to one (we use this for the dynamic array's size)
        're dimension preserve the array to up its size by 1 more in prep for a new item
        'as well as keep all the items its accumulated so far
        ReDim Preserve permList(lineCount) As Entitlements

I have no earthly clue why that fixed it...which is terrible I know...someone want to shed some light on me??

Because tho it's fixed, I'm lost as to the reason for it.

kharri5

[jpeguet]

Edit: I could reproduce the behaviour with Range objects:

sub ShowInstanceImportace()

Dim MyCell as Range
Dim Arr(2) as Range

'Part 1: everything OK
Set MyCell = Range("A1") 'Link MyCell to the instance of object Range("A1") (not a copy)
Set Arr(1) = Mycell 'Link Arr(1) to the instance of object MyCell so it is linked to A1
Set MyCell = Range("A2") 'Witht "Set": the existing instance of MyCell is deleted, a new one is created and linked to cell A2 then Arr(1) is still linked to A1.
Set Arr(2) = Mycell 'Link Arr(2) to the instance of object MyCell so it is linked to Range("A2")
'Next statement returns "Arr(1): $A$1/ Arr(2): $A$2".
MsgBox "Arr(1): " & Arr(1).Address & " / " & "Arr(2): " & Arr(2).Address

'Part 2: unexpected behaviour
Set MyCell = Range("A1") 'Link MyCell to the instance of object Range("A1") (not a copy)
Set Arr(1) = Mycell 'Link Arr(1) to the instance of object MyCell so it is linked to A1
'Next line is the only one changed from part 1
MyCell = Range("A2") 'Without "Set": the existing instance of MyCell is still linked to cell A1, this statement just affects the value of A2 in A1
Set Arr(2) = Mycell 'Link Arr(2) to the instance of object MyCell so it is linked to Range("A1")
'Next statement returns "Arr(1): $A$1/ Arr(2): $A$1".
MsgBox "Arr(1): " & Arr(1).Address & " / " & "Arr(2): " & Arr(2).Address

End Sub

Hello

it looks like you just give a reference to your curEnt object and not save a copy of it in your array. I did'nt know VBA worked liked that in this case but this behaviour is frequent in object programming languages. For example, it is the same for arguments of a VBA function: you can choose between ByVal and ByRef. ByVal gives a copy af the given variable to the function. Any changes made to this copy don't affect the variable. ByRef gives a reference of the object (you can see it as its adress in memory), the changes made by the function to the variable are permanent, even after the function finishes.

Look in VBA help (F1) the "Set" instruction, it explains that it creates a new instances of a class or if it is used (as you did) with "Nothing" like "Set xyz = Nothing" it deletes the instance. Then when you write "curEnt.Category = ..." a new instance is created. I think your code should work if you replace "set curEnt = Nothing" by "Set curEnt = New Entitlements" and it would be easier to understand.

I was'nt able to reproduce this behaviour. Is "Entitlements" a class or a struct that you defined?

Jean-Hugues