MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Turn range values into code...anyone know how??

  Asked By: Durril    Date: Nov 15    Category: MS Office    Views: 1582

Workbook name is "test01.xls"
Worksheet "Sheet1" has the following text values in Range A1:A4:

A1 = Sub EnterText()
A2 = Workbooks("test01.xls").Worksheets("Sheet1").Range("C2").Select
A3 = Selection = "passed"
A3 = End Sub

Does anyone know how to create a macro that will read the values in
this range and then treat those values as actual VBA code and run it?
In other words, a macro would read the range A1:A4 and then run it as
if it was an actual macro like below:

Sub EnterText()
Selection = "passed"
End Sub

I know this is a strange request and it's a long story but if anyone
can enlighten me on how to do it, I would be so grateful.



3 Answers Found

Answer #1    Answered By: Grady Stewart     Answered On: Nov 15

It utilizes the concept of exporting/importing code  modules.
First, I create  a text  file and write the cell contents to the file.
(the first line contains the new code module name)
then, I remove the module (if it exists) and import the file
as a new module.

Sub Load_Module()
Dim RowNum
Dim fso, f, ModFileName, ModName, NewMod
Dim vbaModules

NewMod = "TestMod"
ModFileName = "C:\temp\" & NewMod & ".bas"
Set fso = CreateObject("Scripting.FileSystemObject")
'Write Cells to Module file
If (fso.fileexists(ModFileName)) Then
fso.deletefile ModFileName
End If
Set f = fso.createTextFile(ModFileName)
f.writeline "Attribute VB_Name = ""TestMod"""
For RowNum = 1 To 1000
If Cells(RowNum, 1) = "" Then Exit For
f.writeline Cells(RowNum, 1)
Next RowNum
'vafilename = "C:\temp\Module2.bas"
' Remove Existing Module
Set vbaModules = ThisWorkbook.VBProject.VBComponents

On Error Resume Next
With ActiveWorkbook.VBProject
For cnt = .VBComponents.Count To 1 Step -1
ModName = .VBComponents(cnt).Name
If (UCase(ModName) = UCase(NewMod)) Then
.VBComponents.Remove .VBComponents(cnt)
End If
Next cnt
End With
' Import module
VBProject.VBComponents.Import Filename:=ModFileName
End Sub

Answer #2    Answered By: Brendan Smith     Answered On: Nov 15

I also had to "tick" the "Trust access to the VBA project object model"
in "Macro Security" on the "Developer" ribbon in Excel 2007

Answer #3    Answered By: Faiza Mian     Answered On: Nov 15

I've been wondering if this was possible, so many thanks Paul for
answering Rhonda's question.

I had to make one change to the code  to get it to work in Excel 2007.
I have no idea if they are also needed in Excel 2003. This was to
make the last line (before the End Sub):
ThisWorkbook.VBProject.VBComponents.Import Filename:=ModFileName
rather than
VBProject.VBComponents.Import Filename:=ModFileName

I have also found that you can then automatically run  the macro  that
has been created using
Application.Run "EnterText"
where "EnterText" is the name of the subroutine.

Didn't find what you were looking for? Find more on Turn range values into code...anyone know how?? Or get search suggestion and latest updates.