Help with parsing text for VBComp.CodeModule

[JSSWAT]

Hey all,

So I basically am terrible with strings... any help is greatly appreciated here. Really I just want to take a big long macro from activesheet.cells(1,1) and stick it in Module1.

So I know how to insert code one line at a time. Example:

With ActiveWorkbook.VBProject.VBComponents("Module1").CodeModule
   LineNum = .CountOfLines + 1
        .InsertLines LineNum, "Public Sub SayHello()"
   LineNum = LineNum + 1
        .InsertLines LineNum, "    MsgBox strMessage"
   LineNum = LineNum + 1
        .InsertLines LineNum, "End Sub"
End With

However, I have no idea how to parse a string of:

Public Sub SayHello()
MsgBox strMessage
End Sub

programatically into what I need to loop through the above...

Anyone know how to split this up based on the returns or just know a way to dump it all in nice and easy?

Thanks!

P.S. I can't use the "from text file" approach due to some security issues.

 

[RoryA]

The CodeModule has an AddFromString method too - just pass the whole string as the argument.

 

[JSSWAT]

So close...

Thank you for your responce. It works just like adding it to the line alone would. However, almost everything which is indented resolves as an error(i.e. in red). If I go back in and delete the spaces before the line of code and re-tab things over the resolve correctly again...

Any thoughts?

I suppose I could always just remove all indenting...

Let me know if you want a screen shot or something.

 

[RoryA]

What exactly is in the cell?

 

[JSSWAT]

So here is an example that I can share...

lets just limit the contents to this (* for space so I can show indents)

Sub CompareNewListUpdatestoVersions()
****Dim ListNameColumn As Integer
****Dim TargetTabColumn As Integer
****Dim ListVersionColumn As Integer
****Dim ListNameColumnValue As String
End Sub

this is the result if I run it with the indents

if I take out the indents I get this

 

[RoryA]

How are you indenting in the cell?

 

[JSSWAT]

I have simply copied from VB and pasted into the cell formula bar. The indenting was the natural product of the auto indent from when I programed the code...

Thanks for your continued interest

 

[RoryA]

I can't replicate that behaviour. Which version of Excel are you using?

 

[JSSWAT]

I use Excel 2007

Just resolved the issue by reprograming the capture of the string. I had been using .AddFromString (ActiveCell.Value) instead of using a string to hold the code first.

now I have:

Dim strCode as String
 
strCode = ActiveCell.Value
 
...
 
.AddFromString( strCode)

Sorry for the newbie mistake... I suppose I should always use a variable instead of directly referencing the cell value.