Simple loop help!

[rarpro]

Hi all,

I have a very large code -probably very inefficient as I am not an expert on VBA- and i got the error of 'too large code", so I split the data on several sub routines. however, I added more code on the first subroutine which is a set of equations within a couple of loops, and again got into the error of too large to compile. So I realize that I might be able to shorten the code because there are many equations that are very similar. I'm posting a small example of the code. I think the code could be shortened, but I tried several ways with no luck. I would really appreciate any help with this.

regards,

[vba]

For TP = 1 To NT

'THE DATA FOR LOOP TP TO NT IS IN SHEET TY
    Sheets("TY").Select
    Range("A" & TP + 1).Select
    TNO(TP) = ActiveCell.Value
  
        
        For REGNO = 1 To NREG
          
          'FIRST READS DATA FROM SEVERAL SHEETS
          Sheets("XF1").Select
            Range("B" & REGNO + 1).Select
            P11(REGNO) = ActiveCell.Value
            ActiveCell.Offset(0, 1).Select
            P12(REGNO) = ActiveCell.Value
            ActiveCell.Offset(0, 1).Select
            P13(REGNO) = ActiveCell.Value
            ActiveCell.Offset(0, 1).Select
            P13(REGNO) = ActiveCell.Value
            ActiveCell.Offset(0, 1).Select
            P15(REGNO) = ActiveCell.Value
            ActiveCell.Offset(0, 1).Select
            '....AND SO ON
            
            Sheets("YF1").Select
            Range("B" & REGNO + 1).Select
            C11(REGNO) = ActiveCell.Value
            ActiveCell.Offset(0, 1).Select
            C12(REGNO) = ActiveCell.Value
               'AND SO ON...THEN AFTER READING VALUES FROM THE SHEETS, THE ESTIMATION
               'OF SOME EQUATIONS LIKE:
               
                PK11(REGNO) = (P11(REGNO) * Y11(REGNO)) - C11(REGNO)
                PK12(REGNO) = (P12(REGNO) * Y12(REGNO)) - C12(REGNO)
                PK13(REGNO) = (P13(REGNO) * Y13(REGNO)) - C13(REGNO)
                PK14(REGNO) = (P14(REGNO) * Y14(REGNO)) - C14(REGNO)
                PK15(REGNO) = (P15(REGNO) * Y15(REGNO)) - C15(REGNO)
                PK16(REGNO) = (P16(REGNO) * Y16(REGNO)) - C16(REGNO)
                PK17(REGNO) = (P17(REGNO) * Y17(REGNO)) - C17(REGNO)
                PK18(REGNO) = (P18(REGNO) * Y18(REGNO)) - C18(REGNO)
                
                
                PK1(REGNO) = (PK11(REGNO) * W11(REGNO)) + (PK12(REGNO) * W12(REGNO)) + (PK13(REGNO) * W13(REGNO)) + (PK14(REGNO) * W14(REGNO) _
                        + PK15(REGNO) * W15(REGNO)) + (PK16(REGNO) * W16(REGNO)) + (PK17(REGNO) * W17(REGNO)) + (PK18(REGNO) * W18(REGNO))
                
                V11(REGNO) = (S11(REGNO)) ^ 2
                V12(REGNO) = (S12(REGNO)) ^ 2
                V13(REGNO) = (S13(REGNO)) ^ 2
                V14(REGNO) = (S14(REGNO)) ^ 2
                V15(REGNO) = (S15(REGNO)) ^ 2
                V16(REGNO) = (S16(REGNO)) ^ 2
                V17(REGNO) = (S17(REGNO)) ^ 2
                V18(REGNO) = (S18(REGNO)) ^ 2
           
                SV1(REGNO) = (V11(REGNO) * W11(REGNO) ^ 2 + V12(REGNO) * W12(REGNO) ^ 2 + V13(REGNO) * W13(REGNO) ^ 2 + V14(REGNO) * W14(REGNO) ^ 2 _
                          + V15(REGNO) * W15(REGNO) ^ 2 + V16(REGNO) * W16(REGNO) ^ 2 + V17(REGNO) * W17(REGNO) ^ 2 + V18(REGNO) * W18(REGNO) ^ 2) _
                          + (RHO1(REGNO) * 2) * (W11(REGNO) * W12(REGNO) * S11(REGNO) * S12(REGNO) _
                          + W11(REGNO) * W13(REGNO) * S11(REGNO) * S13(REGNO) + W11(REGNO) * W14(REGNO) * S11(REGNO) * S14(REGNO) _
                          + W11(REGNO) * W15(REGNO) * S11(REGNO) * S15(REGNO) + W11(REGNO) * W16(REGNO) * S11(REGNO) * S16(REGNO) _
                          + W11(REGNO) * W17(REGNO) * S11(REGNO) * S17(REGNO) + W11(REGNO) * W18(REGNO) * S11(REGNO) * S18(REGNO) _
                          + W12(REGNO) * W13(REGNO) * S12(REGNO) * S13(REGNO) + W12(REGNO) * W14(REGNO) * S12(REGNO) * S14(REGNO) _
                          + W12(REGNO) * W15(REGNO) * S12(REGNO) * S15(REGNO) + W12(REGNO) * W16(REGNO) * S12(REGNO) * S16(REGNO) _
                          + W12(REGNO) * W17(REGNO) * S12(REGNO) * S17(REGNO) + W12(REGNO) * W18(REGNO) * S12(REGNO) * S18(REGNO) _
                          + W13(REGNO) * W14(REGNO) * S13(REGNO) * S14(REGNO) + W13(REGNO) * W15(REGNO) * S13(REGNO) * S15(REGNO) _
                          + W13(REGNO) * W16(REGNO) * S13(REGNO) * S16(REGNO) + W13(REGNO) * W17(REGNO) * S13(REGNO) * S17(REGNO) _
                          + W13(REGNO) * W18(REGNO) * S13(REGNO) * S18(REGNO) + W14(REGNO) * W15(REGNO) * S14(REGNO) * S15(REGNO) _
                          + W14(REGNO) * W16(REGNO) * S14(REGNO) * S16(REGNO) + W14(REGNO) * W17(REGNO) * S14(REGNO) * S17(REGNO) _
                          + W14(REGNO) * W18(REGNO) * S14(REGNO) * S18(REGNO) + W15(REGNO) * W16(REGNO) * S15(REGNO) * S16(REGNO) _
                          + W15(REGNO) * W17(REGNO) * S15(REGNO) * S17(REGNO) + W15(REGNO) * W18(REGNO) * S15(REGNO) * S18(REGNO) _
                          + W16(REGNO) * W17(REGNO) * S16(REGNO) * S17(REGNO) + W16(REGNO) * W18(REGNO) * S16(REGNO) * S18(REGNO) _
                          + W17(REGNO) * W18(REGNO) * S17(REGNO) * S18(REGNO))

  Next REGNO
            Sheets("ax1").Select
            Range("b1").Select
