Solved

VBA Error Question

Posted on 2011-03-17
7
296 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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
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…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

789 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