VBA - How to "execute" a dynamic variable assignment
 

Cell A1: "MyVar"
Cell A2: "Purple"
Cell A3: "String"


I want to run code that does the following

Public MyVar as String
MyVar = "Purple"

How would I do that?

it's kinda like

Execute "Dim " & range(A1) & " as " & range(A3)
Execute range(A1) & " = '" & range(A2) & "'"

Is there a way?

VBA - How to "execute" a dynamic variable assignment
 

Dim MyVar 'as variant so it can hold any 'type' of data

'Assign cell values this way...

Sub Test()
Dim MyVar
MyVar = [a2].Value: Debug.Print MyVar
MyVar = [a3].Value: Debug.Print MyVar
MyVar = Empty: Debug.Print MyVar
MyVar = 123: Debug.Print MyVar
End Sub

...results in Immediate Window:

Purple
String

123

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

VBA - How to "execute" a dynamic variable assignment
 

ddmcmath wrote:

Cell A1: "MyVar"
Cell A2: "Purple"
Cell A3: "String"


I want to run code that does the following

Public MyVar as String
MyVar = "Purple"

How would I do that?

it's kinda like

Execute "Dim " & range(A1) & " as " & range(A3)
Execute range(A1) & " = '" & range(A2) & "'"

Is there a way?

Google is your friend.

https://www.google.com/#q=vba+dynamic+code

This page has some code that you can perhaps modify to do what you want:

https://gist.github.com/dck-jp/15d96e98e7bdb1bfc266

(Pay attention to the "GenerateSub" function on that page.)

There are other ways, but offhand I can't remember how to do any of them any
more. (It's been a looooong time since I've needed to.)

--
"All new source code!" As if source code rusted.

VBA - How to "execute" a dynamic variable assignment
 

Good reply! Wasn't thinking about adding to a VBA project...

In this case the entire module code could be written in a single
column, then exported to a .bas, then imported into a VBA project.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

VBA - How to "execute" a dynamic variable assignment
 

GS wrote:

Good reply! Wasn't thinking about adding to a VBA project...

In this case the entire module code could be written in a single
column, then exported to a .bas, then imported into a VBA project.

The page I linked to shows that exporting to a .bas is an unnecessary step.

IIRC it can also be done via the VBScript runtime using eval() -- but it
looks like that function may have been removed from Win7 (at least) because I
can't get it to work here, even in a .vbs file... and I don't really care
enough to continue investigating.

--
Unless you're driving a f%*%$)g battleship,
the train is ALWAYS going to win.

VBA - How to "execute" a dynamic variable assignment
 

hi,

with
Cell A1: "MyVar"
Cell A2: "Purple"
Cell A3: "String"

copy the following code in the Module1, and run macro ModifyCode

Sub test1()
End Sub

Sub ModifyCode()
Dim code(1)
Dim Mdl As String, MyMacro As String

Mdl = "Module1"
MyMacro = "test1"

code(0) = "Dim " & [A1] & " as " & [A3]
code(1) = "msgbox [A2]"

For i = 0 To 1
s = s & code(i) & Chr(10)
Next

With ThisWorkbook.VBProject.VBComponents(Mdl).codemodul e
..InsertLines .ProcStartLine(MyMacro, 0) + 1, s
End With
test1
With ThisWorkbook.VBProject.VBComponents("Module1").cod emodule
..DeleteLines .ProcStartLine("ModifyCode", 0), .ProcCountLines("ModifyCode", 0)
End With
End Sub

isabelle

Le 2016-07-18 Ã* 18:14, a écrit :

Cell A1: "MyVar"
Cell A2: "Purple"
Cell A3: "String"


I want to run code that does the following

Public MyVar as String
MyVar = "Purple"

How would I do that?

it's kinda like

Execute "Dim " & range(A1) & " as " & range(A3)
Execute range(A1) & " = '" & range(A2) & "'"

Is there a way?

VBA - How to "execute" a dynamic variable assignment
 

a little bit shorter,

Sub test1()
End Sub

Sub ModifyCode()
Dim code(1)
Dim Mdl As String, MyMacro As String

Mdl = "Module1"
MyMacro = "test1"

code(0) = "Dim " & [A1] & " as " & [A3]
code(1) = "msgbox [A2]"

For i = 0 To 1
s = s & code(i) & Chr(10)
Next

With ThisWorkbook.VBProject.VBComponents(Mdl).codemodul e
..InsertLines .ProcStartLine(MyMacro, 0) + 1, s
test1
..DeleteLines .ProcStartLine("ModifyCode", 0), .ProcCountLines("ModifyCode", 0)
End With
End Sub

isabelle

VBA - How to "execute" a dynamic variable assignment
 

GS wrote:

Good reply! Wasn't thinking about adding to a VBA project...

In this case the entire module code could be written in a single
column, then exported to a .bas, then imported into a VBA project.

The page I linked to shows that exporting to a .bas is an unnecessary
step.

IIRC it can also be done via the VBScript runtime using eval() -- but
it looks like that function may have been removed from Win7 (at
least) because I can't get it to work here, even in a .vbs file...
and I don't really care enough to continue investigating.

Isabelle has offered some direct VBA that looks like a possible
approach. I haven't done much this way because I found it easier to
give my addins their own addins when user-defined extensibility (termed
'Plugins') was used with core apps. These apps (XLAs) were designed to
load any files found in their 'Plugins' subfolder. This allows users to
add their own custom features and associated menus to what I call 'core
apps' so they could automate some of the 'unique to them' tasks related
to their use of my addin.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus