Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to store information (2002/SP3)

    I am looking a way to store information and then to call back this information later on in another part of the code using a VBA code. I'm just not sure how this data should be stored. Here is an example. I have two categories: Category 1 has 3 rows of data and category 2 has 5, as follows:

    ABC Category
    Row 1
    Row 2
    Row 3

    XYZ Category
    Row 1
    Row 2
    Row 3
    Row 4
    Row 5

    I want to store this data such that for each category, I can call a procedure that will perform an action for each row in this category. For example, for category 1, I will perform 3 call actions, followed by category 2 where I will perform 5 call actions.

    Any ideas?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How to store information (2002/SP3)

    Do you want to store the information in variables? If so, you could use arrays, e.g.

    Dim arrABC(1 To 3) As String
    arrABC(1) = "Row 1"
    arrABC(2) = "Row 2"
    arrABC(3) = "Row 3"

    Later on you can loop through the items:

    Dim i As Integer
    For i = LBound(arrABC) To UBound(arrABC)
    ' Do something with array element
    Debug.Print arrABC(i)
    Next i

    You can also store the data in cells on a (possibly hidden) worksheet. Or in a text file.

  3. #3
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to store information (2002/SP3)

    Thanks Hans.

    The procedure that I wrote reads the categories and rows from a text file? How do I deal with unknown number of categories and unknown number of rows in an array?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How to store information (2002/SP3)

    If you don't know how many categories there will be, arrays might not be the best solution.

    Can you explain what you want to accomplish? That might focus our ideas.

  5. #5
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to store information (2002/SP3)

    The text file that I'm using to read from can have from 1 to 20 various categories and 1 to 100 rows in each category. I thought of using the array function and declare the array to be fixed using "Dim varArray (1 to 20, 1 to 100, 1 to 100)", where the varArray is a 3-dimensional array containining (category name, test name, test code). One of the problems is that the data enteries in the array can change depending on the input text file. Is there a way to resize the array such that it would contain the actual number of enteries?

    I'm planning to use this array to do this:
    For each "category name" = 1 to UBound("category name")
    For each "test name"" from N = 1 to 1 to UBound("test name")
    For each "test name"" from R = 1 to 1 to UBound("test name")
    Call procedure (M, R)

  6. #6
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to store information (2002/SP3)

    Sorry, in the last post it should read:

    For each "test name"" from N = 1 to 1 to UBound("category name")
    For each "test name"" from R = 1 to 1 to UBound("test name")
    Call procedure (N, R)

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How to store information (2002/SP3)

    It's still not clear to me what you want to do with the data.

  8. #8
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to store information (2002/SP3)

    Sorry for not being clear. Let me see if the following explanation would be better.

    I am a test file (see attached) that contains the temperature readings of a number of tests (specified by the name of the Experiment #) and listed for a number of thermocouple readings (specified by t #). What I want to do is extract specific information from each test. The information that I need is listed in another file called "RelevantTestData.txt" (see attached). Note that I might not be interested in all of the Experiments listed in the test file and not in all the thermocouple/temperature data listed under each Experiment.

    For The example that I provided, the final data would listed in a spreadsheet (see attached).
    Attached Files Attached Files

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How to store information (2002/SP3)

    I'd simply open both text files in Excel.
    Loop sequentially through the cells in column A of relevanttestdata.xls and fill single variables with the names of the experiment, location and number.
    When you have a complete set, create a new column in the outcome sheet, then loop through the cells of column A of testfile.xls and parse the text. Fill the outcome sheet as you go.

  10. #10
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to store information (2002/SP3)

    Thanks Hans, I'll give it a try.

    By the way, I have a question about using the command "ReDim Preserve array()". What are the limitations of using the "ReDim Preserve" command for a 2 or a 3 dimensional array?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How to store information (2002/SP3)

    You can only change the last dimension when you use ReDim Preserve.

    For example:

    Dim arrData()
    ReDim arrData(5, 8)
    ...
    ' The following instruction is valid:
    ReDim Preserve arrData(5, 10)
    ' But this one causes an error:
    ReDim Preserve arrData(7, 8)

  12. #12
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to store information (2002/SP3)

    Is there a way to redimension a multi-dimensional array while still preserving the contents of the array?

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How to store information (2002/SP3)

    Only the last dimension, as I wrote in my previous reply. There is no way to resize the other dimensions while preserving the data.
    You could create a new array with the desired dimensions, and copy the data from the old array to the new one.

  14. #14
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to store information (2002/SP3)

    Thanks Hans :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •