Consulting

Results 1 to 16 of 16

Thread: called sub not working

  1. #1
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location

    called sub not working

    In this code:

    [vba]If Sheets("Input + Wksheet").Range("B10").Value = 1 And Sheets("Input + Wksheet").Range("G6").Value > 65 Then

    DirectBillOnlyIndemnity2Off
    With Sheets("Direct Bill ONLY")
    .Range("D11").Value = "X"
    .Range("E24").Formula = "=if(MEDCVG=1,VLOOKUP(YearsOfService,Indemnity,IF(Age<=65,7,14),FALSE), 0)/12"
    .Range("F24").Formula = "=IF(Age<65,Indemnity!H42/12,Indemnity!O42/12)-E24"

    'DirectBillOnlyIndemnity3On
    'DirectBillOnlyIndemnity4On

    End With

    With Sheets("Direct Bill with RIA")
    .Range("D11").Value = "X"
    .Range("E20").Formula = "=if(MEDCVG=1,VLOOKUP(YearsOfService,Indemnity,IF(Age<=65,7,14),FALSE), 0)/12"
    .Range("F20").Formula = "=IF(Age<65,Indemnity!H42/12,Indemnity!O42/12)-E20"


    End With

    End If[/vba]

    The line in red (the sub being called) does not execute. However if you go to the sub and either press F8 and step through it or execute it with F5 it works fine. Ideas?
    Peace of mind is found in some of the strangest places.

  2. #2
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Is it in an outside module? Does it error? When running the housing code, and stepping through with F8, what happens? Does it go into the code? Does it go into the code and then error? What exactly happens? Is the routine in another module but set to Private?

  3. #3
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    Howdy Zack, long time no see.

    Its not in an outside module. It does not error. It steps through it just fine using F8 but when you look at the sheet it is suposed to do the proc on it does absolutly nothing. It is not set to private.
    Peace of mind is found in some of the strangest places.

  4. #4
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    me thinks it is because the proc that is being called:

    [VBA]Sub DirectBillOnlyIndemnity2Off()
    Range("J22").ClearContents
    Range("J23").ClearContents
    Range("K23").ClearContents
    Range("H22:K25").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

    Range("I24").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

    End Sub[/VBA]

    was created in the sheet. So when its called its like what range? However, I did switch it to the module and added a qualifier for the sheet for each range with no luck.
    Peace of mind is found in some of the strangest places.

  5. #5
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Can you set a breakpoint in your procedure, inside DirectBillOnlyIndemnity2Off
    , then run your code normally. It should break into your routine. I have no idea what your routine does, so it's hard to say if it's working right or not.

    Yeah, long time no see! It's been nice being able to get back into the forums again, albeit slowly.

  6. #6
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    The procedure has no specific sheet set to it, so it will work on the active sheet. Do you want to pass a worksheet to that routine, to specify it? Or is it the same worksheet every time? You should qualify the worksheet either way.

  7. #7
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    Ill post the WB.
    Peace of mind is found in some of the strangest places.

  8. #8
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    I think i got it working. Was not in the part of the code where it selects the sheet to perform the sub on. Ill post back if i have any more issues.
    Peace of mind is found in some of the strangest places.

  9. #9
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    Well I dont have it working. Im posting the workbook. Heres how I tested it.

    When you open the WB copy the three highlighted cells to the right of the Retirement Date and paste them in the yellow boxes that correspond to them to the left.

    Make sure the medical coverage box is set to 1.

    Now, press "Calculate Retiree Rates"

    Next, check the 'Direct Bill ONLY" sheet. The boxes and text should be gone on the indemnity row next to Retiree Only but they are not, or if they are not all of it has vanished.

    Next, if you open the VBE editor and F8 throught it it works as it should except for the part where it wont remove the Retiree Spouse/DP text. It says its a merged cell but I dont see how seeing as how i un merged it..

    Why would it work if you F8 through it and not if you just press the button? I even put break points as it went through the code on every line and it worked with the exception of the merged cell thing.

    Peace of mind is found in some of the strangest places.

  10. #10
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,368
    Location
    Hi again :-)

    Not well tested... In a junk copy of your wb, try:

    Sub DirectBillOnlyIndemnity2Off()
        
        '// Use With to easily qualify each range...                                    //
        With ThisWorkbook.Worksheets("Direct Bill ONLY") '<---OR:  With Sheet4
            '// BEFORE testing, J22 still needed un-merged                              //
            .Range("J22").ClearContents
            .Range("J23").ClearContents
            .Range("K23").ClearContents
            '// ...and you can nest the with for properties of the Range, such as .Borders//
            With .Range("H22:K25")
                .Borders.LineStyle = xlNone
                .Borders(xlDiagonalDown).LineStyle = xlNone
                .Borders(xlDiagonalUp).LineStyle = xlNone
                .Borders(xlInsideVertical).LineStyle = xlNone
                .Borders(xlInsideHorizontal).LineStyle = xlNone
            End With
            
            With .Range("I24")
                .Borders.LineStyle = xlNone
                .Borders(xlDiagonalDown).LineStyle = xlNone
                .Borders(xlDiagonalUp).LineStyle = xlNone
            End With
        End With
    End Sub
    Your ranges were not being qualified, so success would be dependant upon the sheet being active.

    I did not read through all the code, but I do not believe you are deleting any of the sheets. If this is the case, might I make a suggestion? See in the very first With, where I noted that you could use the sheet's CodeName, like:
        With Sheet4
    As long as you are not copying sheets or deleting them, you might consider using their CodeNames, as two things seem benefitted to me leastwise. (1) If the user renames a sheet, the codename still works, and (2) I find it much easier to write code with intellisense helping me out.

    Hope that helps,

    Mark

  11. #11
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,368
    Location
    ACK...

    I forgot to mention, if you do try using the codenames, they can be changeed during design time, so you could name them like 'shtDirect_Bill_ONLY' or 'shtDirectBillOnly', so that it would still be intuitive as you are typing along.

  12. #12
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Question: why do you have 2 subs named DirectBillOnlyIndemnity1On

    One is in Module1 and one is on the code for Sheet4 (DirectBill Only)

    (You have other subs duplicated also)

    If you REALLY wanted to call a specific one, I think you need to use something like

    Call Module1.DirectBillOnlyIndemnity1On

    Personnally (and it's only my style) I'd put subs like DirectBillOnlyIndemnity1 in a module and only WS control code and event handlers in the WS code.

    You might not be executing the same module with F5 that your WS controls are

    Paul

  13. #13
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    Thanks to you both. Marks suggestion was spot on and exactly what i needed. Looks like I have a lot of modifying and cleaning up to do. Thanks again Mark.
    Peace of mind is found in some of the strangest places.

  14. #14
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,368
    Location
    I'm happy to be of help Austen.

    Quote Originally Posted by Paul_Hossler
    ...If you REALLY wanted to call a specific one, I think you need to use something like

    Call Module1.DirectBillOnlyIndemnity1On

    ...You might not be executing the same module with F5 that your WS controls are
    Hi Paul,

    I certainly agree with most everything you pointed out, such as the duplication of subs and keeping general code in standard modules. Personally, in cases where it seems advantageous to create a function for something that only needs done as part of an event or two in the object module (let's say Workbook_Open in ThisWorkbook), I find it easier to keep the function in the same object/class module. Usually I'll mark it Private just so its plain to me later. Certainly just a preference as you noted.

    As to qualifying the procedure called w/the module name, in this case Austen is using commandbuttons from the Forms toolbar. So when he chose the macro to run from the Assign Macro dialog box, it is already being qualified as necessary. For instance, in Austen's workbook, if you bring up the Assign Macro dialog for one of the buttons, and scroll down, you'll see the duplicate subs prefaced with 'Sheet4.'.

    Well... probably already too 'wordy', so I'll stop "flappin' my yap".

    A great day to both ya'll,

    Mark

  15. #15
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Mark,

    "Flap away" -- I always learn something, like ...

    As long as you are not copying sheets or deleting them, you might consider using their CodeNames, as two things seem benefitted to me leastwise. (1) If the user renames a sheet, the codename still works, and (2) I find it much easier to write code with intellisense helping me out.

    That was a good catch. I developed the habit of never assuming an Activesheet and always qualifying my .Ranges and .Cells so I never thought about that and read right though it


    Paul

  16. #16
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    I find myself in that habit more and more. I just carried my variable naming convention for worksheets right over to their code names. Simple, and no more variable declarations and Set statements, just go straight to coding.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •