VBA code very difficult to understand Help needed

[hamadsultan]

Hi Experts;
History:
I have a work book that contains 9 number of sheets but two are very important and i use excell 2007 to open it. I found some user defind function in a module which use in the Main worksheet. One of function i posted here also along with the how the programmer use in formula bar.
First code is here

Function DINTERP2(TAMB, TAMBLOW, TAMBHIGH, PWR, NX, NY, [COLOR=red]TXLOW, TYLOW[/COLOR], TXHIGH, TYHIGH, NFNC)
    Call SINTRP2(PWR, ANSLOW, NX, NY, TXLOW, TYLOW, NFNC, 1)
    Call SINTRP2(PWR, ANSHIGH, NX, NY, TXHIGH, TYHIGH, NFNC, 1)
    DINTERP2 = ANSLOW + ((TAMB - TAMBLOW) / (TAMBHIGH - TAMBLOW)) * ([COLOR=red]ANSHIGH - ANSLOW[/COLOR])
End Function
 
Sub SINTRP2(XN, ANS, NX, NY, TX, TY, NFNC, NDEG)
    NNDEG = NDEG
    If (NX > NDEG) Then GoTo LABLE2
    MsgBox "TABLE TOO SMALL FOR DESIRED DEGREE OF FIT"
    GoTo LABLEEND
LABLE2:
    If ([COLOR=red]XN < TX(1))[/COLOR] Then GoTo LABLE10
    If (XN > TX(NX)) Then GoTo LABLE20
    GoTo LABLE30

Now the programmer use it as;

=IF(C$13+DATA!F5=0,SINTERP2(E$109,81,11,FLTAMB,OPTDATA,9,1),DINTERP2(E$109,J$69,J$73,E$52,20,9,INDIRECT(J$71),INDIRECT(J$72),INDIRECT(J$75),INDIRECT(J$76),4))*F34

Now I want to undestand the following :
1.ANSLOW and ANSHIGH is used in Dinterp2 function it dfines here and use here i search all the sheets in that workbook they are only in this method. What value it used and where the values come here?
2.The code i copy from the formula bar u see INdirect(J71) and (J72) its Strings come from as J71=J70&"PWR" and same in J72.
But in the function of DINTERP2 its assign TXLOW and TYLow. When SINTRP2 call it used as TX and TY if u see programmer used TX(1) what it means?
Please help me to understand this code.
I pray to God help all the guys who help me always.
Regards

 

[p45cal]

ANSLOW andANSHIGH could be a global variable of any kind, it could also be nothing at all when the SINTRP2 is called, but that sub could assign it a value, but you don't include all of the SINTRP2so I can't say.
ANSHIGH and ANSLOW become ANS when SINTRP2 is entered, but you don't show what happens to ANS because the code is cut OFF.

What you need to do is make sure the Locals pane is visible (Alt+V, S), put a break (F9) on the line which starts Function DINTERP2, then step through the code with F8 watching the values in the Locals pane.

 

[hamadsultan]

Hi friends;
I upload the two images named Step1 and step2. In step 2 i show a selected cell and in the formula bar i highlight the value OPTDATA when i press F9 it shows a huge array but i am unable to find where it is and how it excute and fill which is shown in step1 image.
Plz guide me how i understand that.

Please help me.

Here r the complete SINTRP2 procedure what happen with ANS and i asure u ANSLOW and ANSHIGH never define throught complete code. It used only here i mentioned above.
Sub SINTRP2(XN, ANS, NX, NY, TX, TY, NFNC, NDEG)
    NNDEG = NDEG
    If (NX > NDEG) Then GoTo LABLE2
    MsgBox "TABLE TOO SMALL FOR DESIRED DEGREE OF FIT"
    GoTo LABLEEND
LABLE2:
    If (XN < TX(1)) Then GoTo LABLE10
    If (XN > TX(NX)) Then GoTo LABLE20
    GoTo LABLE30
