Consulting

Results 1 to 6 of 6

Thread: Compile error: vba VIA vba

  1. 06-15-2005, 04:48 AM #1
    ilyaskazi
    ilyaskazi is offline
    VBAX Tutor ilyaskazi's Avatar
    Joined
    Mar 2005
    Posts
    202
    Location

    Question Compile error: vba VIA vba

    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
    Reply With Quote Reply With Quote
  2. 06-15-2005, 05:37 AM #2
    Bob Phillips
    Bob Phillips is offline
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    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
    Reply With Quote Reply With Quote
  3. 06-15-2005, 05:40 AM #3
    Richie(UK)
    Richie(UK) is offline
    VBAX Contributor Richie(UK)'s Avatar
    Joined
    May 2004
    Location
    UK
    Posts
    188
    Location
    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?
    Reply With Quote Reply With Quote
  4. 06-15-2005, 06:00 AM #4
    Bob Phillips
    Bob Phillips is offline
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    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
    Reply With Quote Reply With Quote
  5. 06-15-2005, 06:16 AM #5
    ilyaskazi
    ilyaskazi is offline
    VBAX Tutor ilyaskazi's Avatar
    Joined
    Mar 2005
    Posts
    202
    Location
    thankyou all...it is now working...
    Reply With Quote Reply With Quote
  6. 06-15-2005, 11:47 AM #6
    MWE
    MWE is offline
    VBAX Expert
    Joined
    Feb 2005
    Posts
    929
    Location
    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
    "It's not just the due date that's important, it's also the do date" [MWE]

    When your problem has been resolved, mark the thread SOLVED by clicking on the Thread Tools dropdown menu at the top of the thread.
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules