VB.NET RSS VB.NET RSS

Programming in VBA reading file into dynamic array

Please support our VB.NET advertiser: Intel Parallel Studio Home
Reply

•
•
Join Date: Jan 2005
Posts: 56
Reputation: kharri5 is an unknown quantity at this point 
Solved Threads: 0
kharri5's Avatar
kharri5 kharri5 is offline Offline
Junior Poster in Training

Programming in VBA reading file into dynamic array

 
0
  #1
Jan 21st, 2008
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?

VB.NET Syntax (Toggle Plain Text) 
  1.  
  2. 'Name: GetLinesFromText
  3. 'Returns: nothing
  4. 'Description: This opens the file reads it line by line and sends it to be parsed. It then
  5. 'stores the parsed results into the permissionslist array for later use
  6. '
  7. Sub GetLinesFromText()
  8.  
  9.     Dim sFileName As String
  10.     Dim iFileNum As Integer
  11.     Dim sBuf As String
  12.     Dim curEnt As New Entitlements
  13.  
  14.     'For parsiing the line into separate strings
  15.     ReDim Substr(0) As String
  16.     Dim SubStrCount As Integer
  17.     Dim lineCount As Integer
  18.     Dim wordCount As Integer
  19.     Dim i As Integer
  20.     lineCount = 0
  21.     wordCount = 0
  22.  
  23.     ' edit this:
  24.     sFileName = REPORT_DATA_FILE
  25.  
  26.     ' does the file exist?  simpleminded test:
  27.     If Len(Dir$(sFileName)) = 0 Then
  28.         MsgBox ("You didn't input a file at all. Please make sure the package was unzipped to the C: drive directly")
  29.         Exit Sub
  30.     End If
  31.  
  32.     iFileNum = FreeFile()
  33.     Open sFileName For Input As iFileNum
  34.  
  35.     'while not at the end of the file
  36.     Do While Not EOF(iFileNum)
  37.  
  38.         'up the line counter to one (we use this for the dynamic array's size)
  39.         're dimension preserve the array to up its size by 1 more in prep for a new item
  40.         'as well as keep all the items its accumulated so far
  41.         ReDim Preserve permList(lineCount) As Entitlements
  42.         'Set permList(lineCount) = Nothing
  43.         'use line input to get the line
  44.         Line Input #iFileNum, sBuf
  45.         ' now you have the next line of the file stored in sBuf
  46.         ' rip apart sBuf into individual strings, storing the count of them
  47.         ' in SubStrCount. This will be needed to iterate to the right number
  48.         SubStrCount = ParseString(Substr(), sBuf, ",")
  49.         ' the below process will store the sub-strings in permList:
  50.         ' Get the first string. That's a heading topic entitlement
  51.         If (SubStrCount = 5) Then
  52.             curEnt.Category = Substr(1)
  53.             curEnt.Title = Substr(2)
  54.             curEnt.Id = Substr(3)
  55.             curEnt.Enttlmnt = Substr(4)
  56.             curEnt.EntType = Substr(5)
  57.             'plug in the item at the count - 1 position for the 0 based array
  58.         Else
  59.             MsgBox ("Your source text file contains a line with more items than allowed" _
  60.                     & "Please check the source file or use another one in proper format" _
  61.                     & "This presentation will not work as expected unless you start over")
  62.             Exit Sub
  63.         End If
  64.  
  65.         If (lineCount = 1) Then
  66.             MsgBox ("XXXfor permList(0) the first three elems are: " & permList(0).Category & " :: " _
  67.                 & permList(0).Title & " :: " & permList(0).Id)
  68.         End If
  69.         MsgBox (lineCount)
  70.  
  71.         'Me thinks the problem lies right here, but cannot figure out a solution at all
  72.         Set permList(lineCount) = curEnt
  73.  
  74.         MsgBox ("for permList(0) the first three elems are: " & permList(0).Category & " :: " _
  75.                 & permList(0).Title & " :: " & permList(0).Id)
  76.  
  77.         lineCount = lineCount + 1
  78.  
  79.         'IT JUST KEEPS OVERWRITING ALL MY ROWS TO THE ONE THAT WAS LAST
  80.  
  81.         'MsgBox ("The Current ent details are: " & curEnt.Category & " :: " _
  82.         '        & curEnt.Title & " :: " & curEnt.Id & " :: " _
  83.         '        & curEnt.Id & " :: " & curEnt.Enttlmnt & " :: " _
  84.         '        & curEnt.EntType)
  85.     Loop ' we've reached the end of the file now
  86.  
  87.     ' close the file
  88.     Close iFileNum
  89.  
  90.     'after all of this the array now stores the values we will need to fill in the
  91.     'excel sheet with data
  92.     MsgBox ("for permList(0) the first three elems are: " & permList(0).Category & " :: " _
  93.             & permList(0).Title & " :: " & permList(0).Id)
  94.     MsgBox ("for permList(1) the first three elems are: " & permList(1).Category & " :: " _
  95.             & permList(1).Title & " :: " & permList(1).Id)
  96.  
  97. 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,
Reply With Quote Quick reply to this message  
•
•
Join Date: Jan 2005
Posts: 56
Reputation: kharri5 is an unknown quantity at this point 
Solved Threads: 0
kharri5's Avatar
kharri5 kharri5 is offline Offline
Junior Poster in Training

Re: Programming in VBA reading file into dynamic array

 
0
  #2
Jan 22nd, 2008
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

VB Syntax (Toggle Plain Text) 
  1.  
  2. If (SubStrCount = 5) Then
  3.             curEnt.Category = Substr(1)
  4.             curEnt.Title = Substr(2)
  5.             curEnt.Id = Substr(3)
  6.             curEnt.Enttlmnt = Substr(4)
  7.             curEnt.EntType = Substr(5)
  8.         Else
  9.             MsgBox ("Your source text file contains a line with more items than allowed" _
  10.                     & "Please check the source file or use another one in proper format" _
  11.                     & "This presentation will not work as expected unless you start over")
  12.             Exit Function
  13.         End If
  14.  
  15.         'Me thinks the problem lies right here, but cannot figure out a solution at all
  16.         Set permList(lineCount) = curEnt
  17.  
  18.         curEnt.Category = ""
  19.         curEnt.Title = ""
  20.         curEnt.Id = ""
  21.         curEnt.Enttlmnt = ""
  22.         curEnt.EntType = ""
  23.  
  24.         lineCount = lineCount + 1

And after that loop was finished if I printed this:

VB Syntax (Toggle Plain Text) 
  1.  
  2. MsgBox ("for permList(0) the first three elems are: " & permList(0).Category & " :: " _
  3.             & permList(0).Title & " :: " & permList(0).Id)
  4.     MsgBox ("for permList(1) the first three elems are: " & permList(1).Category & " :: " _
  5.             & permList(1).Title & " :: " & permList(1).Id)
  6.     MsgBox ("for permList(2) the first three elems are: " & permList(2).Category & " :: " _
  7.             & 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

VB Syntax (Toggle Plain Text) 
  1.  
  2. Do While Not EOF(iFileNum)
  3.  
  4.         'CRUCUIAL SETTING THIS TO NOTHING...DON'T KNOW WHY
  5.         Set curEnt = Nothing
  6.         'up the line counter to one (we use this for the dynamic array's size)
  7.         're dimension preserve the array to up its size by 1 more in prep for a new item
  8.         'as well as keep all the items its accumulated so far
  9.         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
Reply With Quote Quick reply to this message  
•
•
Join Date: Jun 2009
Posts: 2
Reputation: jpeguet is an unknown quantity at this point 
Solved Threads: 0
jpeguet jpeguet is offline Offline
Newbie Poster

Re: Programming in VBA reading file into dynamic array

 
0
  #3
Jun 28th, 2009
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


•
•
•
•
Originally Posted by kharri5 View Post
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

VB Syntax (Toggle Plain Text) 
  1.  
  2. If (SubStrCount = 5) Then
  3.             curEnt.Category = Substr(1)
  4.             curEnt.Title = Substr(2)
  5.             curEnt.Id = Substr(3)
  6.             curEnt.Enttlmnt = Substr(4)
  7.             curEnt.EntType = Substr(5)
  8.         Else
  9.             MsgBox ("Your source text file contains a line with more items than allowed" _
  10.                     & "Please check the source file or use another one in proper format" _
  11.                     & "This presentation will not work as expected unless you start over")
  12.             Exit Function
  13.         End If
  14.  
  15.         'Me thinks the problem lies right here, but cannot figure out a solution at all
  16.         Set permList(lineCount) = curEnt
  17.  
  18.         curEnt.Category = ""
  19.         curEnt.Title = ""
  20.         curEnt.Id = ""
  21.         curEnt.Enttlmnt = ""
  22.         curEnt.EntType = ""
  23.  
  24.         lineCount = lineCount + 1

And after that loop was finished if I printed this:

VB Syntax (Toggle Plain Text) 
  1.  
  2. MsgBox ("for permList(0) the first three elems are: " & permList(0).Category & " :: " _
  3.             & permList(0).Title & " :: " & permList(0).Id)
  4.     MsgBox ("for permList(1) the first three elems are: " & permList(1).Category & " :: " _
  5.             & permList(1).Title & " :: " & permList(1).Id)
  6.     MsgBox ("for permList(2) the first three elems are: " & permList(2).Category & " :: " _
  7.             & 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

VB Syntax (Toggle Plain Text) 
  1.  
  2. Do While Not EOF(iFileNum)
  3.  
  4.         'CRUCUIAL SETTING THIS TO NOTHING...DON'T KNOW WHY
  5.         Set curEnt = Nothing
  6.         'up the line counter to one (we use this for the dynamic array's size)
  7.         're dimension preserve the array to up its size by 1 more in prep for a new item
  8.         'as well as keep all the items its accumulated so far
  9.         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
Reply With Quote Quick reply to this message  
•
•
Join Date: Jun 2009
Posts: 2
Reputation: jpeguet is an unknown quantity at this point 
Solved Threads: 0
jpeguet jpeguet is offline Offline
Newbie Poster

Re: Programming in VBA reading file into dynamic array

 
0
  #4
Jun 28th, 2009
Edit: I could reproduce the behaviour with Range objects:


VB Syntax (Toggle Plain Text) 
  1. sub ShowInstanceImportace()
  2.  
  3. Dim MyCell as Range
  4. Dim Arr(2) as Range
  5.  
  6. 'Part 1: everything OK
  7. Set MyCell = Range("A1") 'Link MyCell to the instance of object Range("A1") (not a copy)
  8. Set Arr(1) = Mycell 'Link Arr(1) to the instance of object MyCell so it is linked to A1
  9. 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.
  10. Set Arr(2) = Mycell 'Link Arr(2) to the instance of object MyCell so it is linked to Range("A2")
  11. 'Next statement returns "Arr(1): $A$1/ Arr(2): $A$2".
  12. MsgBox "Arr(1): " & Arr(1).Address & " / " & "Arr(2): " & Arr(2).Address
  13.  
  14. 'Part 2: unexpected behaviour
  15. Set MyCell = Range("A1") 'Link MyCell to the instance of object Range("A1") (not a copy)
  16. Set Arr(1) = Mycell 'Link Arr(1) to the instance of object MyCell so it is linked to A1
  17. 'Next line is the only one changed from part 1
  18. 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
  19. Set Arr(2) = Mycell 'Link Arr(2) to the instance of object MyCell so it is linked to Range("A1")
  20. 'Next statement returns "Arr(1): $A$1/ Arr(2): $A$1".
  21. MsgBox "Arr(1): " & Arr(1).Address & " / " & "Arr(2): " & Arr(2).Address
  22.  
  23. End Sub
•
•
•
•
Originally Posted by jpeguet View Post
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
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread is more than three months old.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Other Threads in the VB.NET Forum
Thread Tools Search this Thread



.net 30minutes 2005 2008 access account arithmetic array arrays basic binary bing button buttons c# center check checkbox code combobox component connectionstring convert crystalreport data database databasesearch datagrid datagridview design dissertation dissertations dissertationthesis dropdownlist excel file-dialog folder ftp generatetags google gridview hardcopy image images inline insert intel internet listview mobile monitor ms net networking output passingparameters peertopeervideostreaming picturebox picturebox1 plugin port print printing problem problemwithinstallation project reports" save searchbox searchvb.net select serial server soap sql table tcp text textbox timer toolbox trim update updown user usercontrol vb vb.net vb.netcode vb.netformclosing()eventpictureboxmessagebox vb2008 vbnet view visual visualbasic visualbasic.net visualstudio visualstudio2008 web wpf
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC