Hi Guys,
Iam starting of on a new project in my VBA career, instead of just hitting it and building a "small book" of subs and functions, I want to start of by asking some general questions so that I end up with a code as clean as possible. I am basically looking for the quickest way to list values from parts of rows based on certain cell values. My problem is that the location of these cell values (and their headers) change from time to time.
The picture below shows a draft on the layout, the green areas will change and has to be dynamic in size while the light blue and gray areas will be fixed in width. However, if I add a Theme (green area) the blue/green area will shift one column to the right. I guess I can solve that with named ranges or with the help of "FIND" vba code perhaps?
The sheet is used for trading positions, and the Grey area will contain the positions, ive put in 3 random ones for the example (Cell AH4-8).
The functionality I need is to be able to sort/show in different ways the positions(gray area) based on Sub Themes, Themes, Assets, Type(thematic, event, tactical) and direction(bull/bear) as I choose. The different "sortings" will be in separate sheets.
So i would for instance have a sheet where i just sorted all the positions based on theme, it would looke someting like this: Sheetname: THEMES
REFALTION
EURUSD xxx xxx xxx
SP500 xxx xxx xxx
Totals: xxx xxx xxx
DECOUPLING
ZFc1 xxx xxx xxx
Totals: xxx xxx xxx
EUROPE
EURUSD xxx xxx xxx
Totals: xxx xxx xxx
ASIA
SP500 xxx xxx xxx
ZFc1 xxx xxx xxx
Totals: xxx xxx xxx
Today we use a coding that is coded with absolute references that is I cannot change any elements of the sheets without having to change code for hours. And the code we use is utterly slow. (15 seconds to update sheets, on a Intel Xeon Hexa Core (6x2.8Ghz with 8GB DDR machine...)
I think the code is particular slow as the old sheet use only two column and "1 to 9" and "a to z" so that it will look through all rows looking for 1A, then 2A, 3A etc til its comes to 10Z before its done. I imagine how it is proposed here will be quicker?
Questions:
1. Structure
I guess this isnt to easy to answer, but how should I ideally cope with the structure? I imagine a structure where I have one sub that reads the info given 1-6 inputs. I.E:
Sub Sort(conSubTheme, conTheme)
Sub Sort(conAsset, conType, conDirection, conPosition)
etc?
Is possible as here to have a variable number of inputs to a sub? Or will it be overly complicated? Any smart ideas?
2. "Active Themes"
How can I easiest identify the Active Themes and sub themes? And give them a number? I assume I should have a number to facilitate the loop to next theme. So that for instance conSubTheme1 is column A, etc, and conSubTheme9 is column I while conTheme1 is column J, and conTheme2 is column K and conTheme5 is column N.
The code will have to read row "xx"4 to A4, and ideally "group" these into Themes and Sub themes. I can to facilitate this either use "T" or "S" for instance in the cell above a theme name. I.e Cell a3 = S and a4=Long Dollar so i know Long Dollar is a Sub Theme and not a Theme or Asset for that sake?
I would prefer to do this without the "S/T/A" etc.
3. Active cell or "ranges/offset"
What's quickest in terms of code? Looping using ActiveCell(0,+1) etc or Range("DATA").Offset(0,+1) ? Am I imagining this or is the Range.offset approach quicker? (i have screenupdating set to false)
4. Hierarchical listing
What would be the best way to sort on Themes (column J4-Q4) then bull/bear (column AD/AE) for instance? When I say sort I mean a code that will group the bull and bear positions under each Theme. (and if there is no positions in that theme do not list the theme.
Historically I did this with a "conditional write sub", which i gave conditions in the name. that is to sort Theme 1 bull and then bear i would do
sub WriteData(Theme1,bull)
sub WriteData(Theme1,bear)
Or perhaps something like this:
For i 0 to 5 do
sub write data("Theme" & i ,bull)
sub write data("Theme" & i ,bear)
I would greatly appreciate some advice, and I will repost with code as soon as i have started on the new code as i understand it might be easier to give advice that way.
Iam starting of on a new project in my VBA career, instead of just hitting it and building a "small book" of subs and functions, I want to start of by asking some general questions so that I end up with a code as clean as possible. I am basically looking for the quickest way to list values from parts of rows based on certain cell values. My problem is that the location of these cell values (and their headers) change from time to time.
The picture below shows a draft on the layout, the green areas will change and has to be dynamic in size while the light blue and gray areas will be fixed in width. However, if I add a Theme (green area) the blue/green area will shift one column to the right. I guess I can solve that with named ranges or with the help of "FIND" vba code perhaps?
The sheet is used for trading positions, and the Grey area will contain the positions, ive put in 3 random ones for the example (Cell AH4-8).
The functionality I need is to be able to sort/show in different ways the positions(gray area) based on Sub Themes, Themes, Assets, Type(thematic, event, tactical) and direction(bull/bear) as I choose. The different "sortings" will be in separate sheets.
So i would for instance have a sheet where i just sorted all the positions based on theme, it would looke someting like this: Sheetname: THEMES
REFALTION
EURUSD xxx xxx xxx
SP500 xxx xxx xxx
Totals: xxx xxx xxx
DECOUPLING
ZFc1 xxx xxx xxx
Totals: xxx xxx xxx
EUROPE
EURUSD xxx xxx xxx
Totals: xxx xxx xxx
ASIA
SP500 xxx xxx xxx
ZFc1 xxx xxx xxx
Totals: xxx xxx xxx
Today we use a coding that is coded with absolute references that is I cannot change any elements of the sheets without having to change code for hours. And the code we use is utterly slow. (15 seconds to update sheets, on a Intel Xeon Hexa Core (6x2.8Ghz with 8GB DDR machine...)
I think the code is particular slow as the old sheet use only two column and "1 to 9" and "a to z" so that it will look through all rows looking for 1A, then 2A, 3A etc til its comes to 10Z before its done. I imagine how it is proposed here will be quicker?
Questions:
1. Structure
I guess this isnt to easy to answer, but how should I ideally cope with the structure? I imagine a structure where I have one sub that reads the info given 1-6 inputs. I.E:
Sub Sort(conSubTheme, conTheme)
Sub Sort(conAsset, conType, conDirection, conPosition)
etc?
Is possible as here to have a variable number of inputs to a sub? Or will it be overly complicated? Any smart ideas?
2. "Active Themes"
How can I easiest identify the Active Themes and sub themes? And give them a number? I assume I should have a number to facilitate the loop to next theme. So that for instance conSubTheme1 is column A, etc, and conSubTheme9 is column I while conTheme1 is column J, and conTheme2 is column K and conTheme5 is column N.
The code will have to read row "xx"4 to A4, and ideally "group" these into Themes and Sub themes. I can to facilitate this either use "T" or "S" for instance in the cell above a theme name. I.e Cell a3 = S and a4=Long Dollar so i know Long Dollar is a Sub Theme and not a Theme or Asset for that sake?
I would prefer to do this without the "S/T/A" etc.
3. Active cell or "ranges/offset"
What's quickest in terms of code? Looping using ActiveCell(0,+1) etc or Range("DATA").Offset(0,+1) ? Am I imagining this or is the Range.offset approach quicker? (i have screenupdating set to false)
4. Hierarchical listing
What would be the best way to sort on Themes (column J4-Q4) then bull/bear (column AD/AE) for instance? When I say sort I mean a code that will group the bull and bear positions under each Theme. (and if there is no positions in that theme do not list the theme.
Historically I did this with a "conditional write sub", which i gave conditions in the name. that is to sort Theme 1 bull and then bear i would do
sub WriteData(Theme1,bull)
sub WriteData(Theme1,bear)
Or perhaps something like this:
For i 0 to 5 do
sub write data("Theme" & i ,bull)
sub write data("Theme" & i ,bear)
I would greatly appreciate some advice, and I will repost with code as soon as i have started on the new code as i understand it might be easier to give advice that way.