wimgielis.com

Generate placeholder elements

• Mar. 09, 2014

	Example files with this article:
	Generate placeholder elements

Introduction

Actually, this article is more of an Excel VBA inspired article, since I will show a number of neat/advanced tricks in VBA-coding. For example, to avoid loops and create a very condensed code snippet. The case is as follows: say that you want to generate measures / parameters in a dimension, which act like placeholder elements - an alias determines the contents of the elements. Or you need a linenumber dimension with elements LN01 until LN99. True, Excel can do this, if you do this on a regular basis, then it can be done in seconds. You can also use custom lists or the built-in functionality of series. Again true, a good deal of Notepad++ can do this too, and probably many other tools as well. Still, the code that I will show you can handle many more situations.

VBA-code

This result, based on input by the user, can be generated with almost a oneliner in Excel VBA:

Sub PlaceholderElements_ToSheet()
' Wim Gielis
' https://www.wimgielis.com
'''''
' Placeholder elements in TM1
' 03/09/14
'''''
    sPrefix = "LN"
    sSuffix = ""
    iStartAt = 1
    iCount = 100
    iDigits = 3

    Columns(1).ClearContents

    v = Evaluate("TRANSPOSE(""" & sPrefix & """&INDEX(TEXT(ROW(1:" & iCount & ")+" & iStartAt - 1 & ",""" & String(iDigits, "0") & """),)&""" & sSuffix & """)")
    Cells(1).Resize(UBound(v)).Value = Application.Transpose(v)

End Sub

That is basically all code you need; quite impressive, isn’t it? A Variant variable holds the result of the calculations, and is then written to the sheet (column A). If you want to all the user to input his/her choices and let the code generate the result, you could use:

Sub PlaceholderElements_ToSheet_UserInput()
' Wim Gielis
' https://www.wimgielis.com
'''''
' Placeholder elements in TM1, input from the user
' 03/09/14
'''''
    With Application
        sPrefix = .InputBox("Prefix:", "", "Param_", Type:=2)
        sSuffix = .InputBox("Suffix:", "", "", Type:=2)
        iStartAt = .InputBox("Start at:", "", 1, Type:=1)
        iCount = .Max(.InputBox("Number of parameters:", "", 10, Type:=1), 1)
        iDigits = .Max(.InputBox("Number of digits:", "", 3, Type:=1), 1)
    End With

    Columns(1).ClearContents

    v = Evaluate("TRANSPOSE(""" & sPrefix & """&INDEX(TEXT(ROW(1:" & iCount & ")+" & iStartAt - 1 & ",""" & String(iDigits, "0") & """),)&""" & sSuffix & """)")
    Cells(1).Resize(UBound(v)).Value = Application.Transpose(v)

End Sub

To understand the code a little bit better, first run this code or the code immediately below:

Sub ExampleToUnderstand()
    MsgBox Join([TRANSPOSE(INDEX(TEXT(ROW(1:10),"000"),))], vbCr)

End Sub

Sub ExampleToUnderstand_2()
    MsgBox Join(Evaluate("TRANSPOSE(INDEX(TEXT(ROW(1:10),""000""),))"), vbCr)

End Sub

The result should be twice this message box:

After that, I insert the relevant - non-trivial - additions to make the code more generic and more versatile. I succeeded (in my opinion). If you want to allow for a custom separator between 2 successive elements (rather than Enter for example):

Sub PlaceholderElements_ToMsgBox_UserInput()
' Wim Gielis
' https://www.wimgielis.com
'''''
' Placeholder elements in TM1, input from the user including a separator. Output to a message box
' 03/09/14
'''''
    With Application
        sPrefix = .InputBox("Prefix:", "", "Param_", Type:=2)
        sSuffix = .InputBox("Suffix:", "", "", Type:=2)
        iStartAt = .InputBox("Start at:", "", 1, Type:=1)
        iCount = .Max(.InputBox("Number of parameters:", "", 10, Type:=1), 1)
        iDigits = .Max(.InputBox("Number of digits:", "", 3, Type:=1), 1)
        sSeparator = Replace(Replace(.InputBox("Separator:", "", "enter", Type:=2), "enter", vbCr), "tab", vbTab)
    End With

    MsgBox Join(Evaluate("TRANSPOSE(""" & sPrefix & """&INDEX(TEXT(ROW(1:" & iCount & ")+" & iStartAt - 1 & ",""" & String(iDigits, "0") & """),)&""" & sSuffix & """)"), sSeparator)

End Sub

For example, 1 long string with a comma-separated list of elements:

The alternative looping code would look like this:

Sub PlaceholderElements_ToMsgBox_UserInput()
' Wim Gielis
' https://www.wimgielis.com
'''''
' Placeholder elements in TM1, input from the user. Output to sheet and message box
' 03/09/14
'''''
    With Application
        sPrefix = .InputBox("Prefix:", "", "Param_", Type:=2)
        sSuffix = .InputBox("Suffix:", "", "", Type:=2)
        iStartAt = .InputBox("Start at:", "", 1, Type:=1)
        iCount = .Max(.InputBox("Number of parameters:", "", 10, Type:=1), 1)
        iDigits = .Max(.InputBox("Number of digits:", "", 3, Type:=1), 1)
        sSeparator = Replace(Replace(.InputBox("Separator:", "", "enter", Type:=2), "enter", vbCr), "tab", vbTab)
    End With

    Columns(1).ClearContents

    v = Cells(1).Resize(iCount).Value
    For m = 1 To iCount
        v(m, 1) = sPrefix & Format(m + iStartAt - 1, String(iDigits, "0")) & sSuffix
    Next
    MsgBox Join(Application.Transpose(v), sSeparator)
    Cells(1).Resize(iCount).Value = v

End Sub

Both codes have their advantages. Looping codes will be simpler to write and understand by the non-technical programming person. Code 1 is cleaner and avoids looping. It shows a number of generic principles to condense coding in VBA.

Adding the code to automatically copy the result to the clipboard, makes this little tool indispensable:

Sub PlaceholderElements_ToClipboard()
' Wim Gielis
' https://www.wimgielis.com
'''''
' Placeholder elements in TM1, output to the clipboard
' 03/09/14
'''''
    With Application
        sPrefix = .InputBox("Prefix:", "", "Param_", Type:=2)
        sSuffix = .InputBox("Suffix:", "", "", Type:=2)
        iStartAt = .InputBox("Start at:", "", 1, Type:=1)
        iCount = .Max(.InputBox("Number of parameters:", "", 10, Type:=1), 1)
        iDigits = .Max(.InputBox("Number of digits:", "", 3, Type:=1), 1)
        sSeparator = Replace(Replace(.InputBox("Separator:", "", "enter", Type:=2), "enter", vbCr), "tab", vbTab)
    End With

    With New DataObject
        .SetText Join(Evaluate("TRANSPOSE(""" & sPrefix & """&INDEX(TEXT(ROW(1:" & iCount & ")+" & iStartAt - 1 & ",""" & String(iDigits, "0") & """),)&""" & sSuffix & """)"), sSeparator)
        .PutInClipboard
    End With

End Sub

Do not forget to add a reference to "Microsoft Forms 2.0 Object Library" to access the clipboard programmatically.

Wrap up

This article demonstrates the use of pretty advanced Excel VBA-coding to generate placeholder elements for TM1.

• « Previous TM1 article
• Next TM1 article »