PDA

View Full Version : Need help with correct finding/sorting. Explanation and example file enlcosed.



Peskarik
10-22-2008, 02:25 PM
Hello, everyone!

I really need some help. :(
I have made an automated reporting tool (was asked to do that for some people at work). It is based on Excel. I have a file that reads in data from another Excel file (data file), then does a lot of analysis, makes plots, tables, etc.

I wrote the code to be very dynamic and flexible, no hard-coding. Still, I have a problem that I do not know how to deal with.

Let me explain:

in the main excel file I read in financial data for some funds and indices. Some of the funds belong to our firm, some are from other firms (our peers), and I have a couple of indices.

Also, one of the funds/indices is set to be the benchmark.
As of now, I hard-code the name of the benchmark fund. But the end user should not go into code and change the names at all! I want the end user to click a button, a couple of message boxes pop up asking for location of data file, for location of the output files, and what the benchmark fund should be (based on the names of funds the data for which I read in already), then code runs, producing excel file and powerpoint file with figures and tables.

What I need to do:
in the enclosed excel file, the first page contains names of funds/indices, as I read them in from data file. On the second sheet, I ordered them in such a way that first four names are funds from my firm, then come 3 indices, then come funds of peer companies.

How can I turn sheet 1 into sheet 2?

Optimal would be that once sheet one is created a box comes up, containing all the names in sheet 1, and next to each name there are four check boxes where one can mark if the name belongs to own company fund, to index, or to peer company fund, and also if particular fund/index is the benchmark. (Basically 5 columns in that box - column of names and 4 columns (named "own", "index", "peer", "benchmark") of empty check boxes). In the first 3 columns one should be able to check several boxes, while in the 4th ("benchmark") - only one.
Then, based on how boxes were checked a list like on sheet 2 should be produced and the name of benchmark fund should be save as a global constant so that it can be used across many code modules in the report-generating excel file.
I wonder if this is possible to make with Excel VBA? That would be optimal, because if the names would change the user can always select which ones are indices, which ones are peers etc.

If the above is not possible, then another solution would be to use .find() method with some key words. FOr example, all names containing "alpha" or "beta" should be specified as own firm's funds, all names with "index" or "investable" should be indices, the rest would be peer funds. This solution is not optimal, because name "Ex Managed USD" on the sheet belongs to own firm's funds, but does not contain key words "alpha" or "beta".
If the solution with the message box is not possible, how would I write the code that takes sheet 1 in enclosed file and produces sheet 2? In this code there will be .find() and .findnext() methods, obviously. And I also want the empty rows between the groups, just like in sheet 2.

SOrry for a lengthy description, but it is hard to explain the problem in a few words.
Please, could someone help me. I don't know how to do this :dunno :( , and that is the last thing I need to do with the file, except for copying tables/plots from Excel to PowerPoint (I think I can figure that out myself).

Thank you a lot in advance!!!
:help

mdmackillop
10-22-2008, 03:48 PM
As a start (no code to form sheet 2) . Any use?

Peskarik
10-23-2008, 12:40 AM
As a start (no code to form sheet 2) . Any use?

This looks very promising! :bow:
I see I have to learn about userforms. I wish I had more time, but it is a pressing matter now.

I hope I may ask for some of your time to help me with this problem of mine. I have following questions:

1) What is the fastest way to produce a user form like this (I have Excel 2003 SP2)? And how does it work? I see there is a small procedure in module 1 selecting a named range, and there is a command button in sheet1. How are these connected? :think:

2) How do I make the form to produce what is on sheet 2? Also, the name of the benchmark fund should be put into a public variable, how do I do that? Right now I have it hard-coded in the declaration section like this:
Public Const BenchmarkName As String = "Alpha Absolute Return (USD)"

