Can sub routines be performed in Excel

[mesteptoe]

Many years ago I wrote some structural analysis programmes in BBC Basic. I still use them but the hardware is now getting old and I am worried that, if it packs up, so do my calculations. The calcs. in BBC Basic include many subroutines which, basically, run the same calculation numerous times on an iterative basis until a solution is found, e.g. the highest point on a curve, the lowest point on a curve etc. etc.. This use FOR 1 to 300 and NEXT statements. Once a figure is obtained it might well be used by another part of the programme. The programme is also interactive, allowing me to choose a suitable timber beam for instance from a selection that the programme has listed as satisfying all the stress criteria. Once I specify a joist size the programme continues and provides a detailed calculation, based on the section size chosen.

Can Excel be programmed in this way?

Any help/ guidance much appreciated.

Graham

 

[VoG]

Hi and welcome to MrExcel.

What you described seems perfectly possible in Excel Visual Basic for Applications. I suggest that you invest in an introductory book such as http://www.dummies.com/WileyCDA/Dum...ramming-For-Dummies.productCd-0764574124.html

This is also good for learning VBA 'hands on' http://www.add-ins.com/vbhelp.htm and there are tutorials here http://www.datapigtechnologies.com/ExcelMain.htm

Edit: P.S. I have successfully transformed iterative programs from Fortran to VBA.

 

[Jonmo1]

Welcome to the board, And the world of Excel.

Yes, Excel can do stuff like that. You'll probably even find excel is more powerfull than what you were using before, though I'm not familiar with BBC Basic.

I am still amazed almost every day at the stuff excel can do. It's almost limitless.

There are a wide variety of built in Functions that you can use in formulas.
Just select a cell, and click Insert Function. You'll find a list of all built in functions available for various calculations.

Then there's VBA code (macros). That's where you can do the For / Next to loop until a certain criteria is met.

Here's just a very simple example of a VBA macro using a For / Next loop..

Type any Number from 1 to 1 million in a cell, say A1.
Then press ALT + F11 (that opens the VBA code Window)
Click Insert Module.
Paste this code

Sub Test()
Dim i as long
 
For i = 1 to 1000000
    If i = Range("A1").Value Then
        MsgBox "The Value in A1 is " & i
        Exit For
    End If
Next i
End Sub

Then close the VBA window
Click Tools - Macro - Macros
Select Test and click Run

Although this macro itself is probably pretty useless and stupid, it's just to show how a for / next loop works...
And for / next loops are just the tip of the iceburg of what you can do in VBA...

 

[mesteptoe]

Thanks Jonmo

But what is that routine actually doing. What is the process it is going through to find the number in Cell A1? Also, why is the timer showing all the time?

Graham

 

[Jonmo1]

Like I said, that was a very basic (probably stupid) example, and there are much easier ways to get the value of a cell. That was just to demonstrate a for/next loop...

It assignes a Number to a variable (i).
It starts with 1
So on the first loop i = 1
then it asks Does the value in cell A1 = The Variable i (1)..
If it DOES, THEN it goes to the line that gives the message box - then exits the for loop
If it Does NOT, Then it goes to the Next i (2) and so on..until the variable i = A1

The time shows probably because you put a fairly large number in A1, so it took that many loops (time) for the values to match.

 

[VoG]

This is an example of a subroutine (actually coded as a Function) - in this case finding the roots of a polynomial equation. Probably as clear as mud I know but it illustrates an iterative loop.

Private Function FindSatpH(R, S, U, V, W, Y)
    Dim ALOW, AHIGH, AMID, MHs, count As Integer, FHIGH, FMID
    ALOW = MH
    If Not IsError(PHSL) Then
        AHIGH = (10 ^ -PHSL) / F1
    Else
        AHIGH = 0.00000000000001
    End If
    count = 0
    Do
        count = count + 1
        If count > 50 Then
            MsgBox prompt:="FindSatpH - " & count & " Iterations", _
                    Title:="Convergence Failure", Buttons:=vbExclamation
            MHs = Sqr(AHIGH * AMID)
            Exit Function
        End If
        AMID = Sqr(AHIGH * ALOW)
        FHIGH = AHIGH ^ 6 + R * AHIGH ^ 5 + S * AHIGH ^ 4 + U * AHIGH ^ 3 + V * AHIGH ^ 2 + W * AHIGH + Y
        FMID = AMID ^ 6 + R * AMID ^ 5 + S * AMID ^ 4 + U * AMID ^ 3 + V * AMID ^ 2 + W * AMID + Y
        If Sgn(FHIGH) * Sgn(FMID) < 0 Then
            ALOW = AMID
        Else
            AHIGH = AMID
        End If
        MHs = Sqr(AHIGH * ALOW)
    Loop While Abs(AMID - MHs) > 0.000001 * MHs
    FindSatpH = MHs
End Function

 

[Kaps_mr2]

For what its worth, I learnt my BASIC on a BBC Micro over 20 years ago. I have used this as a basis for teaching myself VBA. There is a large degree of overlap. Thanks

Regards

Kaps

 

[mesteptoe]

Thanks again Jonmo1

I understand that.

Can you think whether it would be possible to undertake a calculation (in cell A1 for instance), store that number in a cell (say B1), undertake that calculation again using a figure which has increased by one, compare that to the number previously stored in B1, if it is less or more (depending whether you are looking for the peak or trough of a loop for instance) than the figure in B1 - then display the number in A1.

Graham

 

[Jonmo1]

Sure sounds possible, but I'm not sure I understand the logic...
could you give a more real world example of what you want to do?

What calculation specificly,
incriment a variable by 1 (from what # to what #)
Until what criteria is met ?

 

[Darren Bartrup]

Oh my god, BBC Basic!!! I learnt that at school. I'm suprised your computer is still running.

VBA will be able to do what you're asking, and no doubt there's a few shortcuts you'll be able to employ using code that wasn't available back then.
As far as I remember BBC Basic didn't have things like SELECT CASE, DO WHILE and a whole bunch of other stuff.

Would it be possible to paste the original code here, or is it way too long and likely to confuse the issue?
Remember to use [ CODE ] [ / CODE ] tags

Saying that, I won't be at a computer until Tuesday now so you'll probably have an answer before then.