[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.vb.general.discussion

VBA end if without block if and loop without do errors on code

wkeanebaker

2/23/2011 2:39:00 PM

'Loop through all rows of the file
wksOut.Cells(3, 1) = wks.Cells(3, 1)
i = 7
Do Until Left(wks.Cells(i, 1).Value, 14).Value = "Total for fund"

If wks.Cells(i, 2).Value <> "" Then

For y = 1 To 32
wksOut.Cells(i, y).Value = wks.Cells(i, y).Value

End If

i = i + 1

Loop


Please help with where I went wrong here, Thank you
25 Answers

intergalacticexpandingpanda

3/22/2008 2:58:00 PM

0

On Mar 21, 5:44 pm, "Tralfamador" <Tr...@freeserve.net> wrote:

> More people should shun google's awful html front end and al least try
> outlook express. Loads better, no posting limits and a basic killfile that
> works ok for me anyway.

Yes, the newsgroup faq has info on a free mail server IIRC. To be
honest, while I am rather old school, I'm pretty pleased with the web
interface. It does the job for the most part, though threading could
be better. The post scoring system is rather nifty as well.

No Way

2/23/2011 3:30:00 PM

0


"wkeanebaker" <wkeanebaker@gmail.com> wrote in message
news:d2fcca15-3b17-43f6-8654-082d200a61b5@q2g2000pre.googlegroups.com...
> 'Loop through all rows of the file
> wksOut.Cells(3, 1) = wks.Cells(3, 1)
> i = 7
> Do Until Left(wks.Cells(i, 1).Value, 14).Value = "Total for fund"
>
> If wks.Cells(i, 2).Value <> "" Then
>
> For y = 1 To 32
> wksOut.Cells(i, y).Value = wks.Cells(i, y).Value
>
> End If
>
> i = i + 1
>
> Loop
>
>
> Please help with where I went wrong here, Thank you

Should there not be a "Next Y" on the line after wksOut.Cells and before End
If ?

So it would be -

'Loop through all rows of the file
wksOut.Cells(3, 1) = wks.Cells(3, 1)
i = 7
Do Until Left(wks.Cells(i, 1).Value, 14).Value = "Total for fund"

If wks.Cells(i, 2).Value <> "" Then

For y = 1 To 32
wksOut.Cells(i, y).Value = wks.Cells(i, y).Value
Next y

End If

i = i + 1

Loop



Neil..

Jeff Johnson [MVP: VB]

2/23/2011 6:56:00 PM

0

"No Way" <tom@invalid.co.uk> wrote in message
news:ik396b$8fa$1@speranza.aioe.org...

> Should there not be a "Next Y" on the line after wksOut.Cells and before
> End
> If ?

Eww, you really do "Next <variable name>"? I guess it's a preference thing,
but I just prefer the plain Next.

For y = 1 To 10
For x = 1 To 20
' blah blah blah
Next
Next

It's not like you can force an outer loop to iterate by specifying its
variable "early," so I find naming the variable to be wordy. (Of course,
some will argue that it makes the code self-documenting. I don't disagree,
but I still don't find it in any way necessary.)


MikeD

2/23/2011 8:05:00 PM

0



"Jeff Johnson" <i.get@enough.spam> wrote in message
news:ik3l7g$keb$1@news.eternal-september.org...

> Eww, you really do "Next <variable name>"? I guess it's a preference
> thing, but I just prefer the plain Next.
>
> For y = 1 To 10
> For x = 1 To 20
> ' blah blah blah
> Next
> Next

I seem to recall that VB better optimized execution if you omitted the
variable. But that might have only been in VB's early days, or perhaps it
was in VB5 when native code compilation was first introduced (kinda makes
sense that it could be). I don't recall the exact details. I just remember
that at some time, it was the case. With today's computers being so much
faster than 10-15 years ago, it's probably essentially a non-issue now even
if it was never "fixed".

>
> (Of course, some will argue that it makes the code self-documenting. I
> don't disagree, but I still don't find it in any way necessary.)

I dunno. I kinda disagree. IMO, proper indentation does the
self-documenting, and makes the code even easier to read. Consider this
example:

For y =1 To 10
For x = 1 To 10
'blah blah blah
Next x
Next y

Even with the variable names on the 2 Next statements, that's just ugly
code. It's amazing to me how many people write loop blocks (and other code
blocks like Select Case's and even If Then's) without indentation. And WITH
proper indentation, including the variable names in the 2 Next statements is
just superfluous to make the code clear and concise.

If nobody but yourself is ever going to see your code and however you format
your code is legible to you, I guess it's all irrelevant.

--
Mike

(nobody)

2/23/2011 8:19:00 PM

0

"MikeD" <nobody@nowhere.edu> wrote in message
news:ik3p99$d58$1@news.eternal-september.org...
> It's amazing to me how many people write loop blocks (and other code
> blocks like Select Case's and even If Then's) without indentation.

