Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gmmastros on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Setting Array to Nothing

Status
Not open for further replies.
PhilBarnes

PhilBarnes

Technical User
Jan 18, 2005
3
0
0
GB
Here is my problem:
Code: 
    ReDim panelists(300)
        
        y = 1
        
        Do While Not EOF(1)    ' Loop until end of file.
            Line Input #1, panelists(y)    ' Add Mnemonic to array
            y = y + 1
        Loop
    
    If IsArray(panelists) Then
        ReDim Preserve panelists(y - 1)
    Else
        Set panelists = Nothing
    End If

the above is a snippet of my code and shows what im trying to do. It is incomplete I realise panelists is an array as soon as I Redim although what im looking to do is if there are no panellists then set the current Redim'd array of 300 empty rows to nothing.

Is there any way of doing this? "Set panelists = Nothing" seems to be incorrect.. Thanks in advance for any assistance
 
Sort by date Sort by votes
don't do a lot of this kinda thing but I remember seeing something like

If BOF = EOF then
'no records in recordset
else
'Records present
end if

May need to be if BOF = EOF-1 or similar logic if the above doesn't work...

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
Hi PhilBarnes,

There isn't a BOF for Input Files but you can check the file length if you want, something like ..

Code: 
[blue]If FileLen("[i]YourPath\YourFile[/i].txt") = 0 Then
    [green]' File has no content[/green]
Else
    Open "[i]YourPath\YourFile[/i].txt" for input as #1
    [green]' etc., etc.[/green]
End If[/blue]

However you discover the fact, you can't easily use the state of your array variable as a flag (you'ld need to do some error trapping) and you can't set an array to Nothing so I'd be inclined to use a separate boolean myself.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.
 
Upvote 0 Downvote
thanks for the info xlbo. When you mentioned EOF/ BOF it got me thinking and below solved it:

Code: 
    If Not EOF(1) Then
        y = 1
        ReDim panelists(300)
            Do While Not EOF(1)    ' Loop until end of file.
                Line Input #1, panelists(y)    ' Add Mnemonic to array
                y = y + 1
            Loop
        ReDim Preserve panelists(y - 1)
    Else
        '/ do nothing
    End If

it seems that EOF(1) = False when the file has values..
 
Upvote 0 Downvote
Thanks for the additional option Tony. Seems that if I use EOF as a flag I can then declare my array if the file has values and leave it empty if not.
 
Upvote 0 Downvote
Similar stuff;

Dim panelists(),y%

Do While Not EOF(1) ' Loop until end of file.
y = y + 1
Redim preserve panelists(y)
Line Input #1, panelists(y)

Loop

Regards Hugh
 
Upvote 0 Downvote
Cheers Tony - still not got my head around input files yet - getting there on recordsets tho !

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
LOL Geoff,

Difference is you can move both ways in a recordset. Input files are straightforward sequential devices - you're at 'BOF' when you open it, you're not once you've read a record.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.
 
Upvote 0 Downvote
Cheers Tony - most of my work recently has been to do with ODBC queries into excel or interacting with Business Objects - we don't get many text file outputs so I don't get many textfile inputs to play with !!

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

K
  • Question
How to resolve define worksheets array
Replies
1
Views
143
combo
combo
remeng
  • Locked
  • Question
Getting results from a dynamic dimension array
Replies
2
Views
104
Andrzejek
Andrzejek
rf92d
  • Locked
  • Question
Hi referring to thread707-107529
Replies
0
Views
72
rf92d
rf92d
rf92d
  • Locked
  • Question
hi referring to thread707-103495
Replies
9
Views
115
Andrzejek
Andrzejek
jtm2020hyo
  • Locked
  • Question
VBA script to split 0D, 1D, and 2D arrays 1
Replies
10
Views
120
jtm2020hyo
jtm2020hyo

Part and Inventory Search

Sponsor

Back
Top