Odd behavior of sub in Excel userform.

[TaylorDavidI]

I've been working on this spread sheet for a few months now, mostly because it's not critical and I'm just doing it in my spare time.

The section of code in question works fine under most circumstances; however, when "ClassBox" is not empty then it's failing to call the "Feats" sub on the final iteration of the loop (the only time when ClassBox is checked).

Private Sub Finish(TotHD As Integer)
    Dim x As Integer
    
    Call GeneralInfo
    AbilitySelect.Show
    
    Sheet2.Unprotect myPassword
    For x = 1 To TotHD
        If x = 1 Then
            If TypeBox.Value <> "Humanoid" Or HDbox.Value > 0 Then InitialProf TypeBox.Value
            InitialProf RaceBox.Value
            If RaceBox.Value = "Human" Then
                Load AddFeat
                AddFeat.SetAvailable
                AddFeat.Show
            End If
        End If
        If x = TotHD And ClassBox.Value <> "" Then
            Sheet2.Cells(5, 2).Value = ClassBox.Value
            Sheet2.Cells(5, 10).Value = 1
            InitialProf ClassBox.Value
            SkillPoints (x, TotHD)
            If ClassBox.Value = "Fighter" Then
                Load FighterFeat
                FighterFeat.SetAvailable
                FighterFeat.Show
            End If
            If ComboBox1.Value = "Expert" Then
                Load ExpertSkills
                ExpertSkills.Show
            End If
            'Feats x
        Else
            Sheet2.[J4].Value = CInt(Sheet2.[J4].Value) + 1
            SkillPoints (x, TotHD)
            'Feats x
        End If
        Feats x
    Next x
    Sheet2.Protect myPassword
End Sub

Private Sub Feats(HD As Integer)
    If HD = 1 Or IsDivis(HD, 3) Then
        Load AddFeat
        If HD <= CInt(HDbox.Value) Then
            AddFeat.SetRace RaceBox.Value
        Else
            AddFeat.SetClass ClassBox.Value
        End If
        AddFeat.SetAvailable
        AddFeat.Show
    End If
End Sub

All of the other sub calls work perfectly on every iteration of the loop. I've also tried placing the call to Feats in the If statement itself (where I have them commented out now).

I placed some debugging boxes before the call to Feats and at the beginning of Feats, neither even triggered when ClassBox was non-empty.

The function is called by one of four command buttons on a userform when they're completed running their code.

Can anyone tell me what might be causing that line of code to not run? And my appologies for the terrible style and inefficiency. I'll be working on that once I get it working.

David I. Taylor
A+, Network+, MCP Windows XP

 

[fumei]

I am not sure I am totally following, but is it this line

If x = TotHD And ClassBox.Value <> "" Then

that does not seem to be working when ClassBox is NOT empty?

If will only work if x = TotHD AND ClassBox is not empty. Both have to be true.

Gerry
My paintings and sculpture

 

[TaylorDavidI]

That part works fine, it's only meant to check ClassBox when x = TotHD. And the call to Feats happens outside of that If statement anyway.

David I. Taylor
A+, Network+, MCP Windows XP

 

[SkipVought]

Hi,

I'm confused. Feats does not need to be in the last If...Then...Else. In fact, you have them commented out and rather call Feats at the end of the loop.

Skip,
[sub]
[red][/red]
[/sub]

 

[fumei]

the call to Feats happens outside of that If statement anyway.

No it doesn't. It happens directly from that If statement.

If x = TotHD And ClassBox.Value <> "" Then
    Sheet2.Cells(5, 2).Value = ClassBox.Value
    Sheet2.Cells(5, 10).Value = 1
    InitialProf ClassBox.Value
    SkillPoints (x, TotHD)
    If ClassBox.Value = "Fighter" Then
       Load FighterFeat
       FighterFeat.SetAvailable
       FighterFeat.Show
    End If
    If ComboBox1.Value = "Expert" Then
       Load ExpertSkills
       ExpertSkills.Show
    End If
[COLOR=red]   'Feats x[/color red]
Else

Gerry
My paintings and sculpture

 

[SkipVought]

Either way

        If x = TotHD And ClassBox.Value <> "" Then
            Sheet2.Cells(5, 2).Value = ClassBox.Value
            Sheet2.Cells(5, 10).Value = 1
            InitialProf ClassBox.Value
            SkillPoints x, TotHD
            If ClassBox.Value = "Fighter" Then
                Load FighterFeat
                FighterFeat.SetAvailable
                FighterFeat.Show
            End If
            If ComboBox1.Value = "Expert" Then
                Load ExpertSkills
                ExpertSkills.Show
            End If
            [red][b]'Feats x[/b][/red]
        Else
            Sheet2.[J4].Value = CInt(Sheet2.[J4].Value) + 1
            SkillPoints x, TotHD
            [red][b]'Feats x[/b][/red]
        End If

Skip,
[sub]
[red][/red]
[/sub]

 

[fumei]

Right.

Gerry
My paintings and sculpture

 

[TaylorDavidI]

the calls to it that were within the if have been commented out. The call which is still part of the code exists outside any of the flow control other than the loop.

David I. Taylor
A+, Network+, MCP Windows XP

 

[fumei]

      [COLOR=red]  Feats x[/color red]
    Next x

but Feats x IS executed here, right?

It should execute regardless of the value of x, or ClassBox. It is part of the For x = 1 To TotHD loop, and should execute every iteration.

So if you have it in the

