Ah, I see...

Debug.Print "This workbook has " & vbpCt & " modules."

Yes, that was a mistake in my code... should be intCt

Still, the problem I had was instantiating newmod. You say that code
instantiated newmod for you?

I have that code in a standard module named "Module1". I tried putting the
code in "ThisWorkbook" as well as "Sheet1" but then the code would not run
(from the immediate window using ?AddModuleToExcel).

I tried the below revised code can got this:

This workbook has 5 modules.
Error Number 440: Method 'Add' of object '_VBComponents' failed
Error Number 91: Object variable or With block variable not set
This workbook has 5 modules.

What I'm expecting to see is this:

This workbook has 5 modules.
This workbook has 6 modules.

And then, of course, see an additional module in the project from the IDE.
The next step would be finding a way to get the actual code in the module,
and also getting the code to run via automation (as you mentioned, this
should simply be a matter of creating an object to hold reference to the
Excel application object and using something like xlapp.vbp = Active
Workbook.VBProject).

Public Function AddModuleToExcel()
On Error GoTo HandleErr
Dim intCt As Integer
Dim vbp As Object
Dim newmod As Object
If Val(Application.Version) = 10 Then
Set vbp = ActiveWorkbook.VBProject
If Err.Number < 0 Then
MsgBox "Your security settings do not allow this procedure to
run." _
& vbCrLf & vbCrLf & "To change your security setting:" _
& vbCrLf & vbCrLf & " 1. Select Tools - Macro - Security." &
vbCrLf _
& " 2. Click the 'Trusted Sources' tab" & vbCrLf _
& " 3. Place a checkmark next to 'Trust access to Visual Basic
Project.'", _
vbCritical
Exit Function
Else
intCt = vbp.VBComponents.Count
Debug.Print "This workbook has " & intCt & " modules."
Set newmod = vbp.VBComponents.Add(vbext_ct_StdModule)
'Set newmod =
ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_S tdModule)
newmod.Name = "MyNewModule"
intCt = vbp.VBComponents.Count
Debug.Print "This workbook has " & intCt & " modules."
End If
End If
Exit_He
Exit Function
HandleErr:
Select Case Err.Number
Case Else
Debug.Print "Error Number " & Err.Number & ": "; Err.Description
Resume Next
End Select
End Function