These people are geniuses. They are so smart that the lack of indentation is
no obstacle to their brain capacity to auto analyze code blocks on the fly.
:-)

Example(You need to download it to see it, every line starts in column 1):

Supersonic Unlimited-bits RSA Encryption-Decryption:
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=57824&am...

Example function in case you don't want to download it:

Private Function xaddp(ByRef x() As String, ByVal nx As Double) As String
ReDim lx(1 To nx) As Double
Dim hs As String
Dim ins1 As String
Dim cr As Double
Dim out As String
Dim j As Double
Dim i As Double
Dim jx As String
Dim adept As Integer
Dim keep As Integer
Dim lbw As Double

If UBound(x) < nx Then nx = UBound(x)
lbw = 0
For i = 1 To nx
lx(i) = Len(x(i))
If lx(i) > lbw Then lbw = lx(i)
Next
adept = 15 - Len(CStr(nx - 1))
If lbw <= adept Then
j = 0
For i = 1 To nx
j = j + CDbl(x(i))
Next
xaddp = CStr(j)
Exit Function
End If
For i = 1 To nx
x(i) = String(lbw - lx(i), "0") & x(i)
Next
ins1 = ""
cr = 0
While (lbw > 0)
j = 0
keep = IIf(lbw >= adept, adept, lbw)
For i = 1 To nx
j = j + CDbl(Mid(x(i), lbw - keep + 1, keep))
Next
j = j + cr
jx = CStr(j)
j = Len(jx)
If j > keep Then
cr = CDbl(Left(jx, j - keep))
ins1 = Mid(jx, j - keep + 1, keep) & ins1
Else
cr = 0
ins1 = String(keep - j, "0") & Left(jx, keep) & ins1
End If
lbw = lbw - keep
Wend
If cr > 0 Then ins1 = CStr(cr) & ins1
xaddp = ins1
End Function

Helmut_Meukel

2/23/2011 9:06:00 PM

0

Jeff Johnson legte dieses als Verknüpfung auf seinen Bildschirm :
> "No Way" <tom@invalid.co.uk> wrote in message
> news:ik396b$8fa$1@speranza.aioe.org...
>
>> Should there not be a "Next Y" on the line after wksOut.Cells and before
>> End
>> If ?
>
> Eww, you really do "Next <variable name>"? I guess it's a preference thing,
> but I just prefer the plain Next.
>
> For y = 1 To 10
> For x = 1 To 20
> ' blah blah blah
> Next
> Next
>
> It's not like you can force an outer loop to iterate by specifying its
> variable "early," so I find naming the variable to be wordy. (Of course, some
> will argue that it makes the code self-documenting. I don't disagree, but I
> still don't find it in any way necessary.)

Jeff, once upon a time ...

IIRC, there were slight performance differences between "Next" and
"Next i". But this depended on the basic version and changed.

I once (in 2002) wrote a test programm to check if there is any
difference between these 4 versions:
For i = 1 To 255: ... :Next
For i = 1 To 255: ... :Next i
For i = 1 To 255 Step 1: ... :Next
For i = 1 To 255 Step 1: ... :Next i

The test itself is with 3 nested loops with 255 iterations each.
The loop counters (i, j, k) dimmed as Byte, Integer, Long, Single and
Double; 20 runs total.

Guess what, no difference at all when the test app is compiled.
Obviously the internal loop counter is not affected by the data type
of the variable when the starting value is an integer and the step is 1.
I had the app compiled 3 times: native code, native code favoring
Pentium Pro, and P-Code.
I just run the tests with those old compiled exes on my current PC
(AMD Athlon, Win 7): no difference between both native code compilations,
the P-Code is slightly (about 10%) slower.

But I know, back in the old days, with VB3, VB-DOS, Basic PDS 7.1 and
QuickBasic 4.5 there really were performance differences.

Helmut.

MikeD

2/23/2011 9:35:00 PM

0



"Nobody" <nobody@nobody.com> wrote in message
news:ik3q49$p23$1@speranza.aioe.org...
> "MikeD" <nobody@nowhere.edu> wrote in message
> news:ik3p99$d58$1@news.eternal-september.org...
>> It's amazing to me how many people write loop blocks (and other code
>> blocks like Select Case's and even If Then's) without indentation.
>
> These people are geniuses. They are so smart that the lack of indentation
> is no obstacle to their brain capacity to auto analyze code blocks on the
> fly. :-)