Next TP
'then estimated values are written in a different sheet

[/vba]

[Blade Hunter]

I only got half way:

[vba]
'FIRST READS DATA FROM SEVERAL SHEETS
Sheets("XF1").Select
P11(REGNO) = Range("B" & REGNO + 1).Value 'Don't select things, its slow and not necesary
P12(REGNO) = Range("B" & REGNO + 1).Offset(0, 1).Value
P13(REGNO) = Range("B" & REGNO + 1).Offset(0, 2).Value
P13(REGNO) = Range("B" & REGNO + 1).Offset(0, 3).Value
P15(REGNO) = Range("B" & REGNO + 1).Offset(0, 4).Value
'....AND SO ON
'You can loop this and really condense it if you use a 2D array, something like P(num,REGNO) but you will need to change it EVERYWHERE

Sheets("YF1").Select
C11(REGNO) = Range("B" & REGNO + 1).Value
C12(REGNO) = Range("B" & REGNO + 1).Offset(0, 1).Value
'AND SO ON...THEN AFTER READING VALUES FROM THE SHEETS, THE ESTIMATION
[/vba]

You really need to move towards a 2D array and you can seriously get this down a LOT smaller

[rarpro]

Hi Blade Hunter,

Thanks for the quick reply. I think what you showed me will help me to reduce the size of the code. However, you are right, something like a 2D array will reduce even further..Could you please provide with an example how can I set up this? I know there will be a lot of changes required, but on the other hand the benefits will surplus that effort.

Thanks again!

rarpro

[Blade Hunter]

Sure, this is VERY crude as I just whacked it together but it should give you an idea of how you can use it.

[vba]
For TP = 1 To NT
Sheets("TY").Select
Range("A" & TP + 1).Select
TNO(TP) = ActiveCell.Value
For REGNO = 1 To NREG
Sheets("XF1").Select
For X = 11 To 15
P(X, REGNO) = Range("B" & REGNO + 1).Offset(0, X - 1).Value
Next
Sheets("YF1").Select
For X = 11 To 12
C(X, REGNO) = Range("B" & REGNO + 1).Offset(0, X - 1).Value
Next

