Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #109097 > unrolled thread

VBA - How to "execute" a dynamic variable assignment

Back to article view | Back to microsoft.public.excel.programming

Contents

  VBA - How to "execute" a dynamic variable assignment ddmcmath@gmail.com - 2016-07-18 15:14 -0700
    Re: VBA - How to "execute" a dynamic variable assignment GS <gs@v.invalid> - 2016-07-19 00:55 -0400
    Re: VBA - How to "execute" a dynamic variable assignment "Auric__" <not.my.real@email.address> - 2016-07-19 08:20 +0000
      Re: VBA - How to "execute" a dynamic variable assignment GS <gs@v.invalid> - 2016-07-19 13:09 -0400
        Re: VBA - How to "execute" a dynamic variable assignment "Auric__" <not.my.real@email.address> - 2016-07-20 00:57 +0000
          Re: VBA - How to "execute" a dynamic variable assignment GS <gs@v.invalid> - 2016-07-20 12:29 -0400
    Re: VBA - How to "execute" a dynamic variable assignment isabelle <i@v.invalid> - 2016-07-19 21:34 -0400
      Re: VBA - How to "execute" a dynamic variable assignment isabelle <i@v.invalid> - 2016-07-19 21:47 -0400

#109097 — 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?

[toc] | [next] | [standalone]

#109098

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

[toc] | [prev] | [next] | [standalone]

#109099

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.

[toc] | [prev] | [next] | [standalone]

#109100

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

[toc] | [prev] | [next] | [standalone]

#109101

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.

[toc] | [prev] | [next] | [standalone]

#109104

> 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

[toc] | [prev] | [next] | [standalone]

#109102

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).codemodule
.InsertLines .ProcStartLine(MyMacro, 0) + 1, s
End With
test1
With ThisWorkbook.VBProject.VBComponents("Module1").codemodule
.DeleteLines .ProcStartLine("ModifyCode", 0), .ProcCountLines("ModifyCode", 0)
End With
End Sub

isabelle

Le 2016-07-18 à 18:14, ddmcmath@gmail.com 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?
>

[toc] | [prev] | [next] | [standalone]

#109103

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).codemodule
.InsertLines .ProcStartLine(MyMacro, 0) + 1, s
test1
.DeleteLines .ProcStartLine("ModifyCode", 0), .ProcCountLines("ModifyCode", 0)
End With
End Sub

  isabelle

[toc] | [prev] | [standalone]

Back to top | Article view | microsoft.public.excel.programming

csiph-web