Useful Comment Concerning Memory Read/Write Errors
 

Or what I hope people will find useful.
I have been having read and write memory errors while generating or
trying to change captions for commandbuttons I have generated in a
macro that loops so I can generate multiple commandbuttons
From reading other threads in this forum I have set every variable
used to either Nothing or Empty when finished with it and it did work.
My macro's run like a dream.

I have posted this as once you seem to get in the trap of getting a
memory error, it is very difficult to pin point the problem.

I will certainly ensure I empty all variable when finished with them
in the future.

Has anybody else got any useful must do's to prevent memory errors ?

Useful Comment Concerning Memory Read/Write Errors
 

I agree. I know that all variables are (or should be) wiped out when a
module's code stops running, but I still make it my practice to set every
object variable to Nothing before I end my routines - and preferably as soon
as I am done using it. It may be redundant, it may be unnecessary, but it
assures me that I am in control of the memory situation - and IMHO, helps to
avoid problems that can come about by sloppy code (mine or others') or errors
(mine or others').

K Dales

"Donna" wrote:

Or what I hope people will find useful.
I have been having read and write memory errors while generating or
trying to change captions for commandbuttons I have generated in a
macro that loops so I can generate multiple commandbuttons
From reading other threads in this forum I have set every variable
used to either Nothing or Empty when finished with it and it did work.
My macro's run like a dream.

I have posted this as once you seem to get in the trap of getting a
memory error, it is very difficult to pin point the problem.

I will certainly ensure I empty all variable when finished with them
in the future.

Has anybody else got any useful must do's to prevent memory errors ?

Useful Comment Concerning Memory Read/Write Errors
 

Unfortunately I seem to have spoke too soon!.
It sid work (or so I thought) but it's back and I don't know why.
I have a function that generates a commandbutton on a at a specifiec
place with a specific size depending upon the variable I pass into the
function. I also change the caption of the button and it's this caption
that seems to give me the memory error.
I can always generate the first one, then most of the time I can
generate the second button but when I try and do anything concerning
the caption (even if I try and look at it in the locals window!) I get
the memory error. I have stepped through the code making sure that the
all variables are set to empty/Nothing prior to being reassigned but I
still get the memory error.
If I change the function into a Sub and add a loop in there I can
generate as many buttons as i want whether I set all my variable to
nothing/empty or not.
any suggestions anybody. I have already used the code cleaner that is
downloadable off the net. It must be my code somewhere.
I can post if it will help anybody.

Useful Comment Concerning Memory Read/Write Errors
 

I have found my problem but I don't know why it was a problem.
I was generating a commandbutton on a worksheet with the following
line:-

Set NewCommandButton =
ActiveSheet.OLEObjects.Add(classtype:="Forms.Comma ndButton.1", _
Link:=False, _
DisplayAsIcon:=False, _
Left:=Range(LeftPos).Left + 2, _
Top:=Range(LeftPos).Top + 7, _
Width:=wdh, Height:=29)
where LeftPos="B5" As String and Wdh=100 As Integer.

This caused a memory access error.

When I changed the above line to remove all the Ranges everything was
fine. ie:-

LeftPos = Range("B5").Left
TopPos = Range("B5").Top
wdh = 100
Set NewCommandButton =
ActiveSheet.OLEObjects.Add(classtype:="Forms.Comma ndButton.1", _
Link:=False, _
DisplayAsIcon:=False, _
Left:=LeftPos, _
Top:=TopPos, _
Width:=wdh, _
Height:=29)

Can anybody enlighten me as to why?
Thanks for your responses

Useful Comment Concerning Memory Read/Write Errors
 

That is indeed strange, and could be a bug in VBA: the only thing you are
changing is from a string variable to a literal string and I don't think that
should cause a problem - at least it should be acceptable syntax. So I don't
think it was your fault!

But I still hold to my advice about setting all objects back to Nothing,
consider it good programming practice.

" wrote:

I have found my problem but I don't know why it was a problem.
I was generating a commandbutton on a worksheet with the following
line:-

Set NewCommandButton =
ActiveSheet.OLEObjects.Add(classtype:="Forms.Comma ndButton.1", _
Link:=False, _
DisplayAsIcon:=False, _
Left:=Range(LeftPos).Left + 2, _
Top:=Range(LeftPos).Top + 7, _
Width:=wdh, Height:=29)
where LeftPos="B5" As String and Wdh=100 As Integer.

This caused a memory access error.

When I changed the above line to remove all the Ranges everything was
fine. ie:-

LeftPos = Range("B5").Left
TopPos = Range("B5").Top
wdh = 100
Set NewCommandButton =
ActiveSheet.OLEObjects.Add(classtype:="Forms.Comma ndButton.1", _
Link:=False, _
DisplayAsIcon:=False, _
Left:=LeftPos, _
Top:=TopPos, _
Width:=wdh, _
Height:=29)

Can anybody enlighten me as to why?
Thanks for your responses

Useful Comment Concerning Memory Read/Write Errors
 

The plot thickens even more.
I am slowly building up the functionality I want getting one thing to
work in the loop before adding the next thing.
I can generate the button, rename it, then change the LeftPos/TopPos
etc before generating the next and all is well.
But I also want to write the event code while in the loop (which I need
to do as it references a variable that changes while in the loop).
Adding the lines that write the event click code affects the running of
my macro.
It will generate the 1st button, rename it, write the event code then
it will generate the 2nd button but there is something wrong with that
second button. You cannot go near it with anything (cursor also)
without it resulting in a memory error.
It must be the way I'm referencing it in my code....

Set NewCommandButton =
ActiveSheet.OLEObjects.Add(classtype:="Forms.Comma ndButton.1", _
Link:=False, _
DisplayAsIcon:=False, _
Left:=LeftPos, _
Top:=TopPos, _
Width:=wdh, _
Height:=29)
With NewCommandButton.Object
.Caption = Title
End With

k = ActiveSheet.Shapes.Count
line1 = "Sub CommandButton" & k & "_Click"
With ActiveWorkbook.VBProject.VBComponents _
(ActiveSheet.CodeName).CodeModule
X = .CountOfLines
.InsertLines X + 1, line1
.InsertLines X + 2, "d=" & Chr(34) & ChartName
.InsertLines X + 3, "Call Common"
.InsertLines X + 4, "End Sub"
End With

The variables Title, LeftPos, TopPos, Wdh and ChartName all change as
the program loops.

I can generate all the buttons in one loop, then generate all the code
after in a second loop (this I can do in 1 macro) without it failing
but that way I have lost the variable ChartName. I need to add the code
during the same loop as the button generation.
I really can not decide if it's a bug or my programming.