For X = 11 To 18
PK(X, REGNO) = (P(X, REGNO) * Y(X, REGNO)) - C(X, REGNO)
Next
For X = 11 To 18
PK(1, REGNO) = PK(1, REGNO) + (PK(X, REGNO) * W(X, REGNO))
Next
For X = 1 To 18
V(X, REGNO) = (S(X, REGNO)) ^ 2
Next
For X = 11 To 18
SV(1, REGNO) = SV(1, REGNO) + V(X, REGNO) * W(X, REGNO) ^ 2
Next
SV(1, REGNO) = SV(1, REGNO) + (RHO(1, REGNO) * 2) * (W(11, REGNO) * W(12, REGNO) * S(11, REGNO) * S(12, REGNO))
For X = 11 To 17
For Y = 13 To 18
SV(1, REGNO) = SV(1, REGNO) + W(X, REGNO) * W(Y, REGNO) * S(X, REGNO) * S(Y, REGNO)
Next
Next
Next
Sheets("AX1").Select
Range("B1").Select
Next
[/vba]

Some of those loops can be combined but without knowing your data I kept the seperation you have in there.

Let me know if doesn't make sense and I will try to explain further.

Cheers

Dan

[rarpro]

Hi Dan,

This is great!..Yes I think i follow the idea and the code. I also see what you mean when you say that some of the loops can be combined, and I think you are right, for example all that are X=11 to 18 could be under the same loop.

I do have a couple questions tough:
1. I defined the variables at the beginning of the program as (for example):
Dim P11(), P12(), P13()...ETC
dIM c11(), C12(), C13)...ETC

then when I started the REGNO loop, I did:
Redim P11(NREG), P12(NREG), P13(NREG)...etc.

Using these 2d array, do I have to Dim the variables in a different way?
(I have about 300 variables..).
2. You showed me how to read variables without 'selecting'...could you please show me the code to write the variables to a sheet? here is a small example of how I currently have it:

this is within the regno loop:
[VBA]
Sheets("temp").Select
Range("a1").Select

FOR REGNO TO NREG
'code
' code and more code
' then my results go to:
Sheets("TEMP").Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = REGF(REGNO)
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = base
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = result1(REGNO
ActiveCell.Offset(0, -2).Select
next regno
[/VBA]
Thanks again for such a great help, very useful!.

regards,

rarpro

[Blade Hunter]

First I will answer the second question . Where ever you have Select followed by activecell or selection on the next line you can usually delete.

So you have range("A1").Select
Activecell.Formula = "Blade was here"

You can delete those bits and you end up with
So you have range("A1").Formula = "Blade was here"

This has done the same thing without the select.

[vba]
Sheets("TEMP").Select
ActiveCell.Offset(1, 0).Value = REGF(REGNO)
ActiveCell.Offset(1, 1).Value = base
ActiveCell.Offset(1, 2).Value = result1(REGNO)
[/vba]

Note the offset needs to sum as you are no longer moving around the page.

OK, Dimensionalising a 2d array:

This is a 1D array
1)
Dim Blade as variant 'Used where you don't know how many items you need and you are going to populate the array in one command
2)
Dim Blade () as string 'You don't know how many items but you will populate 1 at a time and probaly redim preserve to boost the limit as you need it
3)
Dim Blade(20,10) as string
This is saying that your dimension 1 will have 20 elements and your dimension 2 will have 10, this is 200 elements all up.

It is important to note, I am not 100% sure if you can redim a multi dimension array, if you can't then aim high, dimension it to a number that you would realistically never hit without going way over the top (don't Dim a million elements if your ceiling is most likely going to be 10)

For the purpose of 2D arrays you can think of it as a spreadsheet in memory, first dimension is rows and second is columns.