LABLE10:
    IJ = 1
    If (NNDEG > 2) Then NNDEG = 2
    If (((TX(1) - XN) / (TX(2) - TX(1)) - 2) > 0) Then GoTo LABLEEND2
    GoTo LABLE100
LABLE20:
    If (NNDEG > 2) Then NNDEG = 2
    IJ = NX - NNDEG
    If (((XN - TX(NX)) / (TX(NX) - TX(NX - 1)) - 2) > 0) Then GoTo LABLEEND2
    GoTo LABLE100
LABLE30:
    For i = 1 To NX Step 1
    k = i
    If ((XN - TX(k))) < 0 Then GoTo LABLE60
    If ((XN - TX(k))) = 0 Then GoTo LABLE50
    Next i
    GoTo LABLE60
LABLE50:
    ANS = TY(k, NFNC)
    GoTo LABLEEND:
LABLE60:
    IJ = Int(k - (NNDEG + 1) / 2)
    If (IJ > 0) Then GoTo LABLE80
    IJ = 1
    GoTo LABLE100
LABLE80:
    If ((NX - IJ - NNDEG) >= 0) Then GoTo LABLE100
    IJ = NX - NNDEG
LABLE100:
    XSUM = 0
    PRODCT = 1
    N = IJ + NNDEG
    For j = IJ To N Step 1
    For i = IJ To N Step 1
    If (i = j) Then GoTo LABLE110
    If ((TX(j) - TX(i)) = 0) Then TX(j) = 1.001 * TX(i)
    PRODCT = PRODCT * (XN - TX(i)) / (TX(j) - TX(i))
LABLE110:
    Next i
    XSUM = XSUM + PRODCT * TY(j, NFNC)
    PRODCT = 1
    Next j
    ANS = XSUM
    GoTo LABLEEND
    
LABLEEND2:
    'MsgBox "TRYING TO EXTRAPOLATE TOO FAR IN SINTRP"
    ANS = 0
LABLEEND:
End Sub

 

[p45cal]

As I said; " it could also be nothing at all when the SINTRP2 is called, but that sub could assign it a value". So when SINTRP2 is called, ANSHIGH/ANSHIGH (nothing) become ANS, and later in the code ANS is assigned a value (ANS = XSUM or ANS = 0). Conversely, when the execution returns to the calling module where it was called, ANSHIGH/ANSLOW take on the value of ANS.

OPTDATA is very probably a named range; if you press F5 (when looking at the workbook - not the VEB) and type in OPTDATA it should show you where it is.

Did you not try to follow the values of variables in the Locals pane?

 

[hamadsultan]

Hi;
Yes i try Local pane and i found ans high and ans low values. Thanks for it.
In the workbook whch cell i select and press f5 any emty cell and that Particular cell?
Regards

 

[p45cal]

whch cell i select and press f5 any emty cell and that Particular cell?

It doesn't matter at all, as long as you're in the same workbook.

 

[hamadsultan]

Hi;
Thanks for guidelines at every step. Can u guide me how i define the range with name as i discuss in last post, For example
OPTDATA how the programmer define it?
Thanks

 

[p45cal]

In Excel, on the Formulas tab, there's a Name Manager, bring it up and highlight OPTDATA and take a screenshot (or quote the formula here).
It could have got there programmatically or have been manually input. To see if it's programmatically done, in the VBE, do a search (Ctrl+F) within the Project for OPTDATA. If you find it (it might be in more than one place), copy the codes here.

 

[hamadsultan]

Hi;
Thanks, I follow ur instructions and found that OPTDATA is a range from another sheet C22 to M122.
I attach a image here. I press F9 and when press F8 its not run. How can i run this function to see the values step by step? Workbook is already open.

regards

 

[p45cal]

It will only run when called from another module, or when used in a worksheet formula. This is because it has arguments which need to be supplied (XN,NX etc.).