loop based on a given set of numbers

[msyth2]

I have a set of numbers, say (1,3,5,6,8,11)

I need to use each of them one time in a sql where clause and print report seperately. I also need to use them as part of my exported file name.

How do I loop based on those numbers?

Thanks in advance.

 

[ChrisO]

Sub TestIt()
    Dim strSetOfNumbers As String
    Dim vntNumArray     As Variant
    Dim lngIndex        As Long

    strSetOfNumbers = "1,3,5,6,8,11"
    
    vntNumArray = Split(strSetOfNumbers, ",")
    
    For lngIndex = LBound(vntNumArray) To UBound(vntNumArray)
        Debug.Print "Doing my thing with " & vntNumArray(lngIndex)
    Next lngIndex

End Sub

 

[msyth2]

It works very well, thank you so much.

I am still wondering, is there a way using "For Each i In"?

 

[vbaInet]

If Chris doesn't mind me adapting his code

Sub TestIt()
    Dim strSetOfNumbers As String
    Dim vntNumArray      As Variant
    Dim itm                   As Variant

    strSetOfNumbers = "1,3,5,6,8,11"
    
    vntNumArray = Split(strSetOfNumbers, ",")
    
    For Each itm In vntNumArray
        DoCmd.OpenReport "ReportName",,,"ID = " & itm
        Reports("ReportName").Print
        DoEvents
        DoCmd.Close acReport, "ReportName", acSaveNo
        DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9, "SELECT * FROM TableName WHERE ID = " & itm & ";", "C:\Users\Administrator\Desktop\Client_" & itm & ".xls"
        DoEvents
    Loop

End Sub

From the above you can see the different ways each item in the array is used as an argument when opening the report, printing it then transferring it to excel.

 

[ChrisO]

Why would you prefer to use "For Each i In"?

Please remember I no nothing of your situation other than what you tell me.

Example: Where are the numbers stored? How are you accessing them?

That sort of thing…

PS. And where did all that extra information in vbaInet’s post come from???

Chris.

 

[vbaInet]

Here's where it came from Chris

I need to use each of them one time in a sql where clause and print report seperately. I also need to use them as part of my exported file name.

 

[ChrisO]

Oh well, looks like I made the incorrect assumption that those parts were already built.

 

[msyth2]

Thank you vbaInet too for your post. Your code is very close to what I actually was trying to get Except I had to use Outputto(report) to save the file as .rtf.

I was a little confused about LBound/UBound but I do see now for this case it's the same thing as "For Each In".

The number I had to include in the array was just some fixed index, about 10 of them. So I guess just spliting a string could be the easist for this.

Again ty all for the help.

 

[vbaInet]

I didn't actually see your post about wanting "For Each" before posting. In your case For Each would run faster but you won't notice the difference to be honest.

Glad we could help!

 

[ChrisO]

>>I didn't actually see your post about wanting "For Each" before posting. In your case For Each would run faster but you won't notice the difference to be honest.<<

Option Compare Database
Option Explicit


Public Declare Function timeGetTime Lib "Winmm.dll" () As Long


Sub TestIt()
    Dim strSetOfNumbers As String
    Dim vntNumArray      As Variant
    Dim itm                   As Variant

    strSetOfNumbers = "1,3,5,6,8,11"

    vntNumArray = Split(strSetOfNumbers, ",")

    For Each itm In vntNumArray
        DoCmd.OpenReport "ReportName", , , "ID = " & itm
        Reports("ReportName").Print
        DoEvents
        DoCmd.Close acReport, "ReportName", acSaveNo
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "SELECT * FROM TableName WHERE ID = " & itm & ";", "C:\Users\Administrator\Desktop\Client_" & itm & ".xls"
        DoEvents
    Loop  [color=green]' < Loop without Do[/color]

End Sub


Sub TestIt_1()
    Dim strSetOfNumbers As String
    Dim vntNumArray     As Variant
    Dim lngIndex        As Long
    Dim lngStart        As Long
    Dim lngLoopCount    As Long

    lngStart = timeGetTime()

    For lngLoopCount = 1 To 1000000
        strSetOfNumbers = "1,3,5,6,8,11"
        
        vntNumArray = Split(strSetOfNumbers, ",")
        
        For lngIndex = LBound(vntNumArray) To UBound(vntNumArray)
        Next lngIndex
    Next lngLoopCount
    
    MsgBox timeGetTime() - lngStart   [color=green]' < 3750[/color]

End Sub


