Weird VBA behavior

[600Burger]

Im using VBA with excel to do some routine totaling of large amounts of data that come in every month in more or less the same format...

While im writing the code im also testing it to make sure it runs smoothly, but, once i write a function and test it it goes REALLY slowly. One time I got kinda....bored...and ctrl+alt+deleted to end task. When i reloaded the SAME file (had saved just before testing and ending) it goes signifcantlly faster, like ~30x faster.

My question is, why does it do this and how do i make it do that WITHOUT having to EndTask and come back.

NOTE: saving and closing out the file doesn't work either.

Thanks,
-Andrew

[si_the_geek]

To know why it does that, we'll almost certainly need to see the code.

[MartinLiss]

Moved.

[600Burger]

K, what parts, i've made a few functions and stuff....heres one of the main functions....

Function totalPGEPump()

Dim colName As String
Dim pltName(1 To 17) As String
Dim pltType As String
Dim nDay As Long
Dim day As Long
Dim nHour As Long
Dim hour As Long
Dim i As Long
Dim pltRow As Long
Dim subRow As Long
Dim totalNameCell As String
Dim functionName As String
Dim colNameNum As Long
Dim hourlyTotalArray(30, 24) As Double
Dim totalPlants As Long
'pltType is the type of the plants that are being totaled
pltType = "Pump"
'functionName is the name that will appear as the blocks title in the worksheet
functionName = "PG&E Area " & pltType
'totalNameCell is the cell in which the name will appear
totalNameCell = "AP1893"
Cells((detExlRow(totalNameCell)), detExlCol(totalNameCell)) = functionName


'the column in which the names of the plants are listed.
'use the first cell in that column.
colName = "AQ1"
colNameNum = detExlCol(colName)
'Names of the plants in no order. Add more by increasing the 'dim' above _
'and also by increasing the while loop below.

pltName(1) = "Badger Hill"
pltName(2) = "Banks"
pltName(3) = "Barker Slough"
pltName(4) = "Bluestone"
pltName(5) = "Buena Vista"

pltName(6) = "Chrisman"
pltName(7) = "Cordelia (new)"
pltName(8) = "Del Valle"
pltName(9) = "Devils Den"
pltName(10) = "Dos Amigos"

pltName(11) = "Gianelli"
pltName(12) = "Hyatt"
pltName(13) = "Las Perillas"
pltName(14) = "Polonio"
pltName(15) = "South Bay"

pltName(16) = "Teerink"
pltName(17) = "Therm"

i = 1
subRow = detExlRow(totalNameCell)

totalPlants = 17

nDay = 30 - 1
day = 0
nHour = 23
hour = 0

While i <= totalPlants
pltRow = searchForPlantName(colName, pltName(i), pltType)

While day <= nDay
While hour <= nHour
hourlyTotalArray(day, hour) = hourlyTotalArray(day, hour) + Cells(pltRow + day, colNameNum + 1 + hour)
hour = hour + 1
Wend
day = day + 1
hour = 0
Wend
day = 0
i = i + 1
Wend

While day <= nDay
While hour <= nHour
Cells(subRow + day, colNameNum + 1 + hour) = hourlyTotalArray(day, hour)
hour = hour + 1
Wend
day = day + 1
hour = 0
Wend
End Function



Lemme know if you want some more, or certian functions from this peice.


Sorry bout the wrong forum thing...


EDIT: Lost all my formating, sorry.

[randem]

application deployment?????

[si_the_geek]

Hmm.. strange choice Marty! Moved to Office Development


EDIT: Lost all my formating, sorry.To keep formatting you need to use VBCode tags - either using the button in the post editor screen, or by putting them in manually, like this:
avbcode] 'code here a/vbcode]
(I have added them to your post above).


I'm afraid there is nothing obvious in your code that could make it slow down when being re-run, but then we can't see much of the code you are using (in the other functions, like detExlCol) or the sheet itself.

If you can post the file (put it in a zip file, and click on "post reply" then "manage attachments") then we can probably work out any issues.
(if you cant show us the data, it would help if you could give us "fake" data instead)

[malik641]

So what you are saying is that when you EDIT your existing code (or adding more to it) and then test it...it runs slower unless you force the program to close and re-open? Or is it when you run the code more than once?

[600Burger]

For some reason I couldn't find the VBCode button the first time around (I was looking for it), I see it now though.

The problem:

(It got weirder over the weekend, I swear this is true)

When I open the file for the first time the code runs (measured by how fast the individual cells fill in) at "1x". If I Ctrl+Alt+Delete while the code is running the re-open the same file and run it again it runs much faster ~"10x" at least.

Now, I have discovered while doing the Ctrl+Alt+Delete, that if I just hold Alt down while the code is running I get almost the same results without the impromptu ending for the program. Here is the bulk of my code.

Don't hesitate to criticize this code! I'm a newbie at programming so I’m sure it’s not as "optimized" as it can be. Also, there are probably many functions I have forgotten about that were either for test or are for some other parts of the program.