Visual Basic Macro in Excel

L

lottodude

Member
Can anybody who is good @ VBA for writing macros in excel help me out??

I wrote a macro myself, it basically does the following:

1) replace numbers
2) autofill the data to a range
3) compare data

then loop restart from 1) again..

The macro loops approx. 1500 times each time I run it...
My question is, why does it always start out soooo fast, but slows GREATLY after around 200??? Even if I interrupt the macro and restart the macro again, it's still slow..

In order to let the macro run fast again, I have to like close the excel, re-open it, re-run the macro again to get the fasttest speed.. however, it starts to slow down again after around 200... any idea why??

I'm pretty sure it's not because of the windows memory resource, because if I open another excel window (which will take more windows resource) and run the macro in another window at the same time, it is still very fast (but will slow down later on too)..

is it because that excel saves some temp info as the macro is run??? if so, can i clear it to make it faster???

I added a line of
"set wSheet = nothing", but didn't seem to help...

any idea? experts??
 
G

GillesD

Member
Slowing macros

It is hard to estimate possible cause without seeing the code but this may help:

1 - Make sure you have only one Excel file open; I have seen some macros go very slowly when other files are opened especially if some sheets have identical names in the files.

2 - Does the number of comparisons increases as the number of loops increases? If you use IF or SELECT statement for comparing data, as you make more and more comparisons, it will definitely slow things down.

3 - A constant updating of the screen can also slow down the operation. Use APPLICATION.SCREENUPDATING = FALSE at the beginning. You will not see the changes on the screen but it will definitely help speed in some cases.
 
Last edited:
R

retxx

Member
gilles

hi gilles, a question for you. you are adept at excel which i have. Can you do a chart for keno in excel to let me enter past results from a 80 # game that produces 20# that would help me in selecting either 4-5-6-7-8#to play.Thanks
 
G

GillesD

Member
To retxx

Sure it is possible to do quite a few things but all depends on what criteria you want to base your selection.

If you want to look at numbers that have come out the most often or the least often in a certain number of draws. I would suggest you look at the FREQUENCY function. It will give you how often each number came out in the range you select.

