Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Edit Excel VBA with VBA

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Can anyopne help me figure out how to edit a VBA project's code with code? Specifically, I want to delete all procedures in a sheet's code module by means of a VBA procedure in the same project.
    Background: one sheet contains a pivot table. The sheet's code module contains a small procedure to refresh the pivot cache on sheet activate event and the procedure calls a number of preparatory procedures located in a standard module. But, I need to extract the sheet alone (i.e. without the sheet containing the database) to a new workbook to email a customer. Since only the sheet is saved in the new workbook and without the project's standard modules, the activate procedure cannot find the preparatory procedures so bugs.
    I need to automate the extraction of the pivot table sheet to a new workbook also deleting the VBA code in the sheet's code module.
    Thanks for any help.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I remember seeing this topic before on this board.

    A good reply was given with a link to a website, I beleive, which showed you how to purge your code out of a copy.

    Do a search of this site...

    Another option to check into.
    Search VBA help for "Conditional Compilation"

    Tom


    [ This Message was edited by: TsTom on 2002-05-04 08:43 ]

  3. #3
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Bury the following in an appropriate sheet module:

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Dim ws As Worksheet
    For Each ws In activeworkbook.Worksheets
        With activeworkbook.VBProject.VBComponents("sheet" & _
            ws.Index).CodeModule
                    .DeleteLines 1, .CountOfLines
        End With
    Next
    End Sub
    Hope this helps.

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-05-04 09:31 ]

  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Actually, don't bury this in the sheet module!! You'll kill your existing procedures. Just pull the dim statement and the remainder of the code and put it in your procedure that moves the sheet[s] in question.

    Incidentally P, any relation to Vancouver's goalie?

    Sorry 'bout that and bon soir!

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-05-04 09:52 ]

  5. #5
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this code ....change as required.
    This will delete the ACTIVEWORKBOOKS Sheet
    code....ie. After your routine Copies the
    sheet to a new workbook then Run this code,
    (AMENDED to suit)


    Option Explicit

    '*****************************************************************************
    '* This procedure removes all code the activeworkbook Sheet code you specify.
    '*
    '* Reference to the VB Extensibility library is Automatically set when
    '* 1st run via the GUID routine.
    '*
    '* Removes from Workbooks all:
    '* Code in sheet and workbook modules = vbext_ct_Document
    '* Ivan F Moala 02/09/2001
    '*****************************************************************************

    Sub RunThisFirst()
    On Error Resume Next '// if it already exits
    ThisWorkbook.VBProject.References.AddFromGuid _
    "{0002E157-0000-0000-C000-000000000046}", 5, 0
    On Error GoTo 0

    TellUser

    Remove_SheetCode

    End Sub

    Sub Remove_SheetCode()
    '// XL2K:
    '// Dim VBComp As VBComponent, AllComp As VBComponents, ThisProj As VBProject
    '// XL97 & XL2K:
    Dim VBComp As Object, AllComp As Object, ThisProj As Object
    Dim RemoveOK As Integer, Wbk As Workbook

    If ActiveWorkbook.Name <> ThisWorkbook.Name Then
    '// Ask the User Just in case!
    RemoveOK = MsgBox("Remove All Sheet code from:= " & ActiveWorkbook.Name & " ?", vbYesNo)
    If RemoveOK = vbNo Then Exit Sub
    Set ThisProj = ActiveWorkbook.VBProject
    Set AllComp = ThisProj.VBComponents

    For Each VBComp In AllComp
    With VBComp
    Select Case .Type
    Case 100
    '// Remove Event codes
    .CodeModule.DeleteLines 1, .CodeModule.CountOfLines
    End Select
    End With
    Next
    End If

    Set ThisProj = Nothing
    Set AllComp = Nothing

    MsgBox "Done!" & Space(20), vbInformation + vbSystemModal

    End Sub

    Sub TellUser()
    Dim msg As String
    Dim Proceed As Integer

    msg = "This routine will delete ALL Code" & vbCr
    msg = msg & "from the ActiveWorkbook!" & vbCr
    msg = msg & "If you do not want to delete them then" & vbCr
    msg = msg & "answer NO at the prompt to delete the code." & vbCr & vbCr
    msg = msg & "To start this routine click YES" & vbCr
    msg = msg & "To cancel this routine Click NO" & vbCr

    Proceed = MsgBox(msg, vbInformation + vbYesNo, "Proceed")
    If Proceed = vbNo Then End
    End Sub




    If All else Fails;


    _________________
    Kind Regards,
    Ivan F Moala
    Have a Nice day

    [ This Message was edited by: Ivan F Moala on 2002-05-04 15:17 ]

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ivan my old mate,

    dam fine post, is that really you?

    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  7. #7
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-05 01:42, Jack in the UK wrote:
    ivan my old mate,

    dam fine post, is that really you?

    No ?! .....although there can be times I feel
    like doing that...

    Kind Regards,
    Ivan F Moala From the City of Sails

  8. #8
    New Member
    Join Date
    May 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for your replies:

    TsTom: thanks for the conditional compilation suggestion. Might help if I can figure out how to use it.

    NateO: thanks for the code suggestion. I'll look into it. Nope, not Vancouver goalie, but was born and raised in nearby Seattle although now live in Geneva, Switzerland.

    Ivan: thanks for the code suggestion. I've got to study it before I try it out.

    Thanks again,

  9. #9
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    P, Seattle's a nice spot, used to live outside of Vancouver myself. A slightly different format:

    Code:
    Sub getrid()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        With ActiveWorkbook.VBProject.VBComponents(ws.CodeName).CodeModule
                    .DeleteLines 1, .CountOfLines
        End With
    Next ws
    End Sub
    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-05-07 10:02 ]

  10. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-05 01:42, Jack in the UK wrote:
    ivan my old mate,
    dam fine post, is that really you?
    Jack....

    this image is a small part of a large mpeg. I can't remember the full story, but the guy had been under surveillance for about a year as he had caused thousands of dollars of repairs to his equipment (it may have been IBM or NASA or somewhere blue-chip like that) so they installed cameras to look at his cubicle...

    The full mpeg shows him banging his keyboard, then hitting the screen and cursing.... finally, he gets up, picks up his keyboard and hits the moniter with it, sending it flying, with a totally bemused co-worker looking on in amazement into his cubicle.

    I believe he was fired.

    I'll try to pull it out for you, I know I have the full copy on my other computer here (disconnected)


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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