Excel Sheets

[risk99]

Hi! Is it feasible to generate over 500 sheets in Excel? I know that Excel can handle unlimited numbers of sheets as long as your computer has the capacity for them. But, I want to konw if it's easy to manage those sheets? How can you name them automatically? and what's the best way to find the sheet that you want? THANK YOU VERY MUCH!!!

 

[SteveBausch]

I don't have enough experience/brains to say if it's a good idea, but I did run some tests for you.

Worksheets.Add Count:=250

was about as many I could add at any one time.

750 sheets cumulative, took 20 seconds to save. It loads pretty quick, however.

13 megabytes of drive space.

Help Says:
Sheets in a workbook Limited by available memory (default is 3 sheets)


The Test System was a P4 2.53 + with 256 MB, 120 gb


For naming them other than Sheet1, Sheet2, you could use a loop to change to your custom name, and tack on a numerical suffix. Custom1, Custom2, Custom3, etc.

Or, if you have a worksheet or file with the desired sheet names, just loop through that.

Accessing them? The sky is the limit. You could create a custom GoTo dialog, loading all the names into a ListBox. If there was a heirarchy of sheets, you could use a TreeView.


I cannot vouch for the practicality of any of this.

 

[risk99]

Steve,

THANK YOU VERY MUCH FOR YOU QUICK REPLY!!! I understand your advice pretty well...but, the thing is that I'm just a beginner in Excel VBA, I'm still clueless how to start this project If you have any sample codes for me to look at, I'll be very appreciated!!!

Here is what exectly I want to do:
*I have the following VBA Matrix functions (they are all working well now): F1, F2

1. Generate 500 random samples -- one(1) sample per Sheet and save them in File1.xls. ( I can generate this one by one, no problem. but the key is to do this automatically, and put them onto each Sheet)
2. Obtain 500 matrices by applying Function, F1 to each of the 500 samples from File1.xls, and save these 500 matrices in File2.xls.
3. Generate another 500 Sheets by applying Function, F2 to the 500 matrices from File2.xls and save these 500 sheets into file3.xls.

So, is it possible to manipulate all of the above at once? I prepare to leave my computer to run all of these for couple days ( I have Win2K, P3, 600MHZ, and 320M of RAM)

THANK YOU FOR ALL OF YOUR HELPS!!!

 

[SteveBausch]

I can't help you with most of your query, it's over my head.
Other members can help. We both need the resident expert's responses to this thread.

You need to furnish additional detail.

What is a "random sample"? A number? Group of numbers?

What sort of matrice? How many dimensions?

What are the formulas for the functions?

Is there any sorting?

 

[risk99]

Steve,

I'm sorry for throwing so many stuff to you at once. Okay, let's do this one at a time. My objective is to do bootstrap resembling. For example, I have ten(10) years of anual returns of two stocks, A and B, as listed below:

Year A B
1 rA1 rB1
2 rA2 rB2
. . .
. . .
10 rA10 rB10

Let me name the above table "data" (the Year, A, and B are not included), which is a 10x3 matrix (A2:C11).

Let's create a new sheet:

Year A B Random
1 "=VLOOK($D2,data,2)" "=VLOOK($D2,data,3)" "##"
2 "=VLOOK($D3,data,2)" "=VLOOK($D3,data,3)" "##"
. . . .
. . . .
10 "=VLOOK($D11,data,2)" "=VLOOK($D11,data,3)" "##"

NOTE: "##" is "=RANDBETWEEN(1,10)" (the numbers in the "Random" colum can be repeated between 1 and 10).

Then I name (B1:C11) "Rand1" (yes, the Year and Random colums are not included). The Sheet for "Rand1" is done! And I need to do this over and over for 500 times to create "Rand1" to "Rand500". Hope I have explained clear enough...and please let me know if you need other info the start the first process. THANK YOU VERY MUCH FOR YOUR TIME, STEVE!!!

 

[andrew299]

The number of sheets should not be a problem I have a code that generates 400+ sheets regularly. It takes about half an hour to run and save on my computer which is pretty good. The sheets are created and named after specific codes in a column and therefore I can scroll through these to specify the sheet names. I am not to hot on vlookup tables but if I understand correclty you will need something like the code below


for x = 1 to 500

sheets.add
sheets.name="Rand" &x

do you code bit

next x


dont know if this helps a bit
Andrew299



