Help with parsing text for VBComp.CodeModule

JSSWAT

New Member
Joined
Jul 12, 2010
Messages
22
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:

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

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
The CodeModule has an AddFromString method too - just pass the whole string as the argument.
 
Upvote 0
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.
 
Upvote 0
What exactly is in the cell?
 
Upvote 0
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

example1q.jpg


if I take out the indents I get this


example2.jpg
 
Upvote 0
How are you indenting in the cell?
 
Upvote 0
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
 
Upvote 0
I can't replicate that behaviour. Which version of Excel are you using?
 
Upvote 0
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:
Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top