Solved

VBA Error Question

Posted on 2011-03-17
7
295 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

832 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