Generate placeholder elements

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 ' http://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 ' http://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 ' http://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 ' http://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 ' http://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.




    Homepage

    Section contents

    About Wim

    Wim Gielis is a Business Intelligence consultant and Excel expert

    Other links