PDA

View Full Version : [SOLVED] Compile error: vba VIA vba



ilyaskazi
06-15-2005, 04:48 AM
following is the code which i want to put dynamically in targetwrkbook from activeworkbook...
But i m receiving compile error msg continuously...


With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
.InsertLines .CountOfLines + 1, _
"Private Sub Workbook_BeforeClose(Cancel As Boolean)" & Chr(13) & _
"Dim lRowIndex As Long" & vbNewLine & _
"Dim lColIndex As Integer" & vbNewLine & _
"' RTA, OTA" & vbNewLine & _
"With Worksheets("Rules")" & vbNewLine & _
"For lRowIndex = 2 To 65535" & vbNewLine & _
"If .Cells(lRowIndex, 9).value = "" Then" & vbNewLine & _
"Exit For" & vbNewLine & _
"Else" & vbNewLine & _
"For lColIndex = 16 To 19" & vbNewLine & _
"If .Cells(lRowIndex, lColIndex).value = "" Then" & vbNewLine & _
".Cells(lRowIndex, lColIndex).value = "0"" & vbNewLine & _
"End If" & vbNewLine & _
"Next lColIndex" & vbNewLine & _
"End If" & vbNewLine & _
"Next lRowIndex" & vbNewLine & _
"End With" & vbNewLine & _
"With Worksheets("Validity")" & vbNewLine & _
"For lRowIndex = 2 To 65535" & vbNewLine & _
"If .Cells(lRowIndex, 17).value = "" Then" & vbNewLine & _
"Exit For" & vbNewLine & _
"Else" & vbNewLine & _
"For lColIndex = 17 To 38" & vbNewLine &_
"If .Cells(lRowIndex, lColIndex).value = "" Then" & vbNewLine & _
"' do nothing" & vbNewLine & _
"Else" & vbNewLine & _
".Cells(lRowIndex, lColIndex).value = "'" & .Cells(lRowIndex, lColIndex).value" & vbNewLine & _
"End If" & vbNewLine & _
"Next lColIndex" & vbNewLine & _
"If .Cells(lRowIndex, 40).value <> "" Then .Cells(lRowIndex, 40).value = "'" & .Cells(lRowIndex, 40).value" & vbNewLine & _
"If .Cells(lRowIndex, 41).value <> "" Then .Cells(lRowIndex, 41).value = "'" & .Cells(lRowIndex, 41).value" & vbNewLine & _
"If .Cells(lRowIndex, 43).value <> "" Then .Cells(lRowIndex, 43).value = "'" & .Cells(lRowIndex, 43).value" & vbNewLine & _
"If .Cells(lRowIndex, 44).value <> "" Then .Cells(lRowIndex, 44).value = "'" & .Cells(lRowIndex, 44).value" & vbNewLine & _
"If .Cells(lRowIndex, 46).value <> "" Then .Cells(lRowIndex, 46).value = "'" & .Cells(lRowIndex, 46).value" & vbNewLine & _
"If .Cells(lRowIndex, 47).value <> "" Then .Cells(lRowIndex, 47).value = "'" & .Cells(lRowIndex, 47).value" & vbNewLine & _
"If .Cells(lRowIndex, 49).value <> "" Then .Cells(lRowIndex, 48).value = "'" & .Cells(lRowIndex, 49).value" & vbNewLine & _
"If .Cells(lRowIndex, 50).value <> "" Then .Cells(lRowIndex, 50).value = "'" & .Cells(lRowIndex, 50).value" & vbNewLine & _
"If .Cells(lRowIndex, 52).value <> "" Then .Cells(lRowIndex, 52).value = "'" & .Cells(lRowIndex, 52).value" & vbNewLine & _
"If .Cells(lRowIndex, 53).value <> "" Then .Cells(lRowIndex, 53).value = "'" & .Cells(lRowIndex, 53).value" & vbNewLine & _
"If .Cells(lRowIndex, 55).value <> "" Then .Cells(lRowIndex, 55).value = "'" & .Cells(lRowIndex, 55).value" & vbNewLine & _
"If .Cells(lRowIndex, 56).value <> "" Then .Cells(lRowIndex, 56).value = "'" & .Cells(lRowIndex, 56).value" & vbNewLine & _
"If .Cells(lRowIndex, 58).value <> "" Then .Cells(lRowIndex, 58).value = "'" & .Cells(lRowIndex, 58).value" & vbNewLine & _
"If .Cells(lRowIndex, 59).value <> "" Then .Cells(lRowIndex, 59).value = "'" & .Cells(lRowIndex, 59).value" & vbNewLine & _
"If .Cells(lRowIndex, 61).value <> "" Then .Cells(lRowIndex, 61).value = "'" & .Cells(lRowIndex, 61).value" & vbNewLine & _
"If .Cells(lRowIndex, 62).value <> "" Then .Cells(lRowIndex, 62).value = "'" & .Cells(lRowIndex, 62).value" & vbNewLine & _
"If .Cells(lRowIndex, 64).value <> "" Then .Cells(lRowIndex, 64).value = "'" & .Cells(lRowIndex, 64).value" & vbNewLine & _
"If .Cells(lRowIndex, 65).value <> "" Then .Cells(lRowIndex, 65).value = "'" & .Cells(lRowIndex, 65).value" & vbNewLine & _
"End If" & vbNewLine &_
"Next lRowIndex" & vbNewLine & _
"End With" & vbNewLine & _
"End Sub"
End With

