Excel VBA calling sub from another sub with multip

问题:

I would like to call a sub from another sub inside in the same module. The first sub would be my main code and there I would call the second subroutine. Second subroutine receives multiple inputs as integer, double, double arrays and double matrices. The size of the arrays and matrices are known and stored in an integer variable. The sub also returns several outputs. So, I would like to do something like this.

sub Main()
    Nc As integer
    Dim kij(1 To Nc, 1 To Nc), xi(1 to Nc), a1 As Double
    'I assign values to my variables from the excelsheet e.g. Nc=Cells(1,1) etc.

    CalculateA(Nc,kij, xi, a1, a)
    Cells(5,5)=a
end sub

sub CalculateA(Nc as integer,kij as matrix double, xi as array double, a as Double)
    a=0
    For i=1 To Nc
       For j=1 To Nc
          a = a + kij(i,j)*x(i)*x(j)
       Next j
    Next i
    a = a*a1
end sub

How does it know which sub is the main sub where it starts to run. Can I put the secondary sub on top and the code somehow starts from the bottom subroutine?

回答1:

To call a sub inside another sub you only need to do:

Call Subname()

So where you have CalculateA(Nc,kij, xi, a1, a) you need to have call CalculateA(Nc,kij, xi, a1, a)

As the which runs first problem it's for you to decide, when you want to run a sub you can go to the macro list select the one you want to run and run it, you can also give it a jey shortcut and only have to press those keys, on secondary subs i usually do it like Private sub CalculateA(...) cause this way it doesnt appear in the macro list and it's easier to work

Hope it helps, Bruno

PS: If you have any other question just ask, but this isn't a community where you ask for code, you come here with a question or a code that isn't running and ask for help, not like you did "It would be great if you could write it in the Excel VBA format."

回答2:

These are really two questions.

The first one is answered here: Calling a Sub in VBA

To the second one, protip: there is no main subroutine in VBA. Forget procedural, general-purpose languages. VBA subs are "macros" - you can run them by hitting Alt+F8 or by adding a button to your worksheet and calling up the sub you want from the automatically generated "ButtonX_Click" sub.

回答3:

VBA subs are no macros. A VBA sub can be a macro, but it is not a must.

The term "macro" is only used for recorded user actions. from these actions a code is generated and stored in a sub. This code is simple and do not provide powerful structures like loops, for example Do .. until, for .. next, while.. do, and others.

The more elegant way is, to design and write your own VBA code without using the macro features!

VBA is a object based and event oriented language. Subs, or bette call it "sub routines", are started by dedicated events. The event can be the pressing of a button or the opening of a workbook and many many other very specific events.

If you focus to VB6 and not to VBA, then you can state, that there is always a main-window or main form. This form is started if you start the compiled executable "xxxx.exe".

In VBA you have nothing like this, but you have a XLSM file wich is started by Excel. You can attach some code to the Workbook_Open event. This event is generated, if you open your desired excel file which is called a workbook. Inside the workbook you have worksheets.

It is useful to get more familiar with the so called object model of excel. The workbook has several events and methods. Also the worksheet has several events and methods.

In the object based model you have objects, that have events and methods. methods are action you can do with a object. events are things that can happen to an object. An objects can contain another objects, and so on. You can create new objects, like sheets or charts.