McAfee deletes code from VBA module

Question

I am trying to program an Excel module where it dynamically inserts code in new objects in a form that is created at design time.

I am using this code where "Code" contains a string with the actual code that should go into the DstrFiles object.

Dim DstrFiles As Object
 Set DstrFiles = ThisWorkbook.VBProject.VBComponents("DistributeFiles")

 With DstrFiles.CodeModule
    .InsertLines .CountOfLines + 1, Code
 End With

My problem is that when I use the .InsertLines, McAfee removes the entire Code from my module, is there a way to work around this?

First I create the label with:

Form1.Controls.Add("Forms.Label.1", "Label1", True)

Then I use the .InsertLines to create some code to go with the Label.

For instance, I want the background color of the label to turn red when someone clicks on it. This has been very easy to accomplish with the ".InsertLines".

An ugly way to work around this is to just create a bunch of code beforehand that is ready in the background and then limit the amount of labels that may be created on the fly. - I hope it won't come to that.

I have been googeling around, and this seems to be a known problem with McAfee.

Do anyone know a way to create a dynamic user form that can add code to new labels or button that are added with the Contrls.Add method?

Printmaker

There is a discussion here that may help: vbforums.com/showthread.php?t=358444 – Skinflint

Answer 1

You should not be generating new labels by writing code that creates the controls.

You should be using the .Add method on the Controls collection to create new labels.

For example:

UserForm1.Controls.Add("Forms.Label.1", "foo", True)

You can use WithEvents to get the events.

For example, in UserForm1,

Public WithEvents a As MSForms.Label

Private Sub a_Click()

    MsgBox "label clicked"

End Sub

Private Sub CommandButton1_Click()

    Set a = UserForm1.Controls.Add("Forms.Label.1", "foo", True)
    a.Visible = True
    a.Caption = "Hi There"

End Sub

If you want to make a dynamic array of newly added controls, you'll need to create a little wrapper class. Sample code for that is here.

Schaumberger

This is not really enough for me. I need some code to go with the label as well. Something should happen when you click on the label. – Printmaker

@Christian: Joel Spolsky invented the VBA/Macro capability for Excel :) joelonsoftware.com/items/2007/04/25.html – Congratulant

Thanks for the help Joel, I am finally able to do what I intended and in a neat way. – Printmaker

Answer 2

If possible I would recommend against dynamic generation of code (smells like a self-modifying program?).

It's maybe hard to say without knowing your specific problem but I bet there is a better solution using a function with the necessary parameters.

Bolster

Answer 3

You might be able to workaround this version of McAfee. But the next version of the data-files, or another malware blocker might block you anyhow.

So you can create code like this to run on you development machine, but it will never (or only temporary) work when distributed to customers.

Shlomo