Better way to pull data in a loop [Excel VB]

[Solarion]

Better way to pull data in a loop

I'm working on something and either it's fine and I'm being picky, or it's just not right. While building a spreadsheet I need to do a check inside the loop, and pull data from an unrelated table.

Is there another and more effective way to do this instead of calling this function, is it that perfectly ok?

VB

           If dt IsNot Nothing And dt.Rows.Count > 0 Then

                For Each dr In dt.Rows
                    rowIndex = rowIndex + 1
                    colIndex = 0
                    For Each dc In dt.Columns

                        colIndex = colIndex + 1

                        wSheet.Cells(rowIndex + 1, 1) = dr("Title")
                        wSheet.Cells(rowIndex + 1, 2) = dr("Description")


                        If dr("Or") = "Y" Then
                            wSheet.Cells(rowIndex + 1, 3) = [B]returnVal("Select progDesc FROM Programs WHERE (Code = " & dr("ProgCode") & ")")[/B]
                        End If


                    Next
                Next

            End If

 

[[)roi(]]

Have you considered dumping the contents of the Programs table in another sheet?

 

[Solarion]

Have you considered dumping the contents of the Programs table in another sheet?

Hm that is something worth pondering on.

 

[[)roi(]]

Hm that is something worth pondering on.

Here's another approach:
- If the programs table is large, and there's a likelihood of duplicate queries (i.e. same question = answer)

Caching
Create a hash map to cache your questions & answers, i.e. you'd only run a new SQL query if question/answer was not found in the cache.

 

[me_]

If your tables are both in the same database, you could perform an inner join and include all results from your main table and progDesc from the programs table.

Also when you run Excel macros, always remember to switch off screen updating (using Application.ScreenUpdating) before and switch it back on afterwards (when not troubleshooting). This makes it run significantly faster.

 

[Solarion]

Here's another approach:
- If the programs table is large, and there's a likelihood of duplicate queries (i.e. same question = answer)

Caching
Create a hash map to cache your questions & answers, i.e. you'd only run a new SQL query if question/answer was not found in the cache.

Also will try this option.

If your tables are both in the same database, you could perform an inner join and include all results from your main table and progDesc from the programs table.

Also when you run Excel macros, always remember to switch off screen updating (using Application.ScreenUpdating) before and switch it back on afterwards (when not troubleshooting). This makes it run significantly faster.

Yup know exactly what you mean with the joins. I will give it a bash and post the results thanks.