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

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,334
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
The CodeModule has an AddFromString method too - just pass the whole string as the argument.
 

JSSWAT

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,334
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
What exactly is in the cell?
 

JSSWAT

New Member
Joined
Jul 12, 2010
Messages
22

ADVERTISEMENT

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
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,334
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
How are you indenting in the cell?
 

JSSWAT

New Member
Joined
Jul 12, 2010
Messages
22

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,334
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I can't replicate that behaviour. Which version of Excel are you using?
 

JSSWAT

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

Forum statistics

Threads
1,141,018
Messages
5,703,756
Members
421,313
Latest member
Mooncake1

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
Top