Bob Phillips
06-15-2005, 05:37 AM
I can get this to work, but I had to tweak the code (the long IF tests gave a compile error)


Option Explicit

Sub insertcode()
Dim sCode As String
Dim sCode1 As String
Dim sCode2 As String
sCode = _
"Private Sub Workbook_BeforeClose(Cancel As Boolean)" & vbNewLine & _
"Dim lRowIndex As Long" & vbNewLine & _
"Dim lColIndex As Integer" & vbNewLine & _
"' RTA, OTA" & vbNewLine & _
" With Worksheets(""Rules"")" & vbNewLine & _
" For lRowIndex = 2 To 65535" & vbNewLine & _
" If .Cells(lRowIndex, 9).value = """" Then" & vbNewLine & _
" Exit For" & vbNewLine & _
" Else" & vbNewLine & _
" For lColIndex = 16 To 19" & vbNewLine & _
" If .Cells(lRowIndex, lColIndex).value = """" Then" & vbNewLine & _
" .Cells(lRowIndex, lColIndex).value = 0" & vbNewLine & _
" End If" & vbNewLine & _
" Next lColIndex" & vbNewLine & _
" End If" & vbNewLine & _
" Next lRowIndex" & vbNewLine & _
" End With" & vbNewLine
sCode1 = _
"" & vbNewLine & _
" With Worksheets(""Validity"")" & vbNewLine & _
" For lRowIndex = 2 To 65535" & vbNewLine & _
" If .Cells(lRowIndex, 17).value = """" Then" & vbNewLine & _
" Exit For" & vbNewLine & _
" Else" & vbNewLine & _
" For lColIndex = 17 To 38" & vbNewLine & _
" If .Cells(lRowIndex, lColIndex).value = """" Then" & vbNewLine & _
" ' do nothing" & vbNewLine & _
" Else" & vbNewLine & _
" .Cells(lRowIndex, lColIndex).value = .Cells(lRowIndex, lColIndex).value" & vbNewLine & _
" End If" & vbNewLine & _
" Next lColIndex" & vbNewLine
sCode2 = _
"" & vbNewLine & _
"If .Cells(lRowIndex, 40).value <> """" Then .Cells(lRowIndex, 40).value = .Cells(lRowIndex, 40).value" & vbNewLine & _
"If .Cells(lRowIndex, 41).value <> """" Then .Cells(lRowIndex, 41).value = .Cells(lRowIndex, 41).value" & vbNewLine & _
"If .Cells(lRowIndex, 43).value <> """" Then .Cells(lRowIndex, 43).value = .Cells(lRowIndex, 43).value" & vbNewLine & _
"If .Cells(lRowIndex, 44).value <> """" Then .Cells(lRowIndex, 44).value = .Cells(lRowIndex, 44).value" & vbNewLine & _
"If .Cells(lRowIndex, 46).value <> """" Then .Cells(lRowIndex, 46).value = .Cells(lRowIndex, 46).value" & vbNewLine & _
"If .Cells(lRowIndex, 47).value <> """" Then .Cells(lRowIndex, 47).value = .Cells(lRowIndex, 47).value" & vbNewLine & _
"If .Cells(lRowIndex, 49).value <> """" Then .Cells(lRowIndex, 48).value = .Cells(lRowIndex, 49).value" & vbNewLine & _
"If .Cells(lRowIndex, 50).value <> """" Then .Cells(lRowIndex, 50).value = .Cells(lRowIndex, 50).value" & vbNewLine & _
"If .Cells(lRowIndex, 52).value <> """" Then .Cells(lRowIndex, 52).value = .Cells(lRowIndex, 52).value" & vbNewLine & _
"If .Cells(lRowIndex, 53).value <> """" Then .Cells(lRowIndex, 53).value = .Cells(lRowIndex, 53).value" & vbNewLine & _
"If .Cells(lRowIndex, 55).value <> """" Then .Cells(lRowIndex, 55).value = .Cells(lRowIndex, 55).value" & vbNewLine & _
"If .Cells(lRowIndex, 56).value <> """" Then .Cells(lRowIndex, 56).value = .Cells(lRowIndex, 56).value" & vbNewLine & _
"If .Cells(lRowIndex, 58).value <> """" Then .Cells(lRowIndex, 58).value = .Cells(lRowIndex, 58).value" & vbNewLine & _
"If .Cells(lRowIndex, 59).value <> """" Then .Cells(lRowIndex, 59).value = .Cells(lRowIndex, 59).value" & vbNewLine & _
"If .Cells(lRowIndex, 61).value <> """" Then .Cells(lRowIndex, 61).value = .Cells(lRowIndex, 61).value" & vbNewLine & _
"If .Cells(lRowIndex, 62).value <> """" Then .Cells(lRowIndex, 62).value = .Cells(lRowIndex, 62).value" & vbNewLine & _
"If .Cells(lRowIndex, 64).value <> """" Then .Cells(lRowIndex, 64).value = .Cells(lRowIndex, 64).value" & vbNewLine & _
"If .Cells(lRowIndex, 65).value <> """" Then .Cells(lRowIndex, 65).value = .Cells(lRowIndex, 65).value" & vbNewLine & _
" End If" & vbNewLine & _
" Next lRowIndex" & vbNewLine & _
" End With" & vbNewLine & _
"End Sub"
With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
.InsertLines .CountOfLines + 1, sCode & sCode1 & sCode2
End With
End Sub

