Can sub routines be performed in Excel

mesteptoe

Board Regular
Joined
Aug 22, 2008
Messages
104
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

Legend
Joined
Jun 19, 2002
Messages
63,651
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/DummiesTitle/Excel-VBA-Programming-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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

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

Board Regular
Joined
Aug 22, 2008
Messages
104
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Legend
Joined
Jun 19, 2002
Messages
63,651
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.

Code:
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

Well-known Member
Joined
Jul 5, 2008
Messages
1,525
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

Board Regular
Joined
Aug 22, 2008
Messages
104
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Well-known Member
Joined
Mar 13, 2006
Messages
1,258
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.
 

Forum statistics

Threads
1,081,753
Messages
5,361,097
Members
400,613
Latest member
Markdc123

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top