Consulting

Results 1 to 13 of 13

Thread: Solved: Insert code to worksheet programmatically and run it in the same Sub - compiler error

  1. #1
    VBAX Regular
    Joined
    Jun 2007
    Location
    Moscow
    Posts
    12
    Location

    Question Solved: Insert code to worksheet programmatically and run it in the same Sub - compiler error

    Hello everyone, thanks for your attention.

    I have a Worksheet with my own method (a Sub inside the code). I can successfully use this method from any Module, everything works fine.

    But now I need to make (programmatically) another worksheet with the same method, in the same workbook. I faced a problem with that.

    I tried creating a new Worksheet, and then copying the code from original Worksheet line-by-line. The sheet is created, the code is copied but! when I try to use my (copied) method on a new worksheet, it gives me an error "Method or data member not found".

    If I copy the sheet as the code with one sub, stop it, and then run another sub that uses the method, it works fine. But if I copy sheet and try to use my method in one turn, it won't work. Maybe it's the compiler that can't read newly-inserted code for subs?

    Thank you!

  2. #2
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,999
    Location
    Why not place the code in This Workbook module and name the required sheets as an array?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  3. #3
    VBAX Regular
    Joined
    Jun 2007
    Location
    Moscow
    Posts
    12
    Location
    Quote Originally Posted by Aussiebear
    Why not place the code in This Workbook module and name the required sheets as an array?
    Well, I find these worksheets with my custom methods, properties and functions convenient. ^_^ Now I need to clone them from VBA module "on the run"...

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    How about showing the code that errors.

  5. #5
    VBAX Regular
    Joined
    Jun 2007
    Location
    Moscow
    Posts
    12
    Location
    Quote Originally Posted by xld
    How about showing the code that errors.
    Yes, here it is. I had to clean it up a litte...

    First of all, I have a worksheet, its Excel Name is "SheetToCopy", its VB name is "Sheet1". It has the following code:
    [VBA]
    Dim x As Integer
    Sub CountZeros()
    Dim i, s
    s = 0
    i = 1
    While Not IsEmpty(Cells(i, 1))
    If Cells(i, 1) = 0 Then s = s + 1
    i = i + 1
    Wend
    x = s
    End Sub

    Property Get ZerosInFirstColumn()
    ZerosInFirstColumn = x
    End Property
    [/VBA]

    In MyWorkbook I have the following code:

    [VBA]
    Function CreateSheetWithCode(SheetName) As VBComponent
    Dim ws As Worksheet
    Dim vbc As VBComponent
    Dim c1, c2, c3
    Dim i, j, np

    Set ws = Worksheets.Add

    'Here I assume that the newly added sheet is the last in VBComponents
    c1 = ActiveWorkbook.VBProject.VBComponents.Count
    Set vbc = ActiveWorkbook.VBProject.VBComponents(c1)

    'Now I have to change its Excel name. I loop thru the properties until I find it. Maybe there's a better way?
    c2 = vbc.Properties.Count
    For i = 1 To c2
    If vbc.Properties(i).Name = "Name" Then
    np = i
    Exit For
    End If
    Next i
    vbc.Properties(np).Value = SheetName

    'Copying the code from original sheet:
    c3 = ActiveWorkbook.VBProject.VBComponents("Sheet1").CodeModule.CountOfLines
    For j = 1 To c3
    vbc.CodeModule.InsertLines j, ActiveWorkbook.VBProject.VBComponents("Sheet1").CodeModule.Lines(j, 1)
    Next j

    Set CreateSheetWithCode = vbc
    End Function
    [/VBA]

    At last, I have a module. Here is the code:

    [VBA]
    Sub zzz()
    Dim NS
    Sheet1.CountZeros 'I test the method on the original sheet
    Debug.Print Sheet1.ZerosInFirstColumn 'I test the property on the original sheet
    Set NS = ActiveWorkbook.CreateSheetWithCode("S1") 'I create new sheet
    NS.Name = "MyNewSheet" 'New VB name for the new sheet; works fine, no error here.
    NS.CountZeros 'Causes an error 438 - object doesn't support property or method

    End Sub

    [/VBA]
    Sincerly Yours,
    千歳みどり, FRM

    Member of:
    www.riskofficer.ru, dom.bankir.ru, www.animeforum.ru

  6. #6
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Why not just copy the sheet....the code will go with it..?
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  7. #7
    VBAX Regular
    Joined
    Jun 2007
    Location
    Moscow
    Posts
    12
    Location
    Quote Originally Posted by lucas
    Why not just copy the sheet....the code will go with it..?
    YES!!! Thank you a lot! It really will be copied as well! I dunno why I thought it won't... ~_~


    I don't know - shall I mark the thread as "solved" or not? My problem is solved, but the theory of inserting code into a new sheet and referencing these properties in the same procedure is still unclear.


    By the way, I tried to modify my last Sub in Module1 as follows:
    [VBA]
    Sub zzz()
    Dim ns
    Dim ws As Worksheet
    Sheet1.CountZeros 'I test the method on the original sheet
    Debug.Print Sheet1.ZerosInFirstColumn 'I test the property on the original sheet
    Set ns = ActiveWorkbook.CreateSheetWithCode("S1") 'I create new sheet
    ns.Name = "MyNewSheet" 'New VB name for the new sheet; works fine, no error here.

    Worksheets("S1").CountZeros ' THE NEW LINE - I try to reference new object as a sheet, not like VBComponent.

    End Sub
    [/VBA]
    And now my code makes Excel to CRASH!!! o_O
    Sincerly Yours,
    千歳みどり, FRM

    Member of:
    www.riskofficer.ru, dom.bankir.ru, www.animeforum.ru

  8. #8
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Why not post your workbook and give us an explaination as to what your trying to accomplish with that sub?

    ps you can mark your thread solved using the thread tools at the top of the page and still post followup questions here at any time....your choice.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  9. #9
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Looks like you just need the code to copy the sheet..clear the contents and rename the new sheet.....correct?
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  10. #10
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Oh and happy birthday....
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  11. #11
    VBAX Regular
    Joined
    Jun 2007
    Location
    Moscow
    Posts
    12
    Location
    Quote Originally Posted by lucas
    Looks like you just need the code to copy the sheet..clear the contents and rename the new sheet.....correct?
    Yeah, thanks for your help!


    I don't know why I was so shure that if I just copy a sheet, the code will not be copied... v_v Anyway, I had to learn using VBE object and stuff, so my error gave me some experience ^_^

    Quote Originally Posted by lucas
    Oh and happy birthday....
    Thank you!!!
    Sincerly Yours,
    千歳みどり, FRM

    Member of:
    www.riskofficer.ru, dom.bankir.ru, www.animeforum.ru

  12. #12
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Hi 千歳みどり,
    There seems to me to be no reason for the code you have in the sheets to be in the sheets...none of it is activated by a sheet change or doublclick or click, etc....

    I don't see any reason it can't be in a standard module and I don't think it needs to be in every sheet...
    This is the code for that's in each sheet:
    [VBA]Sub CountZeros()
    Dim i, s
    s = 0
    i = 1
    While Not IsEmpty(Cells(i, 1))
    If Cells(i, 1) = 0 Then s = s + 1
    i = i + 1
    Wend
    x = s
    End Sub
    Property Get ZerosInFirstColumn()
    ZerosInFirstColumn = x
    End Property
    [/VBA]

    I'm not even sure what the property Get ZerosInFirstColumn is or does but I don't see any reason to have it in every sheet.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  13. #13
    VBAX Regular
    Joined
    Jun 2007
    Location
    Moscow
    Posts
    12
    Location
    Quote Originally Posted by lucas
    I'm not even sure what the property Get ZerosInFirstColumn is or does but I don't see any reason to have it in every sheet.
    I'll think on it, thank you!
    Sincerly Yours,
    千歳みどり, FRM

    Member of:
    www.riskofficer.ru, dom.bankir.ru, www.animeforum.ru

Posting Permissions

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