If x = TotHD And ClassBox.Value <> "" Then

statement, it should only execute when both those conditions are met.

I had a similar thing where I had an And which simply would not work. I ended up - because of time considerations - using two If statements just to make it do what I wanted.

Could you try that?

If x = TotHD Then
   If ClassBox.Value <> "" Then

just to see it Feats(x) fires properly?

As an alternative, that may tighten up the logic flow, perhaps use Select Case. Like this:

For x = 1 To TotHD
  Select Case x
     Case 1
        ' your stuff if x = 1
     Case TotHD
        If ClassBox.Value <> "" Then
          ' your stuff if x = TotHD, ClassBox is empty
          Feats x
        Else
          ' your stuff if x = TotHD, ClassBox is NOT empty
        End If
     Case Else
          ' your stuff if x not 1 or TotHD
  End Select
Next x

Often Select Case works better than multiple If..Then statements. After all, the real value being tested is x.

Gerry
My paintings and sculpture

 

[combo]

Are you sure that Feats x is not called? The code inside is conditional, what happens in Immediate window after:
Private Sub Feats(HD As Integer)
Debug.Print HD
If HD = 1 Or IsDivis(HD, 3) Then
....
End If
End Sub

combo

 

[TaylorDavidI]

thanks for the ideas fumei, the problem with a case statement is that multiple cases can be true simultaneously. forexample TotHD might be 1. Of course if I'm wrong and case will run all matching cases not just the first, I'll try that. Nesting the ifs didn't fix it either.

I put in the debug.print as you suggested Combo but the sub isn't called at all on that final iteration. I also threw one in just before the call to Feats (outside the ifs of course) and it doesn't get called either.

This is baffling me.

David I. Taylor
A+, Network+, MCP Windows XP

 

[PHV]

I'd double check all the code in InitialProf, FighterFeat and ExpertSkills for, say, an End instruction.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TaylorDavidI]

There are no unaccounted for End statements, Unload statements or Exit statements that shouldn't be there. they all either complete flow control or leave a function when it is completed.

Other than InitialProf I've been using values that don't call ExpertSkills or FighterFeat anyway and InitialProf is called several other times throughout the running of the code with no errors.

Thanks all the same for the effort,

David I. Taylor
A+, Network+, MCP Windows XP

 

[fumei]

            If TypeBox.Value <> "Humanoid" Or HDbox.Value > 0 Then InitialProf TypeBox.Value
            InitialProf RaceBox.Value
            If RaceBox.Value = "Human" Then
                Load AddFeat
                AddFeat.SetAvailable
                AddFeat.Show
            End If
        End If

Is this properly terminated?

Gerry
My paintings and sculpture

 

[TaylorDavidI]

As far as I know it is... the full code is actually

        If x = 1 Then
            If TypeBox.Value <> "Humanoid" Or HDbox.Value > 0 Then InitialProf TypeBox.Value
            InitialProf RaceBox.Value
            If RaceBox.Value = "Human" Then
                Load AddFeat
                AddFeat.SetAvailable
                AddFeat.Show
            End If
        End If

the "If TypeBox..." line terminates itself since I only call one command, right?

David I. Taylor
A+, Network+, MCP Windows XP

 

[fumei]

Yes, if it is one line. Then it should execute as if it was:

If x = 1 Then
  If TypeBox.Value <> "Humanoid" Or HDbox.Value > 0 Then
     InitialProf TypeBox.Value
  End If
  InitialProf RaceBox.Value
  If RaceBox.Value = "Human" Then
     Load AddFeat
     AddFeat.SetAvailable
     AddFeat.Show
  End If
End If

the problem with a case statement is that multiple cases can be true simultaneously. forexample TotHD might be 1.

Huh? That can be covered with Select Case.

For x = 1 To TotHD
  Select Case x
     Case 1
[COLOR=red]        ' code if x = 1, TotHD = anything
        ' if testing for specific x = 1 AND TotHD = 1[/color red]
        If TotHD = 1 Then  
[COLOR=red]        ' code if BOTH = 1[/color red]
        End If
     Case TotHD
        [COLOR=red]' code if x = anything, AND = TotHD
        ' but value is NOT = 1[/color red]
     Case Else
        [COLOR=red]' code if x = anything
        ' if testing for x = anything AND TotHD = 1[/color red]
        If TotHD = 1 Then
  [COLOR=red]      ' code if x = anything AND TotHD = 1[/color red]
        End if
End Select

All the possibilities can be covered.

Gerry
My paintings and sculpture

 

[TaylorDavidI]

thanks again fumei,

one of my co-workers pointed that out to me a few minutes ago as well so I've come up with something similar. Unfortunately I've forgotten the USB flash drive my sheet is on so I can't test it until this afternoon.

To reduce the number of if statements I've also put the calls to FighterFeats and ExpertSkills into their appropriate cases within InitialProf.

I'll let you know if these changes fix my problem; although I would still love to know why Feats wasn't getting called while outside of the if scope.

David I. Taylor
A+, Network+, MCP Windows XP

 

[SkipVought]

Could it be that TotHD < 1???

For x = 1 To TotHD

Skip,
[sub]
[red][/red]
[/sub]

 

[fumei]

Ah Skip, you are a charmer.

Gerry
My paintings and sculpture

 

[SkipVought]

I dunno, mebe its one of them thar,

"...can't see the forest for the trees..." thang.

Happens to me from time to time. '-)

Skip,
[sub]
[red][/red]
[/sub]