If you get in to 3 or more D arrays though it gets VERY hard to keep in your head, my largest was a 5D array (going back a number of years now though and I wouldn't do it again it was more coding it to prove I could, it was extremely hard to maintain and I ended up rewriting it more realistically anyway)


This:

Dim P11(), P12(), P13()...

Will become this:

Dim P(35,100) 'or something like that but you get the idea

[rarpro]

Very well explained..thanks again...I knew I should have come to this forum while ago. the truth is that I never studied VBA, just learned by doing, as a result many times the code may work but could be very inefficient as you can see.

Anyway, one more question, if may and I apologize for taking much of your time...but hopefully this is the last one...for one.

I was looking at your code with the loops in the example you gave me (see below) and I don't quite follow the offset part offset (0,X -1), why the "X" is in there?, what does it do to the offset? Thanks again!

[VBA]
For REGNO = 1 To NREG
Sheets("XF1").Select
For X = 11 To 15
P(X, REGNO) = Range("B" & REGNO + 1).Offset(0, X - 1).Value
Next
Sheets("YF1").Select
For X = 11 To 12
C(X, REGNO) = Range("B" & REGNO + 1).Offset(0, X - 1).Value
Next[/VBA]

[Blade Hunter]

Offset moves from your point of reference by row, column

Offset(0, X - 1).Value will be X-1 columns to the right of your point of reference: Range("B" & REGNO + 1) in this case.

Because the first reference in your code had no offset: P11(REGNO) = Range("B" & REGNO + 1).Value I need to parse an offset of 0,0 to it hence the X - 1 because X starts at 1, 1-1=0.

You could make your loop 10 to 14 and drop the -1 on the X instead of 11 to 15 but I think 11 to 15 gives a better indication of what you are doing.

Let me know if you need anything else.

Also, because you said you are just getting in to VBA, here is a tidbit I learned recently. When dimensionalising in Excel, don't bother using integer ever, Excel converts it to a long which is just a waste of CPU cycles when you can dim as a long, anything you would normaly dim as an integer, dim it as a long .

Cheers

Dan

[rarpro]

Thanks again..but if the loop starts at 11 (X=11 to 15) then the first value X takes is 11, isn't it? so in the offset part (x-1) wouldn't it be (11-1)?
Sorry if I missed a point here...
Also, after I run when I want to write in the sheet P11, P12, P13..etc, I should write it like this?
Sheets("TEMP").Select
ActiveCell.Offset(1, 0).Value = P(X, REGNO)
- but i think it doesn't work, may be:
ActiveCell.Offset(1, 0).Value = P(11, REGNO)
ActiveCell.Offset(1, 1).Value = P(12, REGNO)

I have bothered a lot now, and you have helped me tons already, so please feel free to stop..I understand how valuable is time.

Thanks a lot!

[rarpro]

I think this works:
For X = 11 To 18
YP(X, REGNO) = Range("B" & REGNO + 1).Offset(0, (X - 10) - 1).Value
Next

[Blade Hunter]

I think this works:
For X = 11 To 18
YP(X, REGNO) = Range("B" & REGNO + 1).Offset(0, (X - 10) - 1).Value
Next

Spot on, I missed that , no need for two arithmetic calculations on it though:

For X = 11 To 18
YP(X, REGNO) = Range("B" & REGNO + 1).Offset(0, X - 11).Value
Next

[rarpro]

And this works too!

ActiveCell.Offset(1, 0).Value = P(11, REGNO)
ActiveCell.Offset(1, 1).Value = P(12, REGNO)

Well, Thank you so much for your help. This is enough to keep me awake re-doing the code tonight.

Just to let you know, I have little experience with VBA, but I do know SAS, but I'm trying to change my SAS program to excel for people that can't afford to pay the expensive SAS license...so...again thanks this has been very useful..and I'm sure now that i will be back.

[Blade Hunter]

ActiveCell.Offset(1, 0).Value = P(11, REGNO)
ActiveCell.Offset(1, 1).Value = P(12, REGNO)

I have bothered a lot now, and you have helped me tons already, so please feel free to stop..I understand how valuable is time.

Thanks a lot!

Again, you are correct.

It's no bother, I am waiting for my wife to finish work for another half hour anyway

[Blade Hunter]

And this works too!

ActiveCell.Offset(1, 0).Value = P(11, REGNO)
ActiveCell.Offset(1, 1).Value = P(12, REGNO)

Well, Thank you so much for your help. This is enough to keep me awake re-doing the code tonight.

Just to let you know, I have little experience with VBA, but I do know SAS, but I'm trying to change my SAS program to excel for people that can't afford to pay the expensive SAS license...so...again thanks this has been very useful..and I'm sure now that i will be back.

No worries mate, always happy to help. Feel free to PM me a link if you post any more questions an they don't get answered . I don't always check the forums but I get an email when I get a PM

[rarpro]

Thanks again..and ...again I apologize for my ignorance..9also new to forums)..how do i post a PM here? just click on your name?

[rarpro]

Ok..Ok..no need to answer i just found out..Thanks a lot!