Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Jun 2010
    Lichfield, UK
    Thanked 0 Times in 0 Posts

    Error Message: Too Many Local, Non Static Variables

    Apologies for the long post but I feel I have to provide some context in order for anyone to be able to help me.

    I have an app (Access 2007/VBA on XP SP3) which I've been using and enhancing for a number of years. I'm in the middle of a major re-engineering job in an attempt to make it more object based. However I am now getting the message "Too Many Local, Non Static Variables" when trying to compile it.

    First of all a bit of background. The app stores cricket scorecards. I have created an object (clsImportFile) which will import a scorecard from an Excel spreadsheet, validate all the data and hold it in a format to enable it to be added into the database. The nature of a cricket scorecard is such that there is a hierarchy of information:

    A Match has 4 Innings
    Within a Match An innings has: A Total, Number of wickets fallen, Extras scored, 11 Batsmen, Up to 11 Bowlers, Up to 10 Fall of Wickets etc
    Within an Innings a Batsman has: An ID, a Name (redundant but needed for display), How the Batsman was out, the Bowler who got him out, # Runs Scored etc
    Similar for Bowlers and Fall of Wickets

    So I thought the easiest way of managing this is to define a series of User Defined Types and expose these as Properties of clsImportFile which can then be used by other forms / objects. The UDTs are defined in a global module. So far so good.

    I have now rewritten a form which I use to manage the import of individual scorecards. Before confirming that it is to be loaded into the Db the data needs to be displayed in a Scorecard form awaiting user confirmation. The main control on this form is a tab control with 4 tabs, one for each innings.

    I am getting the error message (at compile time) in the function I use to populate the controls in this tab control. Essentially the form looks something like the attached screen dump. The 2 main lists in the tab control are ListBoxes.

    I have included the UDT definitions and the whole of the function in which the error occurs below. You will notice a lot of commented out code. By building the row source string in the clsImportFile object as opposed to the form (and creating extra elements of the UDTs [called BattingString, BowlingString etc] to do so) I was able to push the line that triggered the error further back until having done it for the 4 major displays (Batting, Bowling, FoW and Extras) I can now get it to compile without error. However I am not happy with this solution as it is transferring the decision about how the form is to display the data from the form into the object which of course will also be used elsewhere - not good design IMO.

    Microsoft's help says the error is caused by too many non static variables being declared in the procedure (presumably the procedure that triggers the error). As you can see from the code below there are hardly any variables declared in the procedure and I use Option Explicit in all objects. (The instance of clsImportFile called cImport is declared at form level).

    Can anyone throw any light on this? Is what I am trying to do the wrong way of doing it? Am I stretching the use of UDTs to breaking point? Is there a better way?

    Thanks in advance.

    Declaration of UDTs

    Public Type InningsBatting
        BatsmanID As Long
        BatsmanNamePrint As String
        HowOutID As Long
        HowOutPrint As String
        AssisterID As Long
        AssisterNamePrint As String
        BowlerID As Long
        BowlerNamePrint As String
        RunsScored As Long
        InningsStats As Byte
        BallsFaced As Long
        Minutes As Long
        Fours As Long
        Sixes As Long
        BattingString As String
    End Type
    Public Type InningsBowling
        BowlerID As Long
        BowlerPrintName As String
        BallsBowled As Long
        Maidens As Long
        Runs As Long
        Wickets As Long
        NoBalls As Long
        Wides As Long
        BowlingString As String
    End Type
    Public Type InningsFoWPartner
        PartnerSeq As Integer
        PartnerID As Long
        PartnerKnownAs As String
        PartnerOut As Boolean
    End Type
    Public Type InningsFoW
        Wicket As Long
        Score As Long
        Partnership As Long
        Ended As Boolean
        PartnerCount As Integer
        PartnerOut As Integer
        Partner(5) As InningsFoWPartner
    End Type
    Public Type MatchInnings
        Sequence As Integer
        IName As String
        BattingID As Long
        BattingName As String
        BattingMnemonic As String
        BowlingID As Long
        BowlingName As String
        BowlingMnemonic As String
        Total As Long
        Wickets As Long
        Closure As String
        Extras As Long
        ExtrasString As String
        ExByes As Long
        ExWides As Long
        ExNoBalls As Long
        ExLegByes As Long
        ExPenalty As Long
        NotUsed As Boolean
        WKID As Long
        FoWString As String
        BattingDetails(11) As InningsBatting
        BowlingDetails(11) As InningsBowling
        FoWDetails(10) As InningsFoW
    End Type
    Public Type MatchResult
        ResultType As String
        ByRuns As Long
        ByWickets As String
        ByInnings As Boolean
        Forfeit As Boolean
        DuckLewis As Boolean
        WinnerID As Long
        LoserID As Long
    End Type
    And now the function where the compile error occurs:

    Private Function DisplayInnings(iInput As Integer) As Integer
                                                                    ' Function to display an Innings. The tab to display on and so the controls to
                                                                    '  populate are dependent on the Innings passed as iInput
        On Error GoTo Err_DisplayInnings
        Dim iLines As Integer
        Dim iLoop As Integer
        Dim iPartnerOut As Integer                                  ' Offset of Partner Out
        Dim iTemp As Integer
        Dim lngTemp As Integer
        Dim strBat As String                                        ' Row Source for Batting ListBox
        Dim strBowl As String                                       ' Row Source for Bowling ListBox
        Dim strExtras As String                                     ' Full string showing all Extras
        Dim strFoW As String                                        ' Full string showing all FoWs and Partners Out for the innings
        Select Case iInput
            Case 1
            Case 2
            Case 3
            Case 4
        End Select
        With cImport
            strBat = ""
            For iLoop = 1 To 11
    '            strBat = strBat & CStr(iLoop) & ";" & _
    '                .MatchInnings(iInput).BattingDetails(iLoop).BatsmanNamePrint & ";" & _
    '                .MatchInnings(iInput).BattingDetails(iLoop).HowOutPrint & ";" & _
    '                .MatchInnings(iInput).BattingDetails(iLoop).BowlerNamePrint & ";" & _
    '                RightJustify(.MatchInnings(iInput).BattingDetails(iLoop).RunsScored, 6) & ";" & _
    '                "0;0;0;0;"
                strBat = strBat & .MatchInnings(iInput).BattingDetails(iLoop).BattingString
            Next iLoop
            strExtras = "(" & .MatchInnings(iInput).ExtrasString & ")"
            lngTemp = .MatchInnings(iInput).Extras
            iLines = 1
    '        strFoW = ""
    '        If .MatchInnings(iInput).Wickets > 0 Then
    '            For iLoop = 1 To .MatchInnings(iInput).Wickets'
    '                iPartnerOut = .MatchInnings(iInput).FoWDetails(iLoop).PartnerOut
    '                If .MatchInnings(iInput).FoWDetails(iLoop).Ended = True Then
    '                    strFoW = strFoW & CStr(iLoop) & " - " & _
    '                        CStr(.MatchInnings(iInput).FoWDetails(iLoop).Score) & " " & _
    '                        IIf(iPartnerOut > 0, " (" & .MatchInnings(iInput).FoWDetails(iLoop).Partner(iPartnerOut).PartnerKnownAs & ") ", "") & " "''
    '                    iTemp = Len(strFoW)
    '                    If iTemp / iLines > 60 Then
     '                       strFoW = strFoW & vbCrLf
    '                        iLines = iLines + 1
    '                    End If
    '                End If
    '            Next iLoop
    '        End If
            strFoW = .MatchInnings(iInput).FoWString
            strBowl = ""
            For iLoop = 1 To 11
                If .MatchInnings(iInput).BowlingDetails(iLoop).BowlerID > 0 Then
    '                strBowl = strBowl & .MatchInnings(iInput).BowlingDetails(iLoop).BowlerPrintName & ";'" & _
    '                    RightJustify(BallsToOvers(.MatchInnings(iInput).BowlingDetails(iLoop).BallsBowled, .BPO, True), 6) & "';'" & _
    '                    RightJustify(.MatchInnings(iInput).BowlingDetails(iLoop).Maidens, 4) & "';'" & _
    '                    RightJustify(.MatchInnings(iInput).BowlingDetails(iLoop).Runs, 4) & "';'" & _
    '                    RightJustify(.MatchInnings(iInput).BowlingDetails(iLoop).Wickets, 4) & "';'" & _
    '                    RightJustify(.MatchInnings(iInput).BowlingDetails(iLoop).NoBalls, 4) & "';'" & _
    '                    RightJustify(.MatchInnings(iInput).BowlingDetails(iLoop).Wides, 4) & "';"
                    strBowl = strBowl & .MatchInnings(iInput).BowlingDetails(iLoop).BowlingString
                End If
            Next iLoop
            Select Case iInput
                Case 1
                    lstInn1.RowSource = strBat
                    lstInn1.Visible = True
                    lblExt1.Visible = True
                    txtExt1Det = strExtras
                    txtExt1Det.Visible = True
                    txtExt1 = .MatchInnings(iInput).Extras
                    txtExt1.Visible = True
                    txtClo1 = ClosureText(.MatchInnings(iInput).Wickets, .MatchInnings(iInput).Closure)
                    txtClo1.Visible = True
                    lblTot1.Visible = True
                    txtTot1 = .MatchInnings(iInput).Total
                    txtTot1.Visible = True
                    lblFoW1.Visible = True
                    txtFoW1 = strFoW
                    txtFoW1.Visible = True
                    lblBow1.Visible = True
                    lstBow1.RowSource = strBowl
                    lstBow1.Visible = True
                Case 2
                    lstInn2.RowSource = strBat
                    lstInn2.Visible = True
                    lblExt2.Visible = True
                    txtExt2Det = strExtras
                    txtExt2Det.Visible = True
                    txtExt2 = lngTemp
                    txtExt2.Visible = True
                    txtClo2 = ClosureText(lngInnWickets(iInput), strInnClosure(iInput))
                    txtClo2.Visible = True
                    lblTot2.Visible = True
                    txtTot2 = lngInnRunsScored(iInput)
                    txtTot2.Visible = True
                    lblFoW2.Visible = True
                    txtFoW2 = strFoW
                    txtFoW2.Visible = True
                    lblBow2.Visible = True
                    lstBow2.RowSource = strBowl
                    lstBow2.Visible = True
                Case 3
                    lstInn3.RowSource = strBat
                    lstInn3.Visible = True
                    lblExt3.Visible = True
                    txtExt3Det = strExtras
                    txtExt3Det.Visible = True
                    txtExt3 = lngTemp
                    txtExt3.Visible = True
                    txtClo3 = ClosureText(lngInnWickets(iInput), strInnClosure(iInput))
                    txtClo3.Visible = True
                    lblTot3.Visible = True
                    txtTot3 = lngInnRunsScored(iInput)
                    txtTot3.Visible = True
                    lblFoW3.Visible = True
                    txtFoW3 = strFoW
                    txtFoW3.Visible = True
                    lblBow3.Visible = True
                    lstBow3.RowSource = strBowl
                    lstBow3.Visible = True
                Case 4
                    lstInn4.RowSource = strBat
                    lstInn4.Visible = True
                    lblExt4.Visible = True
                    txtExt4Det = strExtras
                    txtExt4Det.Visible = True
                    txtExt4 = lngTemp
                    txtExt4.Visible = True
                    txtClo4 = ClosureText(lngInnWickets(iInput), strInnClosure(iInput))
                    txtClo4.Visible = True
                    lblTot4.Visible = True
                    txtTot4 = lngInnRunsScored(iInput)
                    txtTot4.Visible = True
                    lblFoW4.Visible = True
                    txtFoW4 = strFoW
                    txtFoW4.Visible = True
                    lblBow4.Visible = True
                    lstBow4.RowSource = strBowl
                    lstBow4.Visible = True
                Case Else
            End Select
        End With
        Exit Function
        i1 = MsgBox("An error has occurred in Form_frmLoadData-01-PreviewFile Function DisplayInnings()." & vbCrLf & vbCrLf & Err.Number & " - " & Err.Description, _
            vbOKOnly, strAPPLICATION)
        Resume Exit_DisplayInnings
    End Function
    Attached Images Attached Images

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Burwash, East Sussex, United Kingdom
    Thanked 79 Times in 75 Posts
    My first thought would be to convert your UDTs into classes (even if you have nothing more than public variables in the class) and see if that improves things.
    Microsoft MVP - Excel.

  4. The Following User Says Thank You to rory For This Useful Post:

    njl100 (2013-05-23)

  5. #3
    Star Lounger
    Join Date
    Jun 2010
    Lichfield, UK
    Thanked 0 Times in 0 Posts
    Hi Rory,

    Thanks for the suggestion. I need to get my head around how to implement that but would be interested to know why you think that would work - not doubting it just interested to know :-)

  6. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Burwash, East Sussex, United Kingdom
    Thanked 79 Times in 75 Posts
    Partly because it would separate the UDTs so that you don't have all the variables in one module (though of course you could simply separate them into different modules as they are) and partly because classes seem like a better fit to me here, although frankly I don't understand cricket well enough to be sure!
    Microsoft MVP - Excel.

  7. #5
    Star Lounger
    Join Date
    Jun 2010
    Lichfield, UK
    Thanked 0 Times in 0 Posts
    Thanks for that.
    I'll try separating the UDTs first to see if that fixes it.
    I'll have to wait to convert the UDTs into classes as I would not feel comfortable incorporating that in the next version along with all the other changes I am making. Too much going on at once!! However I will try it once I've finished the current round of changes and report back.

    Thanks for your help.

  8. #6
    Star Lounger
    Join Date
    Jun 2010
    Lichfield, UK
    Thanked 0 Times in 0 Posts
    Separating the UDTs had no effect :-(

    As I said above I'll wait until I've finished the current round of changes before trying to convert UDTs into classes.


Posting Permissions

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