Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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?
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 538
Default 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.
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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

  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 538
Default 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.


  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 99
Default 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?

  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 99
Default 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
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need Formula to "Adjust" to New Column Assignment bethe Excel Worksheet Functions 3 January 22nd 10 12:13 AM
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell Steve Kay Excel Discussion (Misc queries) 2 August 8th 08 01:54 AM
"Type mismatch" when I try to fill an Array variable with "+" [email protected] Excel Discussion (Misc queries) 1 April 17th 07 01:28 PM
When Using Format(Now(), "yyyymmmddhhmm") get wrong number of arguments or invalid property assignment Error Connie Excel Programming 2 November 8th 06 09:30 AM
use variable in Workbooks("book1").Worksheets("sheet1").Range("a1" Luc[_3_] Excel Programming 2 September 28th 05 08:37 PM


All times are GMT +1. The time now is 01:18 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"