Simple question regarding the use of the IF-statement in VBA in Excel

[kristiankoehn]

I am not familiar enough with VB, but need to program the following:
In the excel spreadsheet, I have a variable (AGE) wich is a number. Depending on the size of this variable, I want to make a VB-macro which executes different other macros depending on the value of AGE.

In excel (assuming that "Macro1" would execute a macro - which it will not, I know...!), I would state this like IF(AGE<6;"Macro1";IF(AGE>6;"Macro2";"")) and so forth.

How do I express this in VB? I guess it is pretty straightforward...?

 

[kristiankoehn]

I forgot to mention that the programming will also need to take account of the need to define intervals. By that I mean that I should be able to select the function on the basis of a value greater than AND less than a value. In excel terms: AND(age>=6;age<8).

 

[xladept]

Hi kristiankoehn,

Try something like this:

Sub FAge(): Dim Age As Integer
If Age >= 6 And Age < 8 Then
Macro1
ElseIf Age > 8 Then
Macro2
End If
End Sub

 

[kristiankoehn]

Hm, I am probably not very clever here, but I cannot get the macros executed. I typed in this code (Slightly altered, in norwegian...):

Sub FAlder(): Dim Alder As Integer
If Alder >= 6 And Alder < 8 Then
Vis_6til8
ElseIf Alder > 8 Then
Vis_8til16
ElseIf Alder >= 8 And Alder < 16 Then
Vis_8til16
End If
End Sub

 

[jgs2012]

Sorry for jumping in here... But is it just me or did you miss "Call"ing the macros....???? Try that... I assume Vis_6til8 is the name of your macro.... (It might be more complex than that but from a quick glance it looks like if you add "call" before your macro's it should execute your macros.....

Sub FAlder(): Dim Alder As Integer
If Alder >= 6 And Alder < 8 Then
Call Vis_6til8
ElseIf Alder > 8 Then
Call Vis_8til16
ElseIf Alder >= 8 And Alder < 16 Then
Call Vis_8til16
End If
End Sub

 

[Soggy]

The order of you if statements do not appear to make sense.

you have the option of between 6 & 8 which is okay, but then greater than 8 followed between 8 & 16. I t won't go to the last option as it has already used the greater than 8 option.

If you want between 6 & 8, then 8 & 16, then greater than 16 try:

Sub FAlder()


Dim Alder As Integer




    If Alder >= 6 And Alder < 8 Then
        Vis_6til8
    ElseIf Alder >= 8 And Alder < 16 Then
        Vis_8til16
    ElseIf Alder >= 16 Then
        Vis_gt16
    End If
End Sub

I have changed the name of the greater than macro as the name you provided was repeated. (No need for call function although some use it to make code easier to read).

Also I am assuming you assign a value to alder else where.