timemachine
New Member
- Joined
- Jul 1, 2012
- Messages
- 3
Hi,
I have inherited a macro which needs updating, from a functionality and logic point of view it is very straight forward but I am not sure how the macro was originally created.
It is a (hidden) worksheet, labelled Macro1, with each line having a either a function or formula and the the macro goes line by line and executes whatever function or formula is on that line.
So when you add a text file this macro will run through each function and parse and format the text file (which is in a csv format).
I have not been able to find anything on the web that even remotely looks to have implemented a macro like this, I know it works but how? and how do go about creating it.
I would normally think of just writing it in vba or recording a macro and have never seen it done this way.
So if anyone can explain how you go about creating a macro this way and any benefits.
Also some of functions do not look like legitimate excel functions ( eg Select()) to me so I don't know how it has worked so far.
If anyone can shed any like on this.
below is a snippet.
I have inherited a macro which needs updating, from a functionality and logic point of view it is very straight forward but I am not sure how the macro was originally created.
It is a (hidden) worksheet, labelled Macro1, with each line having a either a function or formula and the the macro goes line by line and executes whatever function or formula is on that line.
So when you add a text file this macro will run through each function and parse and format the text file (which is in a csv format).
I have not been able to find anything on the web that even remotely looks to have implemented a macro like this, I know it works but how? and how do go about creating it.
I would normally think of just writing it in vba or recording a macro and have never seen it done this way.
So if anyone can explain how you go about creating a macro this way and any benefits.
Also some of functions do not look like legitimate excel functions ( eg Select()) to me so I don't know how it has worked so far.
If anyone can shed any like on this.
below is a snippet.
Code:
=SELECT("C1")
=TEXT.TO.COLUMNS(R1C1,1,1,FALSE,FALSE,TRUE,FALSE,FALSE,FALSE,,{1,1;2,1;3,1;4,1;5,1;6,1;7,1;8,1;9,1;10,1;11,1;12,1;13,1;14,1;15,1;16,1;17,1;18,1;19,1;20,1;21,1;22,1;23,1;24,1;25,1;26,1;27,1;28,1;29,1;30,1;31,1;32,1;33,1;34,1;35,1;36,1;37,1;38,1;39,1;40,1;41,1;42,1;43,1;44,1;45,1;46,1;47,1;48,1;49,1;50,1;51,1;52,1;53,1;54,1;55,1;56,1;57,1;58,1;59,1;60,1;61,1;62,1;63,1;64,1;65,1;66,1;67,1;68,1;69,1;70,1;71,1;72,1;73,1;74,1;75,1;76,1;77,1;78,1;79,1;80,1;81,1;82,1;83,1;84,1;85,1;86,1;87,1;88,1;89,1;90,1;91,1;92,1;93,1;94,1;95,1;96,1;97,1;98,1;99,1;100,1;101,1;102,1;103,1;104,1;105,1;106,1;107,1;108,1;109,1;110,1;100,1;111,1;112,1;113,1;114,1;115,1;116,1;117,1;118,1;119,1;120,1})
=SELECT.LAST.CELL()
mLastRow=ROW(SELECTION())
=SELECT("R1C1")
m1stRow=ROW(SELECTION())
=SELECT("C1")
=EDIT.DELETE(1)
=SELECT("C2:C10")
=EDIT.DELETE(1)
=SELECT("C3:C15")
=EDIT.DELETE(1)
=SELECT("C5")
=EDIT.DELETE(1)
=SELECT("C6:C7")
=EDIT.DELETE(1)
=SELECT("C7:C21")
=EDIT.DELETE(1)
=SELECT("C8:C32")
=EDIT.DELETE(1)
=SELECT("C9:C100")
=EDIT.DELETE(1)
=SELECT("C2")
=FORMULA.REPLACE("Some text to replace","With this bit of text",1,2,FALSE)
=SELECT("C1")
=INSERT(4)
=SELECT("C4")
=CUT()
=SELECT("C1")
=PASTE()
=SELECT("C4")
=EDIT.DELETE(1)
=SELECT("C3")
=INSERT(4)
=SELECT("C7")
=CUT()
=SELECT("C3")
=PASTE()
=SELECT("C7")
=EDIT.DELETE(1)
=SELECT("C7")
=CUT()
=SELECT("C9")
=PASTE()
=SELECT("C4")
=CUT()
=SELECT("C7")
=PASTE()
=SELECT("C4")
=EDIT.DELETE(1)
=SELECT("C2")
=FORMAT.NUMBER("0000000000")
=SELECT("C4")
FORMAT.NUMBER("000000000000000")
=SELECT("C7")
=FORMAT.NUMBER("0000")
=SELECT("R1:R"&TEXT(mLastRow,0))
=COLUMN.WIDTH(,"R1:R"&TEXT(mLastRow,0),,3)
=ALIGNMENT(2,FALSE,3,0,FALSE)
=SORT(1,"R1C1",1,,,,,0,1,FALSE)
=SELECT("R1")
=INSERT(2)
mLastRow=mLastRow+1
=SELECT("R1C1")
=FORMULA("Date")
=SELECT("RC[1]")
=FORMULA("A Number")
=SELECT("RC[1]")
=FORMULA("Origin")
=SELECT("RC[1]")
=FORMULA("B Number")
=SELECT("RC[1]")
=FORMULA("Destination")
=SELECT("RC[1]")
=FORMULA("Type of Service")
=SELECT("RC[1]")
=FORMULA("Duration")
=SELECT("RC[1]")
=FORMULA("Tariff")
=SELECT("C2")
=INSERT(1)
=INSERT(1)
=SELECT("R1C2")
=FORMULA("Date")
=SELECT("R1C3")
=FORMULA("Time")
=SELECT("R2C2")
=FORMULA("=MID(RC[-1],7,2)&""/""&MID(RC[-1],5,2)&""/""&MID(RC[-1],1,4)")
=SELECT("R2C3")
=FORMULA("=MID(RC[-2],9,2)&"":""&MID(RC[-2],11,2)&"":""&MID(RC[-2],13,2)")
=SELECT("R2C2:R"&TEXT(mLastRow,0)&"C2")
=FILL.DOWN()
=SELECT("R2C3:R"&TEXT(mLastRow,0)&"C3")
=FILL.DOWN()
=SELECT("R2C2:R"&TEXT(mLastRow,0)&"C5","R"&TEXT(mLastRow,0)&"C3")
=COPY()
=PASTE.SPECIAL(3,1,FALSE,FALSE)
=SELECT("C1")
=EDIT.DELETE(1)
=COLUMN.WIDTH(,"R1:R"&TEXT(mLastRow,0),,3)
=SET.PRINT.TITLES("R1","")
=SET.PRINT.AREA("")
=PAGE.SETUP("&A","Page &P",0.75,0.75,1,1,FALSE,FALSE,FALSE,FALSE,2,,90,1,1,FALSE)
=SELECT("R2C5")
=FOR("counter",1,mLastRow,1)
mCell_Content=GET.FORMULA(ACTIVE.CELL())
=IF(LEN(mCell_Content)=9,FORMAT.NUMBER("0#########"))
=IF(LEN(mCell_Content)>10,FORMAT.NUMBER("00####################"))
=SELECT("R[1]C")
=NEXT()
=RETURN()