[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VBA Error Question

Posted on 2011-03-17
7
Medium Priority
?
309 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 500 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
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…

834 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