Go Back   Access World Forums > Microsoft Access Reference > Code Repository

 
Closed Thread
 
Thread Tools Rate Thread Display Modes
Old 11-09-2017, 06:10 AM   #1
George Moore
Access 2002,2010 & 2016
 
Join Date: Aug 2013
Location: Wales UK
Posts: 44
Thanks: 9
Thanked 8 Times in 6 Posts
George Moore is on a distinguished road
Get Stats About VBA Project

Hi

This is something I wrote for my own curiosity because I wanted to know how many lines of code were in a project but it may be useful to other developers.

It is a fairly simple sub which seqentially examines all tables, forms, reports and modules in a project and produces stats about number of fields, controls, VBA functions, lines of code etc.

It is only intended to be used on design masters not ACCDE files.

As all forms are opened, scrutinised and closed, you will need to close & re-open the database or run an autoexec macro to return to a dashboard when it has completed.

Code:
Sub ProduceStats()
Rem*******************************************
Rem 2017.04.04.04 Set Up
Rem*******************************************
    
    Dim s1 As String
    Dim v1 As Variant
    
    On Error GoTo oops
    
Rem*******************************************
Rem 2017.04.04.04 Table Stats
Rem*******************************************
    
    Dim NoOfTables As Long, NoOfFields As Long, NoOfRecords As Long

    For Each v1 In CurrentDb.TableDefs
            
        NoOfTables = NoOfTables + 1
        
        NoOfFields = NoOfFields + v1.Fields.Count
        
        NoOfRecords = NoOfRecords + DCount("*", v1.Name)
    
    Next v1
        
Rem*******************************************
Rem 2017.04.04.04 Form Stats
Rem*******************************************
    
    Dim NoOfForms As Long, NoOfControls As Long, NoOfModules As Long, NoOfFunctions As Long, CodeLines As Long, LineNumber As Long

    For v1 = 0 To CurrentDb.Containers("Forms").Documents.Count - 1
    
        s1 = CurrentDb.Containers("Forms").Documents(v1).Name
        
        NoOfForms = NoOfForms + 1
    
        DoCmd.OpenForm s1, acDesign, , , , acHidden
      
        NoOfControls = NoOfControls + Forms(s1).Controls.Count
        
        If Forms(s1).HasModule = True Then
        
            NoOfModules = NoOfModules + 1
                    
            CodeLines = CodeLines + Forms(s1).Module.CountOfLines
            
            For LineNumber = 1 To Forms(s1).Module.CountOfLines
            
                If Trim(UCase(Forms(s1).Module.Lines(LineNumber, 1))) Like "FUNCTION *" _
                Or Trim(UCase(Forms(s1).Module.Lines(LineNumber, 1))) Like "SUB *" _
                Or Trim(UCase(Forms(s1).Module.Lines(LineNumber, 1))) Like "PRIVATE FUNCTION *" _
                Or Trim(UCase(Forms(s1).Module.Lines(LineNumber, 1))) Like "PRIVATE SUB * " Then
    
                    NoOfFunctions = NoOfFunctions + 1
                    
                End If
        
            Next LineNumber
        
        End If
        
        DoCmd.Close acForm, s1, acSaveYes
                
    Next v1
        
Rem*******************************************
Rem 2017.04.04.04 Reports
Rem*******************************************

    Dim NoOfReports As Long
    
    For v1 = 0 To CurrentDb.Containers("Reports").Documents.Count - 1
    
        s1 = CurrentDb.Containers("Reports").Documents(v1).Name
            
            NoOfReports = NoOfReports + 1
        
            DoCmd.OpenReport s1, acDesign, , , , acHidden
          
            NoOfControls = NoOfControls + Reports(s1).Controls.Count
            
            If Reports(s1).HasModule = True Then
                        
                CodeLines = CodeLines + Reports(s1).Module.CountOfLines
                
                For LineNumber = 1 To Reports(s1).Module.CountOfLines
                
                    If Trim(UCase(Reports(s1).Module.Lines(LineNumber, 1))) Like "FUNCTION *" _
                    Or Trim(UCase(Reports(s1).Module.Lines(LineNumber, 1))) Like "SUB *" _
                    Or Trim(UCase(Reports(s1).Module.Lines(LineNumber, 1))) Like "PRIVATE FUNCTION *" _
                    Or Trim(UCase(Reports(s1).Module.Lines(LineNumber, 1))) Like "PRIVATE SUB * " Then
        
                        NoOfFunctions = NoOfFunctions + 1
                        
                    End If
            
                Next LineNumber
            
            End If
            
            DoCmd.Close acReport, s1, acSaveYes
                
    Next v1
        
