Macro Instability

[Guest]

I have just built a spreadsheet that includes about 32 macros and they don't
always seem to function correctly. I essentially have built a game where when
an icon is clicked a value located in another worksheet will appear. Thus, I
have built a number of macros that are assigned to various icons, a value
appears. The macros use apply a formula, e.g. "=," to locate the value on the
other worksheet. Some macros work fine all of the time while others produce
odd results or require double clicks of the icon to produce the desired
value).

I am hoping to identify whether these problems are correctable through
debugging the programming or if MSExcel macros are inherently unstable or
prone to errors. Additionally, is this apparent instability associated with
the total number of macros I am attempting to use in this workbook (there are
a lot). The responses I receive here will aid me in approaching a resolution
to the problem, e.g. spend the day debugging (assuming the system will remain
stable once it is debugged) or accept the fact that if I insist on using so
many macros that the system will never function correctly.

Additionally, I am including the text of one macro that works correctly (Sub
Box1) and another that does not (Sub Box5). They essentially do the same
tasks, but for different icons. They were both created using the "record
macro" tool. Given that the macro text pattern is different in each example,
I suspect the "record macro" tool simply makes errors in recognizing actions.
Assuming the MSExcel macro system is generally stable once macros are
correctly written, I am contemplating just copying and pasting the successful
macro (with appropiate changes) into all of the flawed macro routines.
However, if the system is unstable then doing this would just be a waste of
time.

Thanks.

Sub Box1()
'
' Box1 Macro
' Macro recorded 2/24/2006 by Nat Pope
'

'
Range("C8").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-4]C[10]"
Sheets("Sheet1").Select
Range("L4").Select
ActiveCell.FormulaR1C1 = "0"
Sheets("Sheet2").Select
Range("C8").Select
End Sub


Sub Box5()
'
' Box5 Macro
' Macro recorded 2/25/2006 by Nat Pope
'

'
ActiveCell.FormulaR1C1 = "=Sheet1!R[-8]C[10]"
Sheets("Sheet1").Select
Range("L8").Select
ActiveCell.FormulaR1C1 = "0"
Sheets("Sheet2").Select
End Sub

 

[davesexcel]

SOme formulas need to be activated, somehow, especially if they are
macro based

 

[Guest]

Some of your macros rely on the correct setting of ActiveCell prior to the
macro being called. To trap errors in the setting of ActiveCell, insert
calls to :

Sub WhereIsActiveCell()
Dim r As Range
Dim s As String
Set r = ActiveCell
s = r.Address & Chr(10)
s = s & r.Worksheet.Name & Chr(10)
s = s & r.Worksheet.Parent.Name
MsgBox (s)
End Sub

in your subs that do not explicitly Select a cell themselves.
--
Gary's Student

I have just built a spreadsheet that includes about 32 macros and they don't
always seem to function correctly. I essentially have built a game where when
an icon is clicked a value located in another worksheet will appear. Thus, I
have built a number of macros that are assigned to various icons, a value
appears. The macros use apply a formula, e.g. "=," to locate the value on the
other worksheet. Some macros work fine all of the time while others produce
odd results or require double clicks of the icon to produce the desired
value).

I am hoping to identify whether these problems are correctable through
debugging the programming or if MSExcel macros are inherently unstable or
prone to errors. Additionally, is this apparent instability associated with
the total number of macros I am attempting to use in this workbook (there are
a lot). The responses I receive here will aid me in approaching a resolution
to the problem, e.g. spend the day debugging (assuming the system will remain
stable once it is debugged) or accept the fact that if I insist on using so
many macros that the system will never function correctly.

Additionally, I am including the text of one macro that works correctly (Sub
Box1) and another that does not (Sub Box5). They essentially do the same
tasks, but for different icons. They were both created using the "record
macro" tool. Given that the macro text pattern is different in each example,
I suspect the "record macro" tool simply makes errors in recognizing actions.
Assuming the MSExcel macro system is generally stable once macros are
correctly written, I am contemplating just copying and pasting the successful
macro (with appropiate changes) into all of the flawed macro routines.
However, if the system is unstable then doing this would just be a waste of
time.

Thanks.

Sub Box1()
'
' Box1 Macro
' Macro recorded 2/24/2006 by Nat Pope
'

'
Range("C8").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-4]C[10]"
Sheets("Sheet1").Select
Range("L4").Select
ActiveCell.FormulaR1C1 = "0"
Sheets("Sheet2").Select
Range("C8").Select
End Sub


Sub Box5()
'
' Box5 Macro
' Macro recorded 2/25/2006 by Nat Pope
'

'
ActiveCell.FormulaR1C1 = "=Sheet1!R[-8]C[10]"
Sheets("Sheet1").Select
Range("L8").Select
ActiveCell.FormulaR1C1 = "0"
Sheets("Sheet2").Select
End Sub

 

[keepITcool]

Nat,

VBA is a 'bit' harder to learn than Excel...

Do yourself a favour and buy a book like VBA for Dummies.
A couple of hours of studying will save you many hours of frustration.
Once you have an understanding of the basics the rest will fall into
place quite easily.

Currently you may have many macro's but it looks to me your VBA skills
haven't progressed much beyond recording... and the recorder is not the
most efficient coder...

The macro recorder simply records key strokes and movements and is
thius forced to use SELECT,ACTIVATE en SELECTION. Whereas well written
macros will avoid those methods, as they require a lot of unnecessary
screen updating.