3) Is it possible to make this userform dynamic, that is, conditional on the number of names in the list. So if I have 38 names, the box has 38 boxes (in that case I guess there will be 4 columns: names-boxes-names-boxes, each 18 rows tall). That is not priority, though, I could just create a form with 4 columns of 20 rows each and have it like that. I just like to make things nice, efficient, and dynamic. :curtsey:

4) The list of names (sheet1) is produced automatically when the code is run. There is one sheet (called "tab master") with some pre-filled columns and on that sheet there is a button, clicking which runs all the code modules I have.
So there is actually no empty sheet1 in the beginning, but once I click the button and data is read in sheet1 is automatically created and names are put in it. How can I make the user form to come up once sheet one is created? Is it possible even, or should I have user form somewhere in the background already in the initial empty file that only contains "tab master"?

Thank you in advance for your time and help!!!

/Sergey

Peskarik
10-23-2008, 01:13 PM
please, help. :(

mdmackillop
10-23-2008, 01:41 PM
1) I created dynamic named ranges which are used to populate the userform. With the form as is,
you can copy and paste multiple controls. They will be indexed automatically.
The button contains a command to open the form. You can see this in the Sheet Module code.
2) A bit of thought needed. What is the logic of the data on sheet 2? I think the benchmark
can be assigned to the public variable when it is selected.
3) Best to create a form with all the controls. These can be invisible and made visible for each item required.
4) The named range can be created by the button code before the userform is opened.

mdmackillop
10-23-2008, 01:46 PM
Re 3), you cannot assign code to dynamic controls, so hidden ones are better, as all code can be added to the form.

Peskarik
10-23-2008, 11:36 PM
1) I created dynamic named ranges which are used to populate the userform. With the form as is,
you can copy and paste multiple controls. They will be indexed automatically.
The button contains a command to open the form. You can see this in the Sheet Module code.
2) A bit of thought needed. What is the logic of the data on sheet 2? I think the benchmark
can be assigned to the public variable when it is selected.
3) Best to create a form with all the controls. These can be invisible and made visible for each item required.
4) The named range can be created by the button code before the userform is opened.

Thank you for that.

So, if I understand correctly, I create user form before I run the code. And I hide that form. Once I start the code and the range of names is produced, then I use "UserForm.show" command for the form to pop up. The code execution is automatically put on hold at that moment. Then the user selects "own", "index", "peer", or "benchmark" in the form and clicks a button (say "Continue") on the form and then the conditional list on sheet 2 is produced, and the name that was marked as "index" is assigned to a public variable, which is then used throughout the remaining code.
That is at least what I want to be done.

Still, the problem is I don't understand how to create the list in sheet2 after I have categorized everything in the user form, and how to assign the name to a public variable from the user form.

I have WROX Excel 2007 VBA Programmer's Reference book, but the chapter on user forms is very unclear. :(

Peskarik
10-29-2008, 09:01 AM
:hi:
MdMcKillop, thank you for your help! :friends:
With your example file I created my own form and coded around it, so now I have the sorted list the way I want it. :cloud9:

I could not find any information on code like:
...Me.Controls("CheckBox" & i).Value

I have a couple of books on VBA, one of them is Excel 2007 VBA, but none describes "me.controls" sentences. In Excel 2007 VBA "Me." appears only once in one example snippet.

Could not find much on internet either.

Could you, if possible, recommend me where I can read more about these "me.controls()" methods?

Thank you! :beerchug:

mdmackillop
10-29-2008, 11:00 AM
Don't know about a book, but it's basically simple.
Me refers to the "Container", Sheet, Userform or whatever that you are working with and contains objects.
Controls on the userform are indexed. so you could use Me.Controls(1) etc. but in the same way you can refer to cells as Cell(1,1) you can use the name of a range eg. Range("Data") If you have a set of names, you build the string. eg.

For i = 1 to 5
Range("Data" & i).Interior.colorindex = 6
Next

You're simply doing the same thing with the userform.

You can also loop through the controls such as

For each c in me.controls
If left(c.name,7) = "Textbox" then
'Do something
End if
Next