Richie(UK)
06-15-2005, 05:40 AM
Hi,

One problem you may have is that there is a limit to how many line continuations you may have (24 I think). This will be a limiting factor with the large amount of code that you are trying to insert (try smaller strings).

Furthermore, care needs to be taken when using quotes in these circumstances. The following is a much condensed version of your original that demonstrates this point:

Sub Test()
Dim strTest As String
strTest = "Private Sub Workbook_BeforeClose(Cancel As Boolean)" & Chr(13) & _
"Dim lRowIndex As Long" & vbNewLine & _
"Dim lColIndex As Integer" & vbNewLine & _
"' RTA, OTA" & vbNewLine & _
"With Worksheets(" & """Rules""" & ")" & vbNewLine & _
"If .Cells(lRowIndex, 9).value = """" Then" & vbNewLine & _
"Else" & vbNewLine & _
"End If" & vbNewLine & _
"End With" & vbNewLine & _
"End Sub"
With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
.InsertLines .CountOfLines + 1, strTest
End With
End Sub

Anyway, that's a lot of code that you are trying to insert. Are you sure that this is the best approach? Why do you need to do it?

Bob Phillips
06-15-2005, 06:00 AM
Actually, reflecting KeepItCool's point, we should use


Sub insertcode()
Dim sCode As String
Dim sCode1 As String
Dim sCode2 As String
Dim iStartLine As Long
sCode = _
"Dim lRowIndex As Long" & vbNewLine & _
"Dim lColIndex As Integer" & vbNewLine & _
"' RTA, OTA" & vbNewLine & _
" With Worksheets(""Rules"")" & vbNewLine & _
" For lRowIndex = 2 To 65535" & vbNewLine & _
" If .Cells(lRowIndex, 9).value = """" Then" & vbNewLine & _
" Exit For" & vbNewLine & _
" Else" & vbNewLine & _
" For lColIndex = 16 To 19" & vbNewLine & _
" If .Cells(lRowIndex, lColIndex).value = """" Then" & vbNewLine & _
" .Cells(lRowIndex, lColIndex).value = 0" & vbNewLine & _
" End If" & vbNewLine & _
" Next lColIndex" & vbNewLine & _
" End If" & vbNewLine & _
" Next lRowIndex" & vbNewLine & _
" End With" & vbNewLine
sCode1 = _
"" & vbNewLine & _
" With Worksheets(""Validity"")" & vbNewLine & _
" For lRowIndex = 2 To 65535" & vbNewLine & _
" If .Cells(lRowIndex, 17).value = """" Then" & vbNewLine & _
" Exit For" & vbNewLine & _
" Else" & vbNewLine & _
" For lColIndex = 17 To 38" & vbNewLine & _
" If .Cells(lRowIndex, lColIndex).value = """" Then" & vbNewLine & _
" ' do nothing" & vbNewLine & _
" Else" & vbNewLine & _
" .Cells(lRowIndex, lColIndex).value = .Cells(lRowIndex, lColIndex).value" & vbNewLine & _
" End If" & vbNewLine & _
" Next lColIndex" & vbNewLine
sCode2 = _
"" & vbNewLine & _
"If .Cells(lRowIndex, 40).value <> """" Then .Cells(lRowIndex, 40).value = .Cells(lRowIndex, 40).value" & vbNewLine & _
"If .Cells(lRowIndex, 41).value <> """" Then .Cells(lRowIndex, 41).value = .Cells(lRowIndex, 41).value" & vbNewLine & _
"If .Cells(lRowIndex, 43).value <> """" Then .Cells(lRowIndex, 43).value = .Cells(lRowIndex, 43).value" & vbNewLine & _
"If .Cells(lRowIndex, 44).value <> """" Then .Cells(lRowIndex, 44).value = .Cells(lRowIndex, 44).value" & vbNewLine & _
"If .Cells(lRowIndex, 46).value <> """" Then .Cells(lRowIndex, 46).value = .Cells(lRowIndex, 46).value" & vbNewLine & _
"If .Cells(lRowIndex, 47).value <> """" Then .Cells(lRowIndex, 47).value = .Cells(lRowIndex, 47).value" & vbNewLine & _
"If .Cells(lRowIndex, 49).value <> """" Then .Cells(lRowIndex, 48).value = .Cells(lRowIndex, 49).value" & vbNewLine & _
"If .Cells(lRowIndex, 50).value <> """" Then .Cells(lRowIndex, 50).value = .Cells(lRowIndex, 50).value" & vbNewLine & _
"If .Cells(lRowIndex, 52).value <> """" Then .Cells(lRowIndex, 52).value = .Cells(lRowIndex, 52).value" & vbNewLine & _
"If .Cells(lRowIndex, 53).value <> """" Then .Cells(lRowIndex, 53).value = .Cells(lRowIndex, 53).value" & vbNewLine & _
"If .Cells(lRowIndex, 55).value <> """" Then .Cells(lRowIndex, 55).value = .Cells(lRowIndex, 55).value" & vbNewLine & _
"If .Cells(lRowIndex, 56).value <> """" Then .Cells(lRowIndex, 56).value = .Cells(lRowIndex, 56).value" & vbNewLine & _
"If .Cells(lRowIndex, 58).value <> """" Then .Cells(lRowIndex, 58).value = .Cells(lRowIndex, 58).value" & vbNewLine & _
"If .Cells(lRowIndex, 59).value <> """" Then .Cells(lRowIndex, 59).value = .Cells(lRowIndex, 59).value" & vbNewLine & _
"If .Cells(lRowIndex, 61).value <> """" Then .Cells(lRowIndex, 61).value = .Cells(lRowIndex, 61).value" & vbNewLine & _
"If .Cells(lRowIndex, 62).value <> """" Then .Cells(lRowIndex, 62).value = .Cells(lRowIndex, 62).value" & vbNewLine & _
"If .Cells(lRowIndex, 64).value <> """" Then .Cells(lRowIndex, 64).value = .Cells(lRowIndex, 64).value" & vbNewLine & _
"If .Cells(lRowIndex, 65).value <> """" Then .Cells(lRowIndex, 65).value = .Cells(lRowIndex, 65).value" & vbNewLine & _
" End If" & vbNewLine & _
" Next lRowIndex" & vbNewLine & _
" End With" & vbNewLine
With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
iStartLine = .CreateEventProc("BeforeClose", "Workbook") + 1
.InsertLines iStartLine, sCode & sCode1 & sCode2
End With
End Sub

ilyaskazi
06-15-2005, 06:16 AM
thankyou all...it is now working...

MWE
06-15-2005, 11:47 AM
not specifically germane to the nature of the thread, but the 18 If statements bothers my sense of "reasonably compact code". A more compact alternative might be:


For K = 40 To 65
If (K - 39) Mod 3 <> 0 And Cells(lRowIndex, K).Value <> vbNullString Then _
Cells(lRowIndex, K).Value = Cells(lRowIndex, K).Value
Next K