From that, a graph can easily be made showing each number (#1-80) on the X-axis and on the Y-axis, the number of times each came out . You can then pick your number visually.
 
R

retxx

Member
gilles

what I would like it to show me is the best set of 3,4,5,6,7 that hit together the most times as I enter current past draws. Can you do a chart(excel) to do this or is it too much to do. Thanks gilles
 
L

lottodude

Member
Re: Slowing macros

GillesD said:
It is hard to estimate possible cause without seeing the code but this may help:

1 - Make sure you have only one Excel file open; I have seen some macros go very slowly when other files are opened especially if some sheets have identical names in the files.

2 - Does the number of comparisons increases as the number of loops increases? If you use IF or SELECT statement for comparing data, as you make more and more comparisons, it will definitely slow things down.

3 - A constant updating of the screen can also slow down the operation. Use APPLICATION.SCREENUPDATING = FALSE at the beginning. You will not see the changes on the screen but it will definitely help speed in some cases.
Click to expand...

Gilles, thanx for the reply
1 - I only have a file open @ a time.. so i don't think this is a problem for me
2 - The comparing data is fixed.... it's not increasing as the number of loops increases.
3 - I already disable the screenupdate thing...

I think it might be easier for you to look @ the macro... The macro is nothing fancy, just replace, autofill, compare, THEN REPEAT AGAIN, replace, autofil, compare...keep looping until 1500
I'm pretty sure it's the autofill the slows down the whole macro after the number of loops reaches 200...
 
L

lottodude

Member
MACRO

Sub replace_autofill()
Dim wshSheet2 As Worksheet
Dim intInitialValue As Integer
Dim intFinalValue As Integer
Dim i As Integer 'counter
Dim int6P1Row As Integer
Dim btest As Boolean
Set wshSheet2 = ThisWorkbook.Sheets("Sheet2")

intInitialValue = Sheet2.Range("K10") ' it's "3" in K10
intFinalValue = Sheet2.Range("L10") ' It's "1500" in L10
int6P1Row = 2


' REPLACE

For i = intInitialValue To intFinalValue




j = i + 1

Range( _
"D2:I2,D22:I22,D42:I42,D62:I62,D82:I82,D102:I102,D122:I122,D142:I142,D162:I162,D182:I182,D202:I202,D222:I222" _
).Select

Selection.Replace i, _
Replacement:=j, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False


'AUTO FILLING


Application.ScreenUpdating = False
Range("D2:I2").AutoFill Destination:=Range("D2:I9"), Type:=xlFillDefault
Range("D22:I22").AutoFill Destination:=Range("D22:I29"), Type:=xlFillDefault
Range("D42:I42").AutoFill Destination:=Range("D42:I49"), Type:=xlFillDefault
Range("D62:I62").AutoFill Destination:=Range("D62:I69"), Type:=xlFillDefault
Range("D82:I82").AutoFill Destination:=Range("D82:I89"), Type:=xlFillDefault
Range("D102:I102").AutoFill Destination:=Range("D102:I109"), Type:=xlFillDefault
Range("D122:I122").AutoFill Destination:=Range("D122:I129"), Type:=xlFillDefault
Range("D142:I142").AutoFill Destination:=Range("D142:I149"), Type:=xlFillDefault
Range("D162:I162").AutoFill Destination:=Range("D162:I169"), Type:=xlFillDefault
Range("D182:I182").AutoFill Destination:=Range("D182:I189"), Type:=xlFillDefault
Range("D202:I202").AutoFill Destination:=Range("D202:I209"), Type:=xlFillDefault
Range("D222:I222").AutoFill Destination:=Range("D222:I229"), Type:=xlFillDefault




' DATA COMPARING



If Sheet2.Range("U3").Value = "6x" Then
Sheet2.Select
Sheet2.Range("D2,L2").Copy
Sheets("6P1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.Offset(1, 0).Select
Sheets("Sheet2").Select
End If


If Sheet2.Range("V3").Value = "6x" Then
Sheet2.Select
Sheet2.Range("E2,L2").Copy
Sheets("6P1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.Offset(1, 0).Select
Sheets("Sheet2").Select
End If


If Sheet2.Range("W3").Value = "6x" Then
Sheet2.Select
Sheet2.Range("F2,L2").Copy
Sheets("6P1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.Offset(1, 0).Select
Sheets("Sheet2").Select
End If

If Sheet2.Range("X3").Value = "6x" Then
Sheet2.Select
Sheet2.Range("G2,L2").Copy
Sheets("6P1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.Offset(1, 0).Select
Sheets("Sheet2").Select
End If

If Sheet2.Range("Y3").Value = "6x" Then
Sheet2.Select
Sheet2.Range("H2,L2").Copy
Sheets("6P1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.Offset(1, 0).Select
Sheets("Sheet2").Select
End If

If Sheet2.Range("Z3").Value = "6x" Then
Sheet2.Select
Sheet2.Range("I2,L2").Copy
Sheets("6P1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.Offset(1, 0).Select
Sheets("Sheet2").Select
End If






Application.ScreenUpdating = True














Next i


Application.Run "AutoClear"


Range( _
"D2:I2,D22:I22,D42:I42,D62:I62,D82:I82,D102:I102" _
).Select

Selection.Replace j, _
Replacement:=3, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False







End Sub
 
G

GillesD

Member
VBA program

lottodude

I tried your program on my computer. It ran in 40 seconds although there was no data to replace, compare and play with.

The only problem I had was the line: Application.Run "AutoClear"
This may be due to the fact I use the French version of Excel. I may try to look at the code more closely tomorrow to see what could be the cause of your problem.
 
L

lottodude

Member
Re: VBA program

GillesD said:
lottodude

I tried your program on my computer. It ran in 40 seconds although there was no data to replace, compare and play with.

The only problem I had was the line: Application.Run "AutoClear"
This may be due to the fact I use the French version of Excel. I may try to look at the code more closely tomorrow to see what could be the cause of your problem.
Click to expand...

Nonono,
sorry about that, Gilles, Application.Run "AutoClear" is referring to running another macro.....

I'll post the excel file..
it's at

http://www.geocities.com/c_kent301/data1.xls

the macro i was running was "replace_autofill"...
whenever i run it, it slows down after around 200...

i'll take it off later...
 
L

lottodude

Member
Re: VBA program

GillesD said:
lottodude
IIt ran in 40 seconds although there was no data to replace,
Click to expand...

btw, 40 seconds is quite impressive...
just curious, how fast is your computer??
i'm thinking of upgrading my computer...

i've never found my computer slow until I recently using macro to do testings... it's slow.. (600mhz celeron & 1ghz duron)...
 
N

Nick Koutras

Member
to retxx

Lets describe what you want....

1. To load a Lotto Dbase
2. To be able to add new results on it
3. To be able the save it again.
4. To be able to display a frequency
distribution of a selected N-tuplet

5. To Display a graph of that Frequency
or in order of apperance.


Since the N-tuplet frequency has come up
again in this forum, I'll prepare such
a Worksheet for the use of all members.

Nick
 
R

retxx

Member
nick

that would be great nick. Really appreciate it as others that play keno will also. It is a tough game of randomizing that any help is thankfully accepted.
 
N

Nick Koutras

Member
Hello All,

I have just send to LT the first version
of the Lotto N-tuplets Spreadsheet.

As I indicated, in a post above, the program does the following:

- Loads a Game database
These files can be from up to Lotto Pool of 80 numbers and up to 3000 Game results to cover Keno games.

- Displays, upon selection by the user, the N-tuplets appearing in the file loaded.

N-tuplets covered in this version are from 2 to 5, and up to a maximum quantity of 65,000


If the program may be useful I probably can incorporate more options and eliminate
the present limitations.

Any ideas for improvement will be appreciated.


Nick
 
R

retxx

Member
nick

looking forward to the spreadsheet to follow here in Mass. Is it up or do we wait for LT to post it. Thanks
 
You must log in or register to reply here.

Sidebar

Top