+ Reply to Thread
Results 1 to 3 of 3

Using a worksheet parameter to fix an Out of Stack Space error

Hybrid View

  1. #1
    Registered User
    Join Date
    09-11-2017
    Location
    USA
    MS-Off Ver
    Excel for Mac 2016
    Posts
    10

    Using a worksheet parameter to fix an Out of Stack Space error

    In my workbook I have a set of status boxes with code that allows users to check them based on the current status of that tab. The code is the same, so recently I pulled it into its own module. This code is called on every page of the workbook. Recently, I've been getting "Out of Stack Space" errors relating to that code call, and sometimes a "With block variable not set" error that highlights one of the variable setting lines in the status bars module itself. Here's a condensed version of the module code, along with an example of the module call:

    Status bars module:
    Sub StatusBars(ByVal Target As Range)
    
    Dim TabStarted1 As Range
    Set TabStarted1 = ActiveSheet.Range("A4:Z5").Find("Tab Started")
    Dim TabStarted As Range
    Set TabStarted = TabStarted1.Offset(0, 1) 
    
    
    [two more examples of the same]
    
    If Not Intersect(Target, TabStarted) Is Nothing Then
        If Target.Cells.Count = 2 Then
            If WorksheetFunction.CountA(Target) = 0 Then
    
                TabStarted.Value = "X"
                TabStarted.HorizontalAlignment = xlCenter
                TabStarted.Font.Size = 25
                TabStarted.Interior.Color = RGB(255, 255, 0)
                Design.Interior.Color = RGB(255, 255, 255)
                Design.Value = ""
                Configurations.Interior.Color = RGB(255, 255, 255)
                Configurations.Value = ""
                ActiveSheet.Tab.Color = RGB(255, 255, 0)
            
            Else
                TabStarted.Interior.Color = RGB(255, 255, 255)
                TabStarted.Value = ""
                ActiveSheet.Tab.ColorIndex = xlColorIndexNone
           End If
        End If
    
    End If
    
    [etc]
    
    End Sub
    Here is an example of a simple page calling that module:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Application.ScreenUpdating = False
    
    Call StatusBars(Target)
    
    Application.ScreenUpdating = True
    End Sub
    It was suggested to me elsewhere that I could use Application.EnableEvents = False to resolve this. And while I haven't seen Out of Stack Space as often, I still get the "With block variable not set" error. Worse still, if I get an error midway through the code on any sheet, EnableEvents hasn't yet been turned back on, so now I can't do anything in Excel.

    The other suggestion was that because ActiveSheet is a global variable, it stays on the stack, therefore I should try to make ActiveSheet a parameter of my function.

    The Question
    The problem is, I'm not entirely sure how to do this. While I know I can declare my Sub as: Sub StatusBars(ByVal Target As Range, ActiveSheet As Worksheet) and call it by "Call StatusBars(Target, ActiveSheet), I don't know what I need to do to these lines to make them work correctly and not stay on the stack:

    Dim TabStarted1 As Range
    Set TabStarted1 = ActiveSheet.Range("A4:Z5").Find("Tab Started") 
    Dim TabStarted As Range
    Set TabStarted = TabStarted1.Offset(0, 1)
    Does anyone have any suggestions?

  2. #2
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MSO Home and Business 2024
    Posts
    7,335

    Re: Using a worksheet parameter to fix an Out of Stack Space error

    First of all I'd stop using reserved words as variables such as Activesheet or Target. Call them mysheet and myrange or something like that.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  3. #3
    Registered User
    Join Date
    09-11-2017
    Location
    USA
    MS-Off Ver
    Excel for Mac 2016
    Posts
    10

    Re: Using a worksheet parameter to fix an Out of Stack Space error

    I'm not using Activesheet as a variable currently. Because the module will be called by all sheets in the workbook, I am using ActiveSheet so it knows which range to search over. I also use ActiveWorkbook a lot because I may have several versions of the same workbook open for different clients, each of which will have identically named Sheets of course.

    What I am asking, since it was suggested elsewhere, is how I would pass it to the module as a parameter so it doesn't get stuck on the stack.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Error 28 Stack Space
    By scrabtree23 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-27-2015, 09:58 AM
  2. VBA Out of Stack Space Error 28
    By alexcapewell in forum Excel General
    Replies: 9
    Last Post: 10-13-2015, 07:01 AM
  3. Out of stack space error
    By lazyengineer in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-10-2015, 02:24 PM
  4. [SOLVED] Run TIme Error 28: Out of Stack Space
    By smciesl2 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-22-2014, 01:21 PM
  5. Run Time Error 28: Out of Stack Space
    By Shanahan0 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-10-2013, 10:42 AM
  6. Out of stack space error
    By luv2glyd in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 07-20-2008, 03:47 PM
  7. [SOLVED] 'Out of stack space' error on SaveAs
    By Tim in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-22-2005, 01:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1