It may have hit every branch on its way out of the ugly tree, but hey! It works. (but don't quote me on that)

 

[SteveBausch]

Risk99,

Little bit lost on your cell values. What are they? I don't see an equals sign, so I (and Excel) won't understand them as formula.

I am not much of a cell formula wizard, I tend to do everything with code. Do you need cell formula, or just the results?

Bootstrap resembling is a stock term, but it's not a stock term for me. Can you provide some links for information?

I would also like to see a working workbook. I assume you have one, but you want to make the data-gathering automated and you want to increase the number of worksheets.

You still haven't defined Function1 and Function2.

I am never worried about the programming skillset of the people I help. I do get worried when the presentation is not structured.

Could you create a new post, with all information? The information has to be structured, and all details need to be defined and explained. The process has to be linear, and include any decisions the process needs.

Thanks so much,
Steve

 

[risk99]

Andrew299 and Steve, THANK YOU VERY MUCH FOR YOUR HELPS!!!
I'm sorry for the confusion that I've created. Let me try this again.

Let's make this more simple. I have provided two(2) tables from the previous post:

Table 1 -- It's the raw data of the ten year anual returns for Stock A and B. "rA1" is representing Year 1 return for Stock A, "rB1" is representing Year 1 return for Stock B...etc. You don't need "=" for those cells since they are my raw data. Of course, "rB1" is just a symble, it should be listed in a numerical way such as "0.09" ...you got my point.

Table 2 -- It's the bootstrap resembled data for my Stock returns by using "VLook" function and "Randbetween" function. (Please read my Table 2 carefully, I thought I've listed everything in detail...oh well, except the columns for "B" and "Random" are a little off to the left due to some format problems )

MAIN QUESTION: HOW DO I PUT MY "VLOOK" FUNCTION/FORMULA INTO VBA, SO I CAN GET THE SAME RESULTS FOR TABLE 2 AS I DID MANUALLY.

Steve, I'm sorry if it's still confusing...it it is...is it possible that I can send you my Workbook? Thanks!!!

 

[Nikita6003]

Hiya Risk99

There are two ways of using formulas in code:
1. If you need the actual formula to be entered in a cell use the .Formula property of that cell: e.g. to get the VLOOKUP (it's VLOOKUP you want if you're using Excel to lookup data in a vertically oriented table) into cell D2 use

    Dim l_wkbWorkbook As Workbook
    Dim l_wksReportSheet As Worksheet
    
    Dim l_lRow As Long
    
    Set l_wkbWorkbook = Application.Workbooks.Open("Y:\Data\Excel\TekTips.xls")
    Set l_wksReportSheet = l_wkbWorkbook.Sheets.Add
    
    'Create header
    l_wksReportSheet.Cells(1, 2) = "Year"
    l_wksReportSheet.Cells(1, 2).Formula = "A"
    l_wksReportSheet.Cells(1, 3).Formula = "B"
    
    'Fill all cells with your formulas (this'll fill all three columns at the same time)
    For l_lRow = 2 To 11
        l_wksReportSheet.Cells(l_lRow, 2) = l_lRow - 1
        'Add FALSE if you want excel to look up the ACTUAL value
        'this'll return #N/A if the lookup calue is not in the table
        'Omit FALSE if you want xl to lookup the NEAREST available value to your lookup value
        l_wksReportSheet.Cells(l_lRow, 2).Formula = "=VLOOKUP($D" & l_lRow & ",data,2,False)"
        l_wksReportSheet.Cells(l_lRow, 3).Formula = "=VLOOKUP($D" & l_lRow & ",data,3,False)"
    Next l_lRow
    
    l_wkbWorkbook.Save
    
    Set l_wksReportSheet = Nothing
    Set l_wkbWorkbook = Nothing

2. if you just want the result in a cell, try using

Application.Worksheetfunction.VLookup

with the same paramteres you'd use in a worksheet. This'll calculate the result & stick it in any cell of your choosing

HTH!


Cheers
Nikki
Look, mommy, I'm flying!

 

[Nikita6003]

eek - too much copy/paste in the code!

change

l_wksReportSheet.Cells(1, 2) = "Year"

to

l_wksReportSheet.Cells(1, [b]1[/b]) = "Year"

change

l_wksReportSheet.Cells(l_lRow, 2) = l_lRow - 1

to

l_wksReportSheet.Cells(l_lRow, [b]1[/b]) = l_lRow - 1

Cheers
Nikki
Look, mommy, I'm flying!