locked
"Compile Error: Procedure too large" problem... RRS feed

  • Question

  • Hi there,

    I got a macro with 2220 lines.. I know this is really too long but the reason for this is I have several inputbox for users to input value, which will be used throughout the whole macro. I have tried to split the macro, but the inputbox value doesn't work in other macro. As you can imagine, it is ridiculous to ask user to input the same value for every spliited macro. I am not sure why the inputbox value cannot be used in other macro,, can anyone please help?

    My code:

    Private Sub CommandButton1_Click()
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    strFile = InputBox("Which month of report do you want to generate? (yyyy-mm)(e.g. 2012-06):", , Format(Date, "yyyy-mm"))
    .
    .
    .
    Call pivot
    End Sub

    Sub pivot()
    .
    .    <-- strFile will be used in some of these lines
    .
    End Sub

    When the macro run till the pivot(), it says it cannot find the object "strFile".
    I have tried to use:
    1) Public Sub CommandButton1_Click()
    2) I dont' use "Call pivot". Instead, I use "pivot()" but it says it requires a "=" in the line of "pivot()".

    Please help...!!
    Thanks in advance!!!

    Tuesday, July 3, 2012 2:51 AM

Answers

  • When you want a Global variable (available to all subs in all modules) you dimension the variable as follows at the top of a standard module.

    Public strFile As String

    When you have done that, do NOT diemension it anywhere else in your project either as Public or Dim. If you use the Dim statement in another sub then when you run that sub, the value of the variable that has been set in another sub is destroyed.

    In the VBA editor you should select menu item Edit -> Find and enter "Dim strFile" (without the quotes) to search. Set the Option to "Search Current Project" and then search for any instances of Dim strFile and then remove them.

    If you only want a variable to be available to all subs in a specific module, but not available to other modules, then you dimension the variable as follows at the top of the required module.

    Dim strFile As String

    Then in the VBA editor you should select menu item Edit -> Find and enter "Dim strFile" (without the quotes) to search. This time set the Option to "Current Module" and then search for any other instances of Dim strFile and then remove them.


    Regards, OssieMac

    • Marked as answer by Jackynck Tuesday, July 3, 2012 6:31 AM
    Tuesday, July 3, 2012 6:13 AM

All replies

  • You create the variable for the inputbox return as a Global variable and it can be used throughout the project. To do this:-

    At the top of a Standard Module before any subs insert the following line:-

    Public strFile

    The above is done in lieu of the standard method of declaring variables within a sub where the variable is only available in that sub.

    Added with edit after original post:

    (Must be in a Standard Module to be available throughout all modules. If only required in one module then use Dim strFile at the top of the required module and it will be available throughout that module but not in other modules.)


    Regards, OssieMac



    • Edited by OssieMac Tuesday, July 3, 2012 4:36 AM
    Tuesday, July 3, 2012 4:32 AM
  • Hi OssieMac,

    Thanks! How about the "Dim"? Before using strFile, I have to Dim it right? (e.g. Dim strFile As String) I encounter simliar problems like I have Dim n as String and put n = xxxxxxx (a path).

    But then, when the second macro runs, some lines like "Workbook.open (filename:=n)" is not working since n cannot be found...

    It says,, Run-time error '1004'

    However, when I run, I have "Public" all the Dim already (Including that strFile - Public strFile, Public n, ...).
    Do you have any ideas on this? Thanks!!!!!!

    Tuesday, July 3, 2012 5:00 AM
  • When you want a Global variable (available to all subs in all modules) you dimension the variable as follows at the top of a standard module.

    Public strFile As String

    When you have done that, do NOT diemension it anywhere else in your project either as Public or Dim. If you use the Dim statement in another sub then when you run that sub, the value of the variable that has been set in another sub is destroyed.

    In the VBA editor you should select menu item Edit -> Find and enter "Dim strFile" (without the quotes) to search. Set the Option to "Search Current Project" and then search for any instances of Dim strFile and then remove them.

    If you only want a variable to be available to all subs in a specific module, but not available to other modules, then you dimension the variable as follows at the top of the required module.

    Dim strFile As String

    Then in the VBA editor you should select menu item Edit -> Find and enter "Dim strFile" (without the quotes) to search. This time set the Option to "Current Module" and then search for any other instances of Dim strFile and then remove them.


    Regards, OssieMac

    • Marked as answer by Jackynck Tuesday, July 3, 2012 6:31 AM
    Tuesday, July 3, 2012 6:13 AM
  • Hi OssieMac,

    Thats awesome! Thanks! Its working now!

    Tuesday, July 3, 2012 6:32 AM
  • Another way is to pass the inputbox return value to the pivot sub. For this you have to add a parameter to the Sub:

    Sub Pivot(strFile as String)
    ..
    End Sub

    and call it with the parameter

    Call Pivot(strFileName)

    HTH
    Henry
     On 03.07.2012 11:32, OssieMac wrote:

    You create the variable for the inputbox return as a Global variable
    and it can be used throughout the project. To do this:-

    At the top of a Standard Module before any subs insert the following
    line:-

    Public strFile
    The above is done in lieu of the standard method of declaring variables
    within a sub where the variable is only available in that sub.

    Added with edit after original post:

    (*Must be in a Standard Module to be available throughout all modules.
    If only required in one module then use Dim strFile at the top of the
    required module and it will be available throughout that module but not
    in other modules*.)

    Tuesday, July 3, 2012 10:23 AM