String to code

[Guest]

Hello,

Probably a stupid question, but I don't seem to be able to fix it myself.

I have build a line of code in VBA, based on some cell content (selection of
sheets in a workbook), and now I have, in a string-defined variable, the
appropriate code, which I can check with debug.print. The variable, named
txt1, contains the text

Sheets(Array("Data02", "Data03")).Move after:=Sheets("StartAkk")

which is actually what I want to do (the actual sheet names selected will
change at runtime according to some "flags" set in a worksheet).

But now I need to execute that line of code, ie., "activate" the content of
txt1.

How do I do that ?


Thank you very much in advance !


Morten Jansen

 

[Tom Ogilvy]

You can't build a line of code dynamically and then executed it in most
cases.

If the problem is you need to build the array dynamically, rather than
concatenating the string together do this

Dim myArray as Varian
Dim sh as worksheet
for each sh in worksheets
if somecondition then
if not isarray(myArray) then
redim myArray(0 to 0)
myArray(0) = sh.name
else
redim preserve myArray(0 to ubound(myArray,1)+1)
myArray(ubound(myArray)) = sh.name
end if
end if
Next
Sheets(myArray).Move after:=Sheets("StartAkk")

 

[Guest]

Hello,

Thank you very much for your answer. It puzzles me a bit, though, as it
seems quite reasonable for me as a novice VBA user to want to do exactly what
I try to do. I will, however, try to adapt your frame to my use, thanks a
lot. Pls. find below my code that can't be used further, just for your info.

Best regards,

Morten Jansen
-----
Sub Udvaelg()
' Procedure for preparing sheets in workbook for "3D-summing":
' Arrange the selected sheets (selection is in sheet "lister", selected
sheet names have a "1" in the next column, other sheets have a "0")
' between sheets "StartAkk" and "SlutAkk"

Dim AktArk as Variant
Dim T1 As Integer
Dim Txt1 As String
Dim Txt2 As String

Sheets(Array("SlutAkk")).Move after:=Sheets("StartAkk")

ActiveWorkbook.Names.Add Name:="AktiveArk",
RefersToR1C1:="=OFFSET(Lister!R2C2,0,0,COUNTA(Lister!R2C2:R65536C2),2)"
AktArk = Worksheets("Lister").Range("AktiveArk")

Txt1 = "Sheets(Array("

For T1 = 1 To UBound(AktArk)
If AktArk(T1, 2) = 1 Then Txt1 = Txt1 & Chr(34) & AktArk(T1, 1) & Chr(34) &
", "
Next T1

Txt2 = ")).Move after:=Sheets(" & Chr(34) & "StartAkk" & Chr(34) & ")"
Txt1 = WorksheetFunction.Replace(Txt1, Len(Txt1) - 1, 1, Txt2)

Debug.Print Txt1

' ... Application.Run Txt1 ...

End Sub