Then I'm glad that I'm not a genius. <g>

>
> Example function in case you don't want to download it:
>
> Private Function xaddp(ByRef x() As String, ByVal nx As Double) As String
> ReDim lx(1 To nx) As Double
> Dim hs As String
> Dim ins1 As String
> Dim cr As Double
> Dim out As String
> Dim j As Double
> Dim i As Double
> Dim jx As String
> Dim adept As Integer
> Dim keep As Integer
> Dim lbw As Double
>
> If UBound(x) < nx Then nx = UBound(x)
> lbw = 0
> For i = 1 To nx
> lx(i) = Len(x(i))
> If lx(i) > lbw Then lbw = lx(i)
> Next
> adept = 15 - Len(CStr(nx - 1))
> If lbw <= adept Then
> j = 0
> For i = 1 To nx
> j = j + CDbl(x(i))
> Next
> xaddp = CStr(j)
> Exit Function
> End If
> For i = 1 To nx
> x(i) = String(lbw - lx(i), "0") & x(i)
> Next
> ins1 = ""
> cr = 0
> While (lbw > 0)
> j = 0
> keep = IIf(lbw >= adept, adept, lbw)
> For i = 1 To nx
> j = j + CDbl(Mid(x(i), lbw - keep + 1, keep))
> Next
> j = j + cr
> jx = CStr(j)
> j = Len(jx)
> If j > keep Then
> cr = CDbl(Left(jx, j - keep))
> ins1 = Mid(jx, j - keep + 1, keep) & ins1
> Else
> cr = 0
> ins1 = String(keep - j, "0") & Left(jx, keep) & ins1
> End If
> lbw = lbw - keep
> Wend
> If cr > 0 Then ins1 = CStr(cr) & ins1
> xaddp = ins1
> End Function


That's just nasty. Lack of indentation is the least of the problems with
that code.

--
Mike

MikeD

2/23/2011 9:50:00 PM

0



"Helmut_Meukel" <Helmut_Meukel@bn-hof.invalid> wrote in message
news:ik3sqr$37m$1@news.eternal-september.org...
>
> IIRC, there were slight performance differences between "Next" and
> "Next i". But this depended on the basic version and changed.

I'm with you on that. I just don't remember details.

>
> I once (in 2002) wrote a test programm to check if there is any
> difference between these 4 versions:
> For i = 1 To 255: ... :Next
> For i = 1 To 255: ... :Next i
> For i = 1 To 255 Step 1: ... :Next
> For i = 1 To 255 Step 1: ... :Next i
>
> The test itself is with 3 nested loops with 255 iterations each.

A mere 255 iterations is not enough to get accurate results. I'd say bump
that up to 10,000 (maybe much larger than that even). Sure, it's going to
take a little longer, but what good are results if the sample being
tested/benchmarked (in this case, the number of iterations) is not
sufficiently large?

> Guess what, no difference at all when the test app is compiled.

Not surprising. 255 iterations is just not enough.

> Obviously the internal loop counter is not affected by the data type
> of the variable when the starting value is an integer and the step is 1.

No, not obviously. I propose that any and all conclusions you derived are
misleading because the number of iterations you tested wasn't sufficiently
large.

--
Mike

MikeD

