How to fix compile error "type mismatch"? 1

[feipezi]

Hi,

I have a macro (from Pearson Software Consulting) that can count the number of rows of a Module. But as I run it, got something like 'compile error: type mismatch'. Trying to get around but failed. Here is the code:
Thanks in advance.


Public Function TotalCodeLinesInVBComponent(VBComp As VBIDE.VBComponent) As Long
Dim N As Long
Dim S As String
Dim LineCount As Long

If VBComp.Collection.Parent.Protection = vbext_pp_locked Then
TotalCodeLinesInVBComponent = -1
Exit Function
End If

With VBComp.CodeModule
For N = 1 To .CountOfLines
S = .Lines(N, 1)
If Trim(S) = vbNullString Then
' blank line, skip it
ElseIf Left(Trim(S), 1) = "'" Then
' comment line, skip it
Else
LineCount = LineCount + 1
End If
Next N
End With
TotalCodeLinesInVBComponent = LineCount
End Function

Sub tntt()
x = TotalCodeLinesInVBComponent(Module1)
MsgBox x
End Sub

 

[Andrzejek]

count the number of rows of"... what?
Are you counting the numbers of rows of code in a Module?

Anyway,
Which line of code generates the error?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[combo]

What type is Module1 in tntt?

combo

 

[MajP]

the signature of the function is

Public Function TotalCodeLinesInVBComponent(VBComp As VBIDE.VBComponent) As Long

Where do you create the public variable Module1 as a vbide.vbcomponent? My guess is nowhere.

 

[feipezi]

Thanks folks for the quick return.

1) yes, count the number of rows of the code; the error will be spotted at Sub tntt; if I do "Module1" then get "type mismatch..."; if Module1 then get "ByRef argument type mismatch"; of course "Module1" is a string and Module1 a variant. Then what should I do?

2) Module1 is VBComp.CodeModule; not an Object. It's a parameter of the function. Do I need to create a variable to serve as a parameter of the function?

Thanks again.

 

[MajP]

Typing a name does not get you a vbcodemodule object. Something like this.

Public Sub TestCount()
  Dim vbMod As VBIDE.CodeModule
  Set vbMod = GetModuleFromName("Module1")
  MsgBox vbMod.Name & " " & vbMod.CountOfLines
End Sub

Public Function GetModuleFromName(ModuleName As String) As VBIDE.CodeModule
  Dim vbEditor As VBIDE.VBE
  Dim vbProj As VBIDE.VBProject

  Set vbEditor = Application.VBE
  Set vbProj = vbEditor.ActiveVBProject
  Set GetModuleFromName = vbProj.VBComponents(ModuleName).CodeModule
End Function

Also in trust settings you have to "allow access to vbaproject

 

[Andrzejek]

Hmmm... MZTools gives you in one click some Statistics like: File, File Type, Code Lines, Comment Lines, Total Lines, Procedures, Controls

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[feipezi]

Thanks MajP. Your way works. But what about the code that I attached: like Sub tntt(). Are you saying there is no way to make it work?

Thanks again.

 

[PHV]

Try this:
x = TotalCodeLinesInVBComponent(Application.VBE.ActiveVBProject.VBComponents("Module1"))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886