Solved

VBA Error Question

Posted on 2011-03-17
7
291 Views
Last Modified: 2012-05-11
Heyas,

Below is the following code:

Function ReadTextFileTime(sFilename As String, DateStart As String, DateEnd As String)

'Initialising File System Objects to load .dat file into for processing.
Set oFSO = CreateObject("Scripting.FileSystemObject")
Dim oFS
Dim iLineNo As Integer 'Line Counter
Dim iRow As Integer 'Row Counter
Dim stext As String
iLineNo = 0
iRow = 0
Set oFS = oFSO.OpenTextFile(sFilename)

Do Until oFS.AtEndOfStream

stext = oFS.ReadLine

iLineNo = iLineNo + 1 'Incrementing Line Count as file is read.

'Omit data from processing until line 5 then start loop
If iLineNo > 4 Then
   
    If Trim(stext) = "" Then Exit Function 'If no text can be found exit or EOF (End of File) has been reached
    Arrmain = Split(stext, ",") 'Splitting String via , delimiter
    tempTimestamp = Arrmain(0)
    Timestamp = Mid((Left(tempTimestamp, Len(tempTimestamp) - 1)), 2) 'Removing the " from the Timestamp
    If DateValue("Timestamp") > DateValue("DateStart") Then
    Else: GoTo EndofLoop:
    If DateValue("Timestamp") < DateValue("DateEnd") Then

       
    blah blah bunch of array reads
    Else GOTO EndofLoop:

EndofLoop:
   
 End If 'Line No testing
   
Loop

End Function


Now when I run this code I get an error message complie error Loop Without Do and I don't understand why as without these section

 If DateValue("Timestamp") > DateValue("DateStart") Then
    Else: GoTo EndofLoop:
    If DateValue("Timestamp") < DateValue("DateEnd") Then

The code works fine.

Any help would be much appreciated.
0
Comment
Question by:Zack
7 Comments
 
LVL 6

Expert Comment

by:rbgCODE
ID: 35162383
You can't just point to an end of loop function within a loop, you would need to kill the do like

Exit Do


Do [{While | Until} condition]
[statements]
[Exit Do]
[statements]
Loop

Or, you can use this syntax:
Do
[statements]
[Exit Do]
[statements]
Loop [{While | Until} condition]

Open in new window

0
 

Expert Comment

by:MillionDollarQuestion
ID: 35162390
Hi there.

Try reformatting your if statements to look like this:

    If DateValue("Timestamp") > DateValue("DateStart") Then
    Else
        GoTo EndofLoop:
    End If
    If DateValue("Timestamp") < DateValue("DateEnd") Then
    Else
        GoTo EndofLoop:
    End If

Open in new window


Your code compiled fine for me with that change.

Cheers,
Josh
0
 
LVL 11

Expert Comment

by:SeanStrickland
ID: 35162458
I think it's this Else... Try changing this part:

If DateValue("Timestamp") > DateValue("DateStart") Then
    Else: GoTo EndofLoop:

Open in new window


To this:
If DateValue("Timestamp") <= DateValue("DateStart") Then GoTo EndofLoop

Open in new window


You would generally use a colon when concatenating multiple lines of code together... example:
numDogs=4: numCats=2: numPets=numDogs+numCats

That being said, the colons in your Else: GoTo EndofLoop: line don't make much sense to me.  Also, you don't have any true statement associated to your IF statement.  I would flip the logical expression from greater than to "less than or equal to", and put "GoTo EndOfLoop" as my true statement on the same line like I did above.  That should solve your problem and it makes more sense to anyone that comes behind you to code or maintain.

Hope that helps
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Zack
ID: 35162460
Heyas,

I am not trying to end the loop prematurely I am trying to skip lines that have dates don't meet the requirements of the following if statements.

 If DateValue("Timestamp") > DateValue("DateStart") Then
    Else
        GoTo EndofLoop:
    If DateValue("Timestamp") < DateValue("DateEnd") Then
  blah blah array read
 
    Else
        GoTo EndofLoop:
       
 End If 'Line No testing
   
EndofLoop:
Loop  ' (Comment: Then it should move onto the next line)

End Function

Still not working for me.

Thank you.
0
 
LVL 11

Accepted Solution

by:
SeanStrickland earned 125 total points
ID: 35162537
I understand what you're trying to accomplish, I do it pretty regularly when scanning through text files for relevant data.

Actually, I doubt the code I posted for you will work.  The code below should, however.  You've left open your IF statements in the middle of your code.
'Initialising File System Objects to load .dat file into for processing.
Set oFSO = CreateObject("Scripting.FileSystemObject")
Dim oFS
Dim iLineNo As Integer 'Line Counter
Dim iRow As Integer 'Row Counter
Dim stext As String
iLineNo = 0
iRow = 0
Set oFS = oFSO.OpenTextFile(sFilename)

Do Until oFS.AtEndOfStream

stext = oFS.ReadLine

iLineNo = iLineNo + 1 'Incrementing Line Count as file is read.

'Omit data from processing until line 5 then start loop
If iLineNo > 4 Then
    
    If Trim(stext) = "" Then Exit Function 'If no text can be found exit or EOF (End of File) has been reached
    Arrmain = Split(stext, ",") 'Splitting String via , delimiter
    tempTimestamp = Arrmain(0)
    Timestamp = Mid((Left(tempTimestamp, Len(tempTimestamp) - 1)), 2) 'Removing the " from the Timestamp
    If DateValue("Timestamp") > DateValue("DateStart") Then
         'Do nothing
    Else
          GoTo EndofLoop
    End if 'added this
    If DateValue("Timestamp") < DateValue("DateEnd") Then

       
    blah blah bunch of array reads
    Else 
           GOTO EndofLoop
    End If 'added this, too

EndofLoop:
    
 End If 'Line No testing
   
Loop

End Function

Open in new window

0
 

Author Closing Comment

by:Zack
ID: 35162686
Thanks man that worked
0
 
LVL 11

Expert Comment

by:SeanStrickland
ID: 35164576
No problem -- thanks for the grade.

Glad it worked
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now