E.g. Sub Box1 could simply be written as:

Sub Box1()
Range("Sheet2!C8").Formula = "=Sheet1!M4"
Range("Sheet1!L4").Formula = "0"
End Sub



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam

I have just built a spreadsheet that includes about 32 macros and
they don't always seem to function correctly. I essentially have
built a game where when an icon is clicked a value located in another
worksheet will appear. Thus, I have built a number of macros that are
assigned to various icons, a value appears. The macros use apply a
formula, e.g. "=," to locate the value on the other worksheet. Some
macros work fine all of the time while others produce odd results or
require double clicks of the icon to produce the desired value).

I am hoping to identify whether these problems are correctable
through debugging the programming or if MSExcel macros are inherently
unstable or prone to errors. Additionally, is this apparent
instability associated with the total number of macros I am
attempting to use in this workbook (there are a lot). The responses I
receive here will aid me in approaching a resolution to the problem,
e.g. spend the day debugging (assuming the system will remain stable
once it is debugged) or accept the fact that if I insist on using so
many macros that the system will never function correctly.

Additionally, I am including the text of one macro that works
correctly (Sub Box1) and another that does not (Sub Box5). They
essentially do the same tasks, but for different icons. They were
both created using the "record macro" tool. Given that the macro text
pattern is different in each example, I suspect the "record macro"
tool simply makes errors in recognizing actions. Assuming the
MSExcel macro system is generally stable once macros are correctly
written, I am contemplating just copying and pasting the successful
macro (with appropiate changes) into all of the flawed macro
routines. However, if the system is unstable then doing this would
just be a waste of time.

Thanks.

Sub Box1()
'
' Box1 Macro
' Macro recorded 2/24/2006 by Nat Pope
'

'
Range("C8").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-4]C[10]"
Sheets("Sheet1").Select
Range("L4").Select
ActiveCell.FormulaR1C1 = "0"
Sheets("Sheet2").Select
Range("C8").Select
End Sub


Sub Box5()
'
' Box5 Macro
' Macro recorded 2/25/2006 by Nat Pope
'

'
ActiveCell.FormulaR1C1 = "=Sheet1!R[-8]C[10]"
Sheets("Sheet1").Select
Range("L8").Select
ActiveCell.FormulaR1C1 = "0"
Sheets("Sheet2").Select
End Sub

 

[Tom Ogilvy]

Sub StableExample()
Worksheets("Sheet1").FormulaR1C1 = "=Sheet2!R8C10"
Worksheets("Sheet2").Value = 0
End Sub

Make your macros like this andyou should have no problems

If you have attached a macro to a shape such as a rectangle: in the example
"Rectangle 3" another approach is to do things relative to the position of
the rectangle. You can use application.Caller to get the name of the
rectangle (then if you rename it, the code should still work or you can
assign more than one rectangle to the macro and make it operate in a
relative fashion.

Sub Rectangle3_Click()
Dim rect3 as Rectangle
sName = Application.Caller
set rect3 = Activesheet.Rectangles(sName)
set rng = rect3.TopLeftCell
rng.offset(0,-1).FormulaR1C1 = "=Sheet2!R8C10"
End Sub


--
Regards,
Tom Ogilvy

I have just built a spreadsheet that includes about 32 macros and they don't
always seem to function correctly. I essentially have built a game where when
an icon is clicked a value located in another worksheet will appear. Thus, I
have built a number of macros that are assigned to various icons, a value
appears. The macros use apply a formula, e.g. "=," to locate the value on the
other worksheet. Some macros work fine all of the time while others produce
odd results or require double clicks of the icon to produce the desired
value).

I am hoping to identify whether these problems are correctable through
debugging the programming or if MSExcel macros are inherently unstable or
prone to errors. Additionally, is this apparent instability associated with
the total number of macros I am attempting to use in this workbook (there are
a lot). The responses I receive here will aid me in approaching a resolution
to the problem, e.g. spend the day debugging (assuming the system will remain
stable once it is debugged) or accept the fact that if I insist on using so
many macros that the system will never function correctly.

Additionally, I am including the text of one macro that works correctly (Sub
Box1) and another that does not (Sub Box5). They essentially do the same
tasks, but for different icons. They were both created using the "record
macro" tool. Given that the macro text pattern is different in each example,
I suspect the "record macro" tool simply makes errors in recognizing actions.
Assuming the MSExcel macro system is generally stable once macros are
correctly written, I am contemplating just copying and pasting the successful
macro (with appropiate changes) into all of the flawed macro routines.
However, if the system is unstable then doing this would just be a waste of
time.

Thanks.

Sub Box1()
'
' Box1 Macro
' Macro recorded 2/24/2006 by Nat Pope
'

'
Range("C8").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-4]C[10]"
Sheets("Sheet1").Select
Range("L4").Select
ActiveCell.FormulaR1C1 = "0"
Sheets("Sheet2").Select
Range("C8").Select
End Sub


Sub Box5()
'
' Box5 Macro
' Macro recorded 2/25/2006 by Nat Pope
'

'
ActiveCell.FormulaR1C1 = "=Sheet1!R[-8]C[10]"
Sheets("Sheet1").Select
Range("L8").Select
ActiveCell.FormulaR1C1 = "0"
Sheets("Sheet2").Select
End Sub