2/23/2011 9:54:00 PM

0



"MikeD" <nobody@nowhere.edu> wrote in message
news:ik3vf4$3lm$1@news.eternal-september.org...
>
>
> "Helmut_Meukel" <Helmut_Meukel@bn-hof.invalid> wrote in message
> news:ik3sqr$37m$1@news.eternal-september.org...
>>
>> IIRC, there were slight performance differences between "Next" and
>> "Next i". But this depended on the basic version and changed.
>
> I'm with you on that. I just don't remember details.
>
>>
>> I once (in 2002) wrote a test programm to check if there is any
>> difference between these 4 versions:
>> For i = 1 To 255: ... :Next
>> For i = 1 To 255: ... :Next i
>> For i = 1 To 255 Step 1: ... :Next
>> For i = 1 To 255 Step 1: ... :Next i
>>
>> The test itself is with 3 nested loops with 255 iterations each.
>
> A mere 255 iterations is not enough to get accurate results. I'd say bump
> that up to 10,000 (maybe much larger than that even). Sure, it's going to
> take a little longer, but what good are results if the sample being
> tested/benchmarked (in this case, the number of iterations) is not
> sufficiently large?
>
>> Guess what, no difference at all when the test app is compiled.
>
> Not surprising. 255 iterations is just not enough.
>
>> Obviously the internal loop counter is not affected by the data type
>> of the variable when the starting value is an integer and the step is 1.
>
> No, not obviously. I propose that any and all conclusions you derived are
> misleading because the number of iterations you tested wasn't sufficiently
> large.
>


How's that for refuting the same thing 3 different times? <g>

Henning

2/23/2011 10:05:00 PM

0


"Helmut_Meukel" <Helmut_Meukel@bn-hof.invalid> skrev i meddelandet
news:ik3sqr$37m$1@news.eternal-september.org...
> Jeff Johnson legte dieses als Verknüpfung auf seinen Bildschirm :
>> "No Way" <tom@invalid.co.uk> wrote in message
>> news:ik396b$8fa$1@speranza.aioe.org...
>>
>>> Should there not be a "Next Y" on the line after wksOut.Cells and before
>>> End
>>> If ?
>>
>> Eww, you really do "Next <variable name>"? I guess it's a preference
>> thing,
>> but I just prefer the plain Next.
>>
>> For y = 1 To 10
>> For x = 1 To 20
>> ' blah blah blah
>> Next
>> Next
>>
>> It's not like you can force an outer loop to iterate by specifying its
>> variable "early," so I find naming the variable to be wordy. (Of course,
>> some
>> will argue that it makes the code self-documenting. I don't disagree, but
>> I
>> still don't find it in any way necessary.)
>
> Jeff, once upon a time ...
>
> IIRC, there were slight performance differences between "Next" and
> "Next i". But this depended on the basic version and changed.
>
> I once (in 2002) wrote a test programm to check if there is any
> difference between these 4 versions:
> For i = 1 To 255: ... :Next
> For i = 1 To 255: ... :Next i
> For i = 1 To 255 Step 1: ... :Next
> For i = 1 To 255 Step 1: ... :Next i
>
> The test itself is with 3 nested loops with 255 iterations each.
> The loop counters (i, j, k) dimmed as Byte, Integer, Long, Single and
> Double; 20 runs total.
>
> Guess what, no difference at all when the test app is compiled.
> Obviously the internal loop counter is not affected by the data type
> of the variable when the starting value is an integer and the step is 1.
> I had the app compiled 3 times: native code, native code favoring
> Pentium Pro, and P-Code.
> I just run the tests with those old compiled exes on my current PC
> (AMD Athlon, Win 7): no difference between both native code compilations,
> the P-Code is slightly (about 10%) slower.
>
> But I know, back in the old days, with VB3, VB-DOS, Basic PDS 7.1 and
> QuickBasic 4.5 there really were performance differences.
>
> Helmut.
>
>

The For/Next loop compiled is just:

at the start of the loop
jmp doit
loopit:
increment loop counter
doit:
.....
code inside loop
.....
cmp loopcounter, endvalue (compare loopcounter w endvalue)
ble loopit (jump if less or equal)

/Henning