VBA Extensibility problem

R

R Avery

I have the following problem:

For some reason, seemingly at its whim, VBA inserts an extra line
containing only "()" after my code when i add code to a new module using

..CodeModule.AddFromString strCode

This only happens about 1/15 of the time, but it does so consistently.
I have checked the strCode, and there NEVER appears parentheses like
that anywhere. I also checked using debug.print strcode. But VBA
inserts the additional "()" line.

Any suggestions? Any help would be most appreciated.
 
B

Bob Phillips

Can you show us an example where the () is added.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
R

R Avery

Actually, it seems to occur consistently when I declare this API
function. It has occurred elsewhere, and I will try to isolate what is
causing the extra "()". You can reproduce my problem on Excel 2002 with
the following code (assuming your project has Module1 with this code,
and blank Module2 and blank Module3).


Private Declare Function ShellExecute Lib "shell32" _
Alias "ShellExecuteA" _
(ByVal hwnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long



Private Sub CopyThisModuleCodeToOthers()
Dim strCode As String
Dim i As Long

With ThisWorkbook.VBProject.VBComponents
With .Item("Module1").CodeModule
strCode = .Lines(1, .CountOfLines)
End With

For i = 2 To 3
With .Item("Module" & i).CodeModule
.AddFromString strCode
End With
Next i
End With
End Sub
 
R

R Avery

Also, Registry API functions have had the same effect as the
ShellExecute function, in terms of inserting the "()" at the end of the
code.
 
B

Bob Phillips

I have discovered something interesting which suggest a bug in the facility.

Firstly, if you break the code before the 'AddFromString', strCode does not
have that ().

Also, if you move all of the first three lines of the API declaration to one
line, the problem goes away.

This suggests to me that the AddFromString sees the API declaration and is
looking for the () but doesn't see it, so adds it at the end.. If so, a
definite bug.

It is easily circumvented, but I like the layout that you show, and don't
like to have to lay it out to accommodate VB.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
R

R Avery

The way I have dealt with it is to just iterate through every line of
the copied code if it contains any API declarations, looking for a line
with just "()" and delete any it finds.

Thanks for your help!
 
Top