Use VBA to identify MsgBox code which is missing optional strings?

[templeowls]

I have a very large DB with a lot of msgbox's throughout. It sounds nitpicky, but I'd like them all to have a specific box style (vbExclamation, etc.) and title.

The problem is that I don't know how to locate the ones which are missing them. Outside of walking through every line of code, I just edit them when I come across them.



Is there a way via VBA to identify any msgbox's that are missing those elements?

[June7]

Not that I know of. I suggest you dedicate time to review every MsgBox in one sitting. Get this over with and stop obsessing on it. Use Find dialog for "MsgBox".

[isladogs]

Actually, yes you could loop through each instance of MsgBox and add those items where they are missing or different

But I also wouldn't waste time on doing so. Don't forget that MsgBox arguments can also be numbers where vbExclamation = 48
But if combined with e.g. vbOKCancel (1) you would have the value 49 etc, etc
For further info, see https://isladogs.co.uk/msgbox-constants/

As you revise an individual section of code, I suggest you alter the MsgBox code where necessary

[orange]

Here is a quick and dirty effort to find the string Msgbox in your procedure code in your database.

It doesn't differentiate the MsgBox parameters, but does display the module, proc names and the line containing "MsgBox". It may help quantify the effort involved. **Limited testing**

' ----------------------------------------------------------------
' Procedure Name: FindInCode
' Purpose: Routine to search all modules and procs in current database for a named string
' Procedure Kind: Sub
' Procedure Access: Public
' Parameter strFind (String): String to find  Defaults to Msgbox
' Author: Jack
' Date: 03-Mar-23
'
' Sample usage   In immediate window  type  FindInCode and press enter
' ----------------------------------------------------------------
Public Sub FindInCode(Optional strFind As String = "Msgbox")

10        On Error GoTo FindInCode_Error
          Dim Component As Object
          Dim Index As Long
          Dim cmtLines As Integer
          Dim Name As String
          Dim Kind As Long
          Dim Start As Long
          Dim Body As Long
          Dim Length As Long
          Dim BodyLines As Long
          Dim Declaration As String
          Dim ProcedureType As String
          
20        For Each Component In Application.VBE.ActiveVBProject.VBComponents

30            With Component.CodeModule
                 
40                For Index = .CountOfDeclarationLines + 1 To .CountOfLines
50                    If .Lines(Index, 1) Like "*" & strFind & "*" Then
60                        Debug.Print Component.Name & String(50 - Len(Component.Name), " ") _
                              & .ProcOfLine(Index, Kind) & String(50 - Len(.ProcOfLine(Index, Kind)), " ") & .Lines(Index, 1)
70                    End If
80                Next Index
                 
90            End With
              
100       Next Component

          
110       On Error GoTo 0
FindInCode_Exit:
120       Exit Sub

FindInCode_Error:

130       MsgBox "Error " & Err.Number & " (" & Err.Description & "), line " & Erl & " in Procedure FindInCode" _
              & "  Module  Mod_ModuleProcInfo "
140       GoTo FindInCode_Exit
End Sub

Sample:

findincode ("Feb-23")
AWFRelated countVowels ' Date: 01-Feb-23
AWFRelated textVowelCounter ' Date: 01-Feb-23
AWFRelated testqdefasRecSet ' Date: 14-Feb-23
AWFRelated findskill ' Date: 18-Feb-23
DataDictionary IdentifyRequiredFields ' Date: 16-Feb-23
ZZ_ScratchPad TestUA ' Date: 22-Feb-23