MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Calling a sub from another sub -- why the error message?

  Asked By: Cristina    Date: Nov 02    Category: MS Office    Views: 4484

Please help. I just want to call a sub called chkTweak from a sub
called sbTheta.

As background sbTheta is linked to a scrollbar which allows changing
of the angle theta. chkTweak is contained in another procedure found
when I click "sheet 1" on the project. ChkTweak is linked to a
checkbox, when true slects a range (3 cells, one of which is the cell
that contains the theta = angle data), paints the range yellow, and
adds the messages: "LEARN MODE" and "Tweaking ..."

The scrollbar and the checkbox are both on the same sheet.

Because I have several places where I want to call ChkTweak, I want to
be able to call this sub from the other subs. Why does it not work as
it is? Does anyone have the solution? The error message says
"Compile error -- sub or Function not defined"

Thanks for any help.

The present code follows:

Sub sbTheta()
Range("theta").Formula = "= rand90 - 45"

' Add tweaking messages -- chTweak_Click has approp messages
' But this does not work

Call chkTweak_Click

End Sub

Private Sub chkTweak_Click()

' Turn flicker off
Application.ScreenUpdating = False

If chkTweak.Value = True Then

Application.Goto Reference:="input"
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

' Add message / flag on g10, g11

Range("flagon") = "LEARN MODE"
Range("flagtime") = "Tweaking ..."


Application.Goto Reference:="input"
Selection.Interior.ColorIndex = xlNone

' Remove Tweaking message

Range("flagon") = " "
Range("flagtime") = " "


End If

' Turn screen updating on
Application.ScreenUpdating = True

End Sub



6 Answers Found

Answer #1    Answered By: Shelia Wells     Answered On: Nov 02

I can't find a problem. I would suggest that you make sure you are using
option explicit, and then go debug/compile project. If there is an error  this
should show you just where the error is.

Answer #2    Answered By: Roop Kapoor     Answered On: Nov 02


(you can probably get away without the 'Call' too)

The Sheet1 above is the sheetname as shown in the Project Explorer
Pane of the vbe (the name not in brackets). You'll probably have to
change 'Private' to 'Public' too.

Instead of writing the bulk of your code  in the code module for the
sheet, it may be better to write it in a standard module and call  it
from the ChkTweak event handler, and from anywhere else for that matter.

Answer #3    Answered By: Abasi Massri     Answered On: Nov 02

I tried the folowing suggestions:

1. Change 'Private' to 'Public' regarding chkTweak_ Click. Did did
not work

2 Tried to put sbTheta ()in the same module as chkTweak_Click. Did
not work.

3. Refer to Sheet1.chkTweak. Did not work.

4. Put bulk of your code  in standard module, and call  it from event
handler, etc ...
I am pretty sure I can create new subs paint_yellow () and
display_messages (), which together do the same thing as as
chkTweark_Click, except that it will not show the checkbox  as checked.
Well its a 70% solution.

Lesson I seemed to have learned:

1. Event handlers don't want to be called  from subs that are located
in a different module, and

2. Sometimes even from a sub in the same module???

Am I wrong? Have a happy Fourth of july!!

Answer #4    Answered By: Aylin Kaya     Answered On: Nov 02

The way you have "chkTweak_ Click" here, there is a space after the
underline. chkTweak_Click is a single word.

chkTweak_Click HAS to be in the module that belongs to the sheet  or form
that chkTweak is located in. It SHOULD be defined as private, but I don't
think it makes any difference.

sbTheta can be private in the same module, or public in a module you create

What do you mean by "did not work". If nothing at all happened, then you
don't have your click  routine in the right place, or it has the wrong name.

The best way to create a click routine is to go back to Excel, and turn  on
the control toolbar. Then put the spreadsheet into design mode  (from the
toolbar) and double-click your Tweak object. That will create proper event
subroutine in the right module. It might not be the click one, though -
just select the click event from the right-hand dropdown.

Once you've written your code, compile it through the "debug" menu in the VB
editor, and make sure it compiles cleanly. (However, bad naming or
positioning of event handlers will not show as compile errors.)

You are correct that event routines don't want to be called  from elsewhere,
even if they are declared as public. Indeed, no routines in the sheet
modules can be called in this way. I think the reason for this is that the
sheet module is actually a class module, not a normal module.

"Sometimes even from a sub in the same module." Technically incorrect.
Remembering that the event routine needs to be located in the module
associated with the object it services, it can then be called from other
routines in that same module.

You possibly need to post your code  here, and in this case you'll need to
mention which module each part of the code is located in.

Answer #5    Answered By: Rhys Evans     Answered On: Nov 02

Yeah, I figured it out. Thanks for all the help. The problem was
that I was using shapes as buttons. As such I assigned macros, and
did not rely on event macros. Somehow, my scrollbars were not
properly initiated (as Dave S suggests). I followed Dave's advice,
started over and everything is in its right place. Somehow my
scrollbars acted more like shapes than real objects. From now on, I
will be more careful in using shapes that link to macros. Thanks

Answer #6    Answered By: Mildred Bailey     Answered On: Nov 02

Yes, you're quite correct. I was remembering part of the problem only.

A public sub/function in a sheet  module is, indeed, public. However, it's
not directly accessible.

The sheet module is a class module. I.e. it doesn't exist by itself, only
as part of its sheet. So, consider my Sheet1 code:

Option Explicit

Public Sub CommandButton1_Click()
MsgBox "click"
Call UnClick
End Sub

Public Sub UnClick()
MsgBox "unclick"
End Sub

Clearly, clicking the button on sheet 1 will call  the click  subroutine and
that will in turn  call the second subroutine.

My Module1 code:

Option Explicit

Sub a()
Call CommandButton1_Click
Call UnClick
End Sub

refuses to compile and fails if I try to run the sub without compilation.
This is because the code  in Sheet1 doesn't exist in isolation. However, I
can qualify the two subroutines:

Option Explicit

Sub a()
Call Sheet1.CommandButton1_Click
Call Sheet1.UnClick
End Sub

and both get called  fine (and the click sub continues to call the unclick
sub as well). I.e. sub a gives three message  boxes - "click" once, then
"unclick" twice.

There isn't actually anything special about a click subroutine. It's just
that it needs to be named this way so the button can find it - because the
button is not actually bound to the click routine in any way.

All that said, I don't actually recommend calling  methods of a sheet class
from ordinary modules - not quite sure why, it just doesn't "feel" right.

Didn't find what you were looking for? Find more on Calling a sub from another sub -- why the error message? Or get search suggestion and latest updates.