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!

[Aussiebear]

Why not place the code in This Workbook module and name the required sheets as an array?

[みどり]

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"...

[Bob Phillips]

How about showing the code that errors.

[みどり]

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:

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


In MyWorkbook I have the following code:


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


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


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

[lucas]

Why not just copy the sheet....the code will go with it..?

[みどり]

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:

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

And now my code makes Excel to CRASH!!! o_O

[lucas]

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.

[lucas]

Looks like you just need the code to copy the sheet..clear the contents and rename the new sheet.....correct?

[lucas]

Oh and happy birthday....

[みどり]

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 ^_^


Oh and happy birthday.... Thank you!!!

[lucas]

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:
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


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'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!