Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Code to delete/remove code

Code to delete/remove code

ResolvedVersion 2003

Mon 16 Aug 2010, 13:34 Delegate Matt said...

Matt has attended:
Access Intermediate course
Excel VBA Intro Intermediate course
Excel VBA Advanced course

Code to delete/remove code

Hi,

Alot of the reporting I do is issued out to the business and to people who dont nessesarily understand excel or VBA. At the moment i manually remove the code from the version for distribution so that is doesnt run when the open it. Is these an instruction I can build into my current mudule that will remove the code automatically?

Thanks

Wed 18 Aug 2010, 10:53 Trainer Stephen said...

RE: Code to delete/remove code

Hi Matt

Thanks for your question

I am not aware of anyway to remove code from a module automatically.

I assume you are running code on a workbook, and want then to send that work book out without the code.

I would write the code in a separate workbook, and reference the workbook you want to send out in the code. So the code would be written in one workbook, but make changes to another.
You could then send this out without needing to delete the code, as that would be in another workbook.

Regards

Stephen

Wed 18 Aug 2010, 12:05 Delegate Matt said...

RE: Code to delete/remove code

Hi Stephen,

Thanks for your response. When I get a chance to rebuild the report I'll give it a go.

I did have a quick flick through various forums and came across some code. I have'nt tried it yet but wonder what you thought (pasted below). It makes sence to me but I'm resonably new to VBA. This would be run from a seperate module (Not "ThisWorkbook").

Sub DeleteWorkbookEventCode()

''Needs Reference Set To _

"Microsoft Visual Basic For Applications Extensibility"

'Tools>References.

With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule

.DeleteLines 1, .CountOfLines

End With

End Sub

Thanks

Matt

Training courses

98.7 % recommend us

More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Excel tip:

Creating a range of monthly payments as text

You could use a formula to create a range of payment ie. payment amount for x% to y% rate with fixed terms and principle.

The text that would be "between Xamount and Yamount".

Here is how to do it.

1 Use the PMT function to get your monthly payments figure or whatever frequency of payments that you choose he start range.

See PMT under Excel Help

2. Nest these in the ROUND function to round decimals see ROUND under Excel Help

3. Concatenate this using "&" and concatenate " to " and concatenate "Between ".

4. Concatenate the above to PMT function for the end range

ie.

="Between "&ROUND((PMT1),decimal places)&" and "&ROUND((PMT2),decimal places)

View all Excel hints and tips

Code to delete/remove code

Code to delete/remove code

RE: Code to delete/remove code

RE: Code to delete/remove code

Excel articles

Excel tip:

Creating a range of monthly payments as text