Sub TestIt_2()
    Dim strSetOfNumbers As String
    Dim vntNumArray     As Variant
    Dim itm             As Variant
    Dim lngStart        As Long
    Dim lngLoopCount    As Long

    lngStart = timeGetTime()

    For lngLoopCount = 1 To 1000000
        strSetOfNumbers = "1,3,5,6,8,11"
        
        vntNumArray = Split(strSetOfNumbers, ",")
        
        For Each itm In vntNumArray
        Next itm
    Next lngLoopCount
    
    MsgBox timeGetTime() - lngStart   [color=green]' < 4470[/color]

End Sub

 

[vbaInet]

Interesting analysis Chris. I was of the opinion that For Each would be faster since it's not calling the L/UBound functions. It just seems most of these in-built functions are slower than when directed by the coder.

Of course Loop was a typo

 

[ChrisO]

>>Of course Loop was a typo<<
The obvious answer to that is don’t type code to site, just copy/paste what you have tested.
I do not know anybody, including myself, who can reliably type even small amounts of code without making errors.


My point is; why make incorrect claims about speed when your code doesn’t compile? If it doesn’t compile then it wasn’t tested and if it wasn’t tested it’s no more than a guess. When it comes to speed we can not guess; it must be tested.

If we make those claims incorrectly then we stand a good chance of starting yet another urban myth. We have enough of those already.

Now I will make a guess as to why the for next is faster than the for each. Remember it’s just a guess.

The for next is incrementing a Long each loop. A Long fits into a 32 bit register and if the compiler does not push that Long to the math-coprocessor it will be incremented very quickly.

Your code is incrementing a Variant. That would seem to imply a need to extract the Integer, Long or floating point data type from the Variant before processing. That extraction would take extra time which would not be required for a native machine data type such as a Long.

As for the LBound() and UBound() functions.
Again it needs to be tested but a for next loop should compile in such a way that only one call is made to those functions at the start. It should not need to re-evaluate the bounds of the array each loop simply because the bounds are not changing.

But all of that is just a guess and it means it needs to be tested.
We can guess all we like but we have to accept reality.

Chris.

 

[vbaInet]

>>Of course Loop was a typo<<
The obvious answer to that is don’t type code to site, just copy/paste what you have tested.
I do not know anybody, including myself, who can reliably type even small amounts of code without making errors.

My point is; why make incorrect claims about speed when your code doesn’t compile? If it doesn’t compile then it wasn’t tested and if it wasn’t tested it’s no more than a guess. When it comes to speed we can not guess; it must be tested.

If we make those claims incorrectly then we stand a good chance of starting yet another urban myth. We have enough of those already.

It's not a blog or a tech report I'm writing, just suggestions of possible solutions to what is being asked. I can't possibly test every code I put on here. This is why it's a forum and we have people (such as yourself) on here to help spot errors in code and the like There have been times I've written blocks of code without errors.

I doubt that it will cause an urban myth since this isn't a thread about For Each vs For Next, but talking about urban myth's, I know I remember seeing this somewhere and I've just found it:

http://www.cpearson.com/excel/optimize.htm

Now that is more likely to cause an urban myth. You would agree that it is a reliable source. Maybe I should have (still) said it was a guess then?

Your code is incrementing a Variant. That would seem to imply a need to extract the Integer, Long or floating point data type from the Variant before processing. That extraction would take extra time which would not be required for a native machine data type such as a Long.

I see what you mean and it may well be the case but that's an educated guess like you said.

I ran a couple of tests on these two loop structures and it turns out that the bigger the array the more considerably slower For Each becomes when compared to For Next. I was testing the internal loop rather than the external.

 

[ChrisO]

>>Now that is more likely to cause an urban myth . You would agree that it is a reliable source. Maybe I should have (still) said it was a guess then? <<

>> Now that is more likely to cause an urban myth<<
Yes it is, simply because people are more likely to believe people with a higher reputation.

>> You would agree that it is a reliable source.<<
Yes I would, but with the understanding that nothing, or no one, is 100% reliable.

>> Maybe I should have (still) said it was a guess then?<<
In my opinion yes, or at least state some degree of uncertainty.

You ran the risk of adding to the urban myth by not testing it for yourself under the specific conditions. You stated >>In your case For Each would run faster but you won't notice the difference to be honest.<< and the part about it being faster was both unqualified and unquantified. (Personally, I was supprised that there was as much as 25% difference.)

Be wary; people will see this and some will also look at things like post count. 8164 v 1738 (at the moment). Those who know won’t give a toss about post count. Those who don’t know may take it for granted that the higher post count prevails.

My advice to anyone is not to believe anything from anyone, including myself, and simple test it.

In the long run what we believe means nothing. We are trying to shove code down the throat of a computer and if that computer doesn’t like it then all the beliefs in the world will not get it to swallow.

Chris.