Application-defined or object-defined error

[macrofan]

I tried to call a procedure when the workbook opens, the code is like this:

Private Sub Workbook_Open()
Call Sheet1.BollingerBand
Call Sheet3.Candle
End Sub

However, the program stops at first call with a meesage of "Application-defined or object-defined error".

If I delete the line for the first call, no error message. And now if I run the procedure of "BollingerBand" within the workbook, everything is working.

My qauestion is why I can run the procedure but just couldn't call it.

Thanks for yhour reply!

 

[hiker95]

macrofan,

Try:

Private Sub Workbook_Open()
Call BollingerBand
Call Candle
End Sub

 

[macrofan]

macrofan,

Try:

Private Sub Workbook_Open()
Call BollingerBand
Call Candle
End Sub

If I do this, it will show another message of "compile error: Sub or Function not defined". It seems that I have to define the location of the procedures in order to call them. What puzzles me is the second call works. I copied the way to call the first one, but get a error messaage.

 

[hiker95]

macrofan,

Is BollingerBand a VBA macro?

If it is a macro, is it in a Module?

Can we see the macro code?

If posting VBA code, please use Code Tags, see below in my Signature block: If posting VBA code, please use Code Tags - like this

 

[tusharm]

There are times when Excel hasn't fully initialized all the workbook components when the code executes. What I do is put in the workbook_open routine: application.ontime now(),"RealSub" and put the actual code of interest in the RealSub subroutine, which should be in a standard module.

I tried to call a procedure when the workbook opens, the code is like this:

Private Sub Workbook_Open()
Call Sheet1.BollingerBand
Call Sheet3.Candle
End Sub

However, the program stops at first call with a meesage of "Application-defined or object-defined error".

If I delete the line for the first call, no error message. And now if I run the procedure of "BollingerBand" within the workbook, everything is working.

My qauestion is why I can run the procedure but just couldn't call it.

Thanks for yhour reply!

 

[macrofan]

macrofan,

Is BollingerBand a VBA macro?

If it is a macro, is it in a Module?

Can we see the macro code?

If posting VBA code, please use Code Tags, see below in my Signature block: If posting VBA code, please use Code Tags - like this

It is a procedure inside a sheet, not in a module. The code is:

Sub BollingerBand()
'Simple Moving Average
Sheets("YHOO").Range("L2").Select
ActiveCell.FormulaR1C1 = "SMA(20)"
Sheets("YHOO").Range("L133").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(R[-19]C[-6]:RC[-6])"
Selection.Copy
Sheets("YHOO").Range("L134:L262").Select
ActiveSheet.Paste

' Bollinger Upper Band
Sheets("YHOO").Range("M2").Select
ActiveCell.FormulaR1C1 = "BB Up"
Sheets("YHOO").Range("M133").Select
ActiveCell.FormulaR1C1 = "=RC[-1]+2*STDEV(R[-19]C[-7]:RC[-7])"
Selection.Copy
Sheets("YHOO").Range("M134:M262").Select
ActiveSheet.Paste
' Bollinger Lower Band
Sheets("YHOO").Range("N2").Select
ActiveCell.FormulaR1C1 = "BB Low"
Sheets("YHOO").Range("N133").Select
ActiveCell.FormulaR1C1 = "=RC[-2]-2*STDEV(R[-19]C[-8]:RC[-8])"
Selection.Copy
Sheets("YHOO").Range("N134:N262").Select
ActiveSheet.Paste
' EMA(9)Moving Average
Sheets("YHOO").Range("O2").Select
ActiveCell.FormulaR1C1 = "EMA(9)"
Sheets("YHOO").Range("O133").Select
ActiveCell.FormulaR1C1 = "=RC[-9]*0.2+AVERAGE(R[-9]C[-9]:R[-1]C[-9])*0.8"
Sheets("YHOO").Range("O134").Select
ActiveCell.FormulaR1C1 = "=RC[-9]*0.2+R[-1]C*0.8"
Selection.Copy
Sheets("YHOO").Range("O135:O262").Select
ActiveSheet.Paste
End Sub

But as another friend's suggestion, I can try it from a module. But why it works for the second call, not this one?

 

[MARK858]

Putting the pieces together I take from what you say simply
BollingerBand is a macro
&
Sheet3.Candle is a normal worksheet
Yes?

 

[macrofan]

There are times when Excel hasn't fully initialized all the workbook components when the code executes. What I do is put in the workbook_open routine: application.ontime now(),"RealSub" and put the actual code of interest in the RealSub subroutine, which should be in a standard module.

Maybe I should try it as a module and call this module.

 

[macrofan]

Putting the pieces together I take from what you say simply
BollingerBand is a macro
&
Sheet3.Candle is a normal worksheet
Yes?

They are virtually tge same thing. Both are procedures but in different sheets. If I go to code page of the sheets, I can choose the code from the combox containing (declarations) and the procedures.

 

[MARK858]

Having not tried calling 2 sheet subs from different sheets in the same workbook before I wouldn't attempt to speculate.

I have always done what your friend suggested and put multiple subs in std module/s and called them exactly as Hiker95 has suggested