Rem*******************************************
Rem 2017.04.04.04 Modules
Rem*******************************************
    
    For v1 = 0 To CurrentProject.AllModules.Count - 1
    
        s1 = CurrentProject.AllModules(v1).Name
        
        DoCmd.OpenModule s1
        
        CodeLines = CodeLines + Modules(s1).CountOfLines
        
        For LineNumber = 1 To Modules(s1).CountOfLines
        
            If Trim(UCase(Modules(s1).Lines(LineNumber, 1))) Like "FUNCTION *" _
            Or Trim(UCase(Modules(s1).Lines(LineNumber, 1))) Like "SUB * " Then
        
                NoOfFunctions = NoOfFunctions + 1
            
            End If
            
        Next LineNumber
        
        On Error Resume Next
        
        DoCmd.Close acModule, s1, acSaveYes
        
        On Error GoTo oops
        
    Next v1

Rem*******************************************
Rem 2017.04.04.04 Compile Message
Rem*******************************************

    s1 = "Tables : = " & NoOfTables & vbNewLine
    s1 = s1 & "Fields : = " & NoOfFields & vbNewLine
    s1 = s1 & "Records : = " & NoOfRecords & vbNewLine
    s1 = s1 & "Forms : = " & NoOfForms & vbNewLine
    s1 = s1 & "Reports : = " & NoOfReports & vbNewLine
    s1 = s1 & "Controls : = " & NoOfControls & vbNewLine
    s1 = s1 & "Functions : = " & NoOfFunctions & vbNewLine
    s1 = s1 & "Lines Of Code := " & CodeLines
    
    MsgBox s1
        
Rem*******************************************
Rem 2017.04.04.04 Compile Message
Rem*******************************************
                
    Exit Sub
    
oops:
 
    MsgBox Error$

End Sub

George Moore is offline  
Old 11-09-2017, 06:14 PM   #2
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,336
Thanks: 115
Thanked 3,103 Times in 2,821 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Get Stats About VBA Project

The function created by George worked well for me on a small database with no backend tables taking less than 5 seconds to complete

However, when tested on a very large split database, it was terribly slow taking over 18 minutes to complete. See detailed comments on this thread:
https://www.access-programmers.co.uk...d.php?t=296853

I decided to make my own version which has additional information and more importantly ran far faster on my test databases. Times for the above databases were reduced to about 1 second and 1 minute respectively

The output is sent to the immediate window :



It is also displayed as a message box:



The message box layout isn't as neat as I'd like. However, I've spent too long on this today to do any more.

At some point in the future, I may add provision to save the data to a log text file as a further option

You will need to copy all the following items to your own databases.
- Table - tblSysObjectTypes
- Queries - qryDatabaseObjects, qryDatabaseObjectCount & (optionally) qryDatabaseObjectSummary
- Modules modDatabaseStatistics & modVBECode

You will also need to add the VBA reference: Microsoft Visual Basic for Applications Extensibility 5.3
Attached Images
File Type: jpg DatabaseStats1.jpg (81.8 KB, 1031 views)
File Type: png DatabaseStat2.PNG (16.2 KB, 904 views)
Attached Files
File Type: accdb DatabaseStats.accdb (672.0 KB, 215 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)

Last edited by isladogs; 11-10-2017 at 12:53 AM.
isladogs is offline  
The Following 2 Users Say Thank You to isladogs For This Useful Post:
angeljac (01-08-2018), George Moore (11-09-2017)
Old 01-26-2018, 02:05 AM   #3
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,336
Thanks: 115
Thanked 3,103 Times in 2,821 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Get Stats About VBA Project

Belatedly discovered that if you have the MZ Tools add in, it contains its own statistics feature which is very fast.



The output can be exported to a text file or Excel.
Attached Images
File Type: png CaptureMZ.PNG (26.0 KB, 386 views)

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline  
Closed Thread

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
What stats are useful? Jon Site Suggestions 2 11-22-2010 03:40 AM
Headlines and Stats dan-cat Politics & Current Events 2 04-27-2009 02:44 AM
stats niki Reports 0 07-07-2003 05:37 AM
Best way to query stats? Antman Queries 1 04-27-2003 09:24 AM
Important help plz... Stats border20 General 0 01-13-2003 05:59 AM




All times are GMT -8. The time now is 09:09 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World