storing data in VBA, maybe using arrays?

[ultine]

I would like to store user input as an array. I'm using Excel 07 on a PC running WinVista. I'm making an application with UserForms. I want the user to be able to select a day on the calendar (works fine, no problem there), and then in a text box they can enter an item (I should be ok this far, too, although I haven't done it yet). Then the item will be "selectable" (probably from a listBox or something). Now, the item will have several variables that can be assigned by the user as well, such as "completed", "urgent", etc. So with my limited experience it looks like I could use either an array or classes or maybe a combination. I'm thinking I could use Class to define a class called "item" for instance, and the variables associated would be "date1" "date2" etc. But then the variable "date1" would also need some descriptors such as "completed," "due," "urgent," etc. So an "item" object might look like:

jobApplication.completed = true
jobApplication.due = 12/12/2012
jobApplication.urgent = false

and then selecting the date 12/12/2012 would also lead to

date.taskArray = jobApplication is due,

Maybe this is not the right approach at all. The question that is really haunting me though is what i stated in the first line: How do I save all this data? I'd prefer NOT to put it all in a messy spreadsheet. I've done some applications before where I just skirted this issue by saving data to cells and it seemed pretty clumsy. I don't want to be clumsy with this much data.

thanks so much in advance for helping a new guy.

 

[diddi]

i would use random files to store this data. instead of a class, define a type and then you can save records randomly. its small and fast and theres no messy sheets.

 

[ultine]

ok. thanks. I will let you know how that turns out for me. I feel like this is a good next step for my programming "skills" (the word is used with tongue in cheek here).

 

[diddi]

so do you need a bit of a push start?

 

[ultine]

I hate to have you do for me what I can do for myself, but yeah, if you are in the mood, push away. thanks!

 

[diddi]

in a normal module: heres a sample file. it stores state, name of town and location (x and y)

define a Type

Public TLFile As String
Public TL As TownLoc

Type TownLoc
    State As Integer
    Name As String * 30
    XLoc As Single
    YLoc As Single
End Type

thus each record will be 2 + 30 + 4 + 4 bytes long


populate sheet 1 with a bit of sample data and run save sub
then run the load sub and it should repopulate the sheet

Sub SaveLocs()
    If TLFile = "" Then tlfile="C:\MySample.txt"
    Kill TLFile
    With Sheets("Sheet1")
        Open TLFile For Random As #3 Len = 40
        For row = 1 To .Cells(1, 8)
            TL.Name = .Cells(row, 1)
            TL.State = .Cells(row, 2)
            TL.YLoc = .Cells(row, 3)
            TL.XLoc = .Cells(row, 4)
            Put #3, row, TL
        Next row
        Close
    End With
End Sub

Sub LoadLocs()
    If TLFile = "" Then tlfile="C:\MySample.txt"
    With Sheets("Sheet1")
        Open TLFile For Random As #3 Len = 40
            For row = 1 To LOF(3) / TLRL
                Get #3, row, TL
                .Cells(row, 11) = TL.Name
                .Cells(row, 12) = TL.State
                .Cells(row, 13) = TL.YLoc
                .Cells(row, 14) = TL.XLoc
            Next row
        Close
    End With
End Sub

 

[diddi]

heres the created file being access from a form.

Private Sub ComboBox1_Change()
    ComboBox2.Clear
    prow = -1
    Open TLFile For Random As #3 Len = 40
        For row = 1 To LOF(3) / 40
            Get #3, row, TL
            If TL.State = ComboBox1.ListIndex + 1 Then
                prow = prow + 1
                ComboBox2.AddItem Trim(TL.Name)
                ComboBox2.List(prow, 1) = TL.XLoc
                ComboBox2.List(prow, 2) = TL.YLoc
            End If
        Next row
    Close
End Sub

this code searches the whole file and grabs the towns from the selected state chosen in combobox1, and loads the names and locations into combobox2

 

[ultine]

wow! so, after much research I have deciphered most everything in your code. This is great stuff. The only thing I'm still not quite getting is the:

            For Row = 1 To LOF(3) / TLRL

Can you please explain this line in "people-ese"? I think that will help a ton.

The problem I'm having now is this:
I copied the code as you wrote it but wasn't working (error on the Kill line)so I manually created the MySample.txt on the C:/ directory (is this necessary?). Then I got a run time error 75 on the code that I inserted above (For Row = 1...) so I put the MySample.txt in a folder called New Folder (i think my computer is set to not allow access to items directly on the C: drive) so i have this:

Sub SaveLocs()
    If TLFile = "" Then TLFile = "C:\New Folder\MySample.txt"

with everything else reading the same (New Folder inserted in load sub as well).

When I run the Save sub, I get no errors, but when I open the txt file, it is blank (I thought it would have my data?). then when I run the load sub I get a run-time error 6 overflow on:

For Row = 1 To LOF(3) / TLRL

Any help is much appreciated.

 

[ultine]

diddi, sorry... i need to edit that last post a little. I don't know what changed (maybe it is becuase I had the C:/ directory folder open?) but now the .txt file is saving the data. Now

For Row = 1 To LOF(3) / TLRL

gets a run-time "error 11 division by zero" on that line.

also, the txt file is now being populated with a bunch of crazy symbols and @ signs, I assume this is a good thing.

So to recap (sorry for this) I would like to know what a For...To statement does, what LOF(3) /TLRL means (i understand LOF in principle but not application).

Thanks for your time and talents, diddi.