Solved

VBA Error Question

Posted on 2011-03-17
7
292 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

863 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

24 Experts available now in Live!

Get 1:1 Help Now