TheSwamp

Code Red => VB(A) => Topic started by: Amsterdammed on July 17, 2005, 03:48:25 PM

Title: Equivalent to a dotted pair in VBA
Post by: Amsterdammed on July 17, 2005, 03:48:25 PM

It is funny how you can miss thinks like a dotted pair in VBA.

The Job I got to do is simple. I have a input file with information, names and hours, they belong together. I don’t know how many pairs there are, that differs.

So I want to display them in to listboxes, the user selects the name, the hours belonging to the name appear then in a text field and can be edited by the user. This is necessary because the user can choose the name several times for input, and I want the hours he fills in counted of the original amount.

I know a little about  array, but I think the are not Public?

In lisp I would do this with a dotted pair, but how to do in VBA ?

Thanks in Advance,

Bernd

Title: Equivalent to a dotted pair in VBA
Post by: Keith™ on July 18, 2005, 09:18:00 AM

An array to keep objects together and easily manageble would eb the way to go ...

Try this:

Code: [Select]

Public Function MyFunction() As Variant
'declare variables
Dim Data As String
Dim DataFile As Integer
Dim MyArray() As Variant
Dim N As Integer
Dim SplitData As Variant

'open ourdata file
Open FileName For Input As #DataFile
'initialize our counter (not really needed but I always do)
N = 0
'loop through the file until the End of File
While Not EoF(#DataFile)
 'resize our array to include the data we are reading
 ReDim Preserve MyArray(N,1)
'read the data into our variable
 Line Input #DataFile, Data
'split it into 2 parts (I used a . because you asked about a dotted pair)
 SplitData = Split(Data,".")
'take the split pair array and put it into
'our multi dimensional array
 MyArray(N,0) = SplitData(0)
 MyArray(N,1) = SplitData(1)
'increment our counter
 N = N + 1
'loop
Wend
'close file
Close #DataFile
'return the array to the calling function
MyFunction = MyArray
End Function


Now the disclaimer .. this was written on the fly, so it may contain errors and/or omissions ... you should check it before trying to use it.

Title: Equivalent to a dotted pair in VBA
Post by: Amsterdammed on July 18, 2005, 01:24:23 PM

Keith,

Can an array be public ?

Bernd. :?:

Title: Equivalent to a dotted pair in VBA
Post by: Keith™ on July 18, 2005, 03:05:52 PM

yes

at the top of your module:

Code: [Select]

Public MyArray() As Variant

Title: Equivalent to a dotted pair in VBA
Post by: Amsterdammed on July 19, 2005, 07:05:57 AM

Keith,
If i place the Public MyArray i get a MsgBox with the contens

Quote

Constants, fixed-length strings, arrays, user-defined types and Declare statements not allowed as members of object modules

Any Idea how to sail around this?

Thanks

Title: Equivalent to a dotted pair in VBA
Post by: Keith™ on July 19, 2005, 08:22:35 AM

Move it out of the form code. you should put the declare in a regular module.

Title: Equivalent to a dotted pair in VBA
Post by: Amsterdammed on July 19, 2005, 09:33:40 AM

:D

It is always so easy, if you know it.

Thanks a lot , early Bird

Bernd