none
Process not transferred to Subroutine RRS feed

  • Question

  • I have created a "Button" on a spreadsheet - something I have often done before without trouble.  When I double-click, I am transferred to the VBA screen, and to the "Properties" window, where I have edited various aspects of the button; and to the Process window, where the start and end are already created.  I inserted the name of the Routine that I wish to be performed, thus :-

    ------------------------------------

    Private Sub CommandButton1_Click()

        myDir

    End Sub

    ------------------------------------

    Then, in the "ThisWorkbook" module, I have written code, that works just fine, as "Public Sub myDir()".

    BUT - putting just the name of that SUb into the Button code results in an Error message:-

    -------------------------------------

    Compile error

    Sub or Function not defined

    -------------------------------------------

    If I go to that Subroutine, and press F5, it runs just fine.

    So WHY will it not transfer ?


    • Edited by RobinClay Sunday, January 19, 2020 11:17 PM
    Sunday, January 19, 2020 11:10 PM

Answers

  • To:  RobinClay
    re:  calling a routine in a Private module

    MyDir sub belongs in a standard/general module.
    If you must leave it the Private ThisWorkbook module; this works...
    '---
    Private Sub CommandButton1_Click()
      Call ThisWorkbook.MyDir
    End Sub
    '---

    Generally, code in a worksheet module should deal with that worksheet.
    Code in the This workbook module should use the built-in Event code, other routines belong in a standard/general module.
    There are many exceptions.
    '---

    The free Custom_Functions add-in has 20 new Excel functions including the X_vLookUp function.
    Monday, January 20, 2020 2:11 AM

All replies

  • To:  RobinClay
    re:  calling a routine in a Private module

    MyDir sub belongs in a standard/general module.
    If you must leave it the Private ThisWorkbook module; this works...
    '---
    Private Sub CommandButton1_Click()
      Call ThisWorkbook.MyDir
    End Sub
    '---

    Generally, code in a worksheet module should deal with that worksheet.
    Code in the This workbook module should use the built-in Event code, other routines belong in a standard/general module.
    There are many exceptions.
    '---

    The free Custom_Functions add-in has 20 new Excel functions including the X_vLookUp function.
    Monday, January 20, 2020 2:11 AM
  • Thank you !

    I don't really understand, but so long as it works . . . ;-)


    Monday, January 20, 2020 1:42 PM
  • To:  RobinClay
    re:  "I don't really understand,"

    Appreciate hearing back from you.

    In general...
    The 'ThisWorkbook' module and the modules attached to worksheets are "Private".
    That is their contents are not available to other modules (or other workbooks).
    A standard module is not private unless you make it so.

    "Normal" practice would be to place most code in a standard module(s) and use "Option Private Module" as a single line at the top of the module.
    That allows access to the module only from within the workbook.
    If you don't provide .xlsm workbooks to others, then it doesn't really matter.

    Monday, January 20, 2020 2:13 PM