none
VB: How to Remove an END IF ? (ANSWER: divide code with multiple END-IF's, for quick troubleshoot code) RRS feed

  • Question

  • hi,  my vb is not that great, but i am looking for a better way to troubleshoot code.

    if exists a better way than what have to do sometimes, is by hand deleting whole sections of code until problem goes away, for fault isolation then i could use some help there.

    otherwise idea:  have a UDF that can be used for multiple logic IF THEN's & END IF's, to be added to code for dividing sections up, allowed to be left in place by being able to turn it all off,  or on to find a target for a problem area.

    Function goTANGO()    'find target,  maybe call as:  'if gotango = true then "End If9" else vbnullstring 'STOP9'

        gotango=false: IF range("a1") = "x" then gotango = true    'how make multiple IF THEN & corresponding END IF go dark

    end function

     

    idea:  a platform for an IF THEN CASE result might help alot? (any of click 1 check box to isolate an area & 2 more? check boxes for:  yes no did problem go away?)

    not sure of any better for simplicity,  but my test below will look from the bottom of a code area & up.

    have to say there would be a solution to help this, by allowing page breaks in code view.  ( I am forever!! trying to get my search methods, destinations to top of view).  a page can be as long as desired?  but it might assist in this matter.

     

    the example below basically - actually works, but is still more manual than desired..  better than cut test ugh, cut test ugh..

     

     

    ==========    EXAMPLE

    'MAKE CASE CHOICE?    'DIVIDING A PAGE UP  gotango
    'EXAMPLE:   'need udf? turn off IF's & END IF's (FIX CASE CHOICE) on-off,  if gotango = true, then "END IF" else vbnullstring.

    'Private Sub Worksheet_SelectionChange(ByVal Target As excel.RANGE)
    '    Dim A1 As String: A1 = RANGE("A1")   'problem0  workcell references have no home
    '    Dim A2 As String: A2 = RANGE("A2") 'etc

    'With Target     'end with at bottom
    '    If .Count > 1 or goZONE = False Then goEXIT

    'HDR VB, for sheet header or run all the time (hdr here)..    '<<  PROB how test header? need a separate instance?

    '==========  ==========  ==========  ==========  ==========
      'MACROS ON-OFF SWITCH  (watch out: end if at bottom; not to cut off)
    'If RANGE("A1").Value = "." Then      'mac's on/off safety  MAIN START  << LAND MINE
    '==========  ==========  ==========  ==========  ==========

    If 1 Then 'STOP9     'HOW MANY PAGES  38 (assume HDR VB maybe ok, round down: ea 3 pages / 38/10=3.8)
    If 1 Then 'STOP8     'is prob at 9: no, 8: no, 7: yes, then prob is between 7 & 8
    If 0 Then 'STOP7
    'If 1 Then 'STOP6    'one small answer is to have all set to 1, then all code is turned on (no puns intended)
    'If 1 Then 'STOP5
    'If 1 Then 'STOP4
    'If 1 Then 'STOP3
    'If 1 Then 'STOP2
    'If 1 Then 'STOP1
    'If 1 Then 'STOP0

    'TOP COPY:
    'End If 'STOP0
    'End If 'STOP1
    'End If 'STOP2
    'End If 'STOP3
    'End If 'STOP4
    'End If 'STOP5
    'End If 'STOP6
    'End If 'STOP7
    'End If 'STOP8
    '>>>>>>>>>>>>
    'End If 'STOP9    'bot
    '>>>>>>>>>>>>

    'vb
    'vb

    'if gotango = true then "End If" else vbnullstring 'STOP7 
    'vb

    'if gotango = true then "End If" else vbnullstring 'STOP8
    'vb

    '>>>>>>>>>>>>
    'if gotango = true then "End If" else vbnullstring 'STOP9'    'bot
    '>>>>>>>>>>>>

    'end if   'A1 BOT
    'end with
    'end sub












    • Edited by Davexx Monday, April 2, 2018 6:59 AM
    Tuesday, November 28, 2017 1:30 PM

All replies

  • Hi Davexx.

    from the title and initial description of the thread, it looks like you want to disable some code which may have any problem with it.

    form the code you had posted , I got the idea that you are using the Excel VSTO Vb.net workbook.

    correct me , if I misunderstand anything in your above description.

    once you compile the project and run it. you cannot make any changes in code at run time.

    your requirements are not clear, why you want to disable the code.

    try to provide a detailed information about what you are trying to achieve.

    if your code have the faults then you need to correct all the errors before you run the code instead of disable any part of code at run time.

    try to develop a solid logic and make a robust code , so you not need to worry about the problems that may occur at run time.

    from your overall description, I see you mentioned many ideas, some code snippets and some other descriptions and you made the original post very long and confusing. we are not able to understand clearly, what's the issue, which exact code you are using or what you want to do with it.

    I suggest you to again describe your issue in a clear way with proper details , code and steps to reproduce it.

    we will try to make a test with it and try to provide a solution for it.

    Thanks for your understanding.

    Regards

    Deepak  


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 30, 2017 8:26 AM
    Moderator
  • a shorter summary:

    thought i needed help with some kind of udf vb,  eg:  to be able to universally turn IF 1 THEN's - END IF's (ON or OFF) placed thru out a document so would not interfere with code.  i found that i can just leave simple end if's in place, with grid of:  IF 1 THEN's at top, to all be left at:  1  leaves code turned on, and that works.  (not needed, but might have looked like):

    'function goTANGO()    'call as?  gotangoA (1 or 0) 'STOP 100 (if 1 then)    gotangoB  'STOP100  (end if)
    '    set gotangoA = "IF x THEN", else nullstring: Set gotangoB = "END IF", else nullstring
    '    if some switch on RANGE("A5").value ="x" then gotangoA = true: gotangoB = true else union gtango = false
    'End Function

    i had multiple copies of same code only separated with if 1 then or if 0 then etc.  that cause multiple end if problems if not done correct.  i also had an events item set to true that needed to be put back to false, in an Events Change sub (that caused a big monkey wrench).

    unless someone has some troubleshooting improvements, or to better automate this, with optional eg: page breaks in vba code area.  (page breaks might allow multiple other items to work better:  top of view, isolating code faster & more automated than my eg's,  summary lists where pages have note / purpose tags,  more universal techniques, etc......  setting up other tools:  universal dims? more)

    even if have multiple symptoms, i can pick one and isolate it in less than 2 minutes, stead of 6 hours?  i spent a couple of days tracking problems down.

    NOTE:  as below,  just put:  END IF 'STOP 100  etc  thru out code to isolate portions out

    ==========

    hi, thanks for the reply!  i am getting pretty busy on my excel doing much code review.  am pretty lo end on vb skills so pardon if i call anything by a wrong name..  did not think had any answers coming for this so did not check back lately.

    unless there is some other technique on troubleshooting i will try to look up, but i though this simple idea? for just using IF THEN's to isolate portions of code would not get much attention.  i am seeing it as having more power than expected though.  i spent a good of time setting it up.  i will give more of an actual example below,  but:


    a summary of what happened:

    in going thru code for a complete review..  for old repeat examples i found bugs in different areas:  some END IF problems caused an:  "if you click anywhere" to cause different codes to fire for many copies eg:

    'Private Sub Worksheet_SelectionChange(ByVal Target As excel.RANGE)
    'with target

    If 0 Then   'small example, not this one but other click anywhere if endif's wrong, and code firing..
    If Target.row > RANGE(G8).row And Left(Cells(activecell.row, "A:A").Text, 1) = "." Then  'And Left(Target.Formula, 10) <> "=HYPERLINK" Then  'G8 hdr  M8 grid  E7 top  E8 bot
    Set MyRange = Me.RANGE(P6).EntireColumn   '>> DL1x: top of Jumps way below   'OR: & "," &  (some J4 conflict, extra scroll, J4 works below/ if J4 off here)
    If Target.Cells.Count = 1 And Not Intersect(Target, MyRange) Is Nothing Then  'B5 nam  J4 x
        If activecell.Value = "." And Cells(activecell.row, J5).Text = "dn" Then ActiveWindow.ScrollRow = activecell.row - 1      '<<  SCROLL ROW
    End If: End If

    ElseIf 0 Then
        'copies etc..
    ElseIf 0 Then
        'etc
    End If

    'end with
    'end sub


    from there / after much other work checking other vb subs on the main work sheet, i had one item during rewrite mis-labeled for events to true that should have been false.  cause much havoc.  1 ANSWER FOUND:  find a way to turn all other subs off (see if symptom goes away).  1 small method:  put temporary events = false at top of each area,  would have saved alot of time.  including copy:


    'EG:

    'Private Sub Worksheet_SelectionChange(ByVal Target As excel.RANGE)
    '    Dim P6 As String: P6 = RANGE("P6")   'workcell P6 shows eg: N:N
    'with target
     
    If 0 Then   'small example, not this one but other click anywhere if endif's wrong, and code firing..
    If Target.row > RANGE(G8).row And Left(Cells(activecell.row, "A:A").Text, 1) = "." Then  'And Left(Target.Formula, 10) <> "=HYPERLINK" Then  'G8 hdr  M8 grid  E7 top  E8 bot
    Set MyRange = Me.RANGE(P6).EntireColumn   '>> DL1x: top of Jumps way below   'OR: & "," &  (some J4 conflict, extra scroll, J4 works below/ if J4 off here)
     
    If Target.Cells.Count = 1 And Not Intersect(Target, MyRange) Is Nothing Then  'B5 nam  J4 x
        If activecell.Value = "." And Cells(activecell.row, J5).Text = "dn" Then ActiveWindow.ScrollRow = activecell.row - 1      '<<  SCROLL ROW
    End If: End If
     
    ElseIf 0 Then
        'copies etc..
    ElseIf 0 Then
        'etc
    End If
     
    'note, P6 has:  =SUBSTITUTE(SUBSTITUTE(CELL("address",$EL6),"$",""),ROW(),"")&":"&SUBSTITUTE(SUBSTITUTE(CELL("address",$EL6),"$",""),ROW(),"")
    'end with
    'end sub


    ==========    SOME ANSWERS:


    i was able to use the if then technique to isolate a lot of areas that needed checking. with a simple if 0 then,  to if 1 then to turn an area on starting from the bottom up..

    the old alternative (still used as needed), to delete whole sections of code to see if problem went away besides being cumbersome would cause you to lose your place on did above or below work (ugh:  multiple problems).

    to me it would seem a bit simple for anyone to want to help with,  but once i got things lined up on one sub, it was easy to apply elsewhere  (NOTE:  I THINK having an 'optional'  page break system in vb code would greatly enhance this solution).  i had this idea long ago in a post for the next big thing,  but got no replies,  so here is part of it,  funny this might not even be 2% of what had for making excel easier).

    so i did this a week ago & not sure if needs any editing, major notes else where but i spent a lot of time editing this for accuracy because i needed it to work. 

    NOTE:  WHAT WAS POSTING FOR,   thought i needed a way to turn all if thens off, but i found i can leave them in place, as below

    this works & saved alot of time,  the idea is:


    '0. with all 1's below, this item can be left in place, have many other notes but: do not cut off any utilty eg:  if range("a1") = "." then '(macs off)
    '1. apply events false at top
    '2. turn off sections, pick your start point, divide by 50's
      '(correction: all 1's & apply 0 only 1 aat, from bottom up)

    'TSX TOP:   code ON-OFF method for isolate problems (all 1's turns everything on, would be 0's from bot to turn 1 aat from bottom: off)
    '(KEY CK:   all 1's except for 1 zero to test, macs on, events true);  FIND top yes then no, srch that 'yes' stopxx number, problem vb is below that number.
    'RESET TSX (ALL 1's): start middle/x9 (eg 100 is file bot), w/only 1 zero aat test dn for top YES prob, (by 50%/lines 10/5/then 1 up aat), 1st no above: yes is prob

    If 1 Then 'stop100    'STEP1: (to 0) bottom starts here;  ea "page can have a user entered title-note applied, will help with work (if it shows here)
    If 1 Then 'stop99     'as evolve, have a problem-symptoms drop down:  choose from automated troubleshooting list
    If 1 Then 'stop98     'turn off by replace ALL:  End If 'stop   with hash:  'End If 'stop  (hilite IF's & replace same, eg's will revert back on next replace)
    If 1 Then 'stop97
    If 1 Then 'stop96
    If 1 Then 'stop95
    If 1 Then 'stop94
    If 1 Then 'stop93
    If 1 Then 'stop92
    If 1 Then 'stop91
    If 1 Then 'stop90     '(can be zero's from bot up? as boolean AND: 1st zero stops everything after; note: the higher zero turns all off after it)

    If 1 Then 'stop89     't3 NO    STEP2: 50% WAY DOWN, THEN WORK UP 1 AAT WHEN FIND PROBLEM TO GET 1ST NO PROB, YES LINE/ BELOW THAT (IN CODE CHECK)
    If 1 Then 'stop88.1
    If 1 Then 'stop88     't3 NO  t2 no
    If 1 Then 'stop87     't3 YES PROB ts xx 171131b  t2 0 yes, prob here?  ts xx 171131a  if st to ts
    If 1 Then 'stop86     't3 YES
    If 1 Then 'stop85     't3 YES mac on, run  prob
    If 1 Then 'stop84
    If 1 Then 'stop83
    If 1 Then 'stop82
    If 1 Then 'stop81
    If 1 Then 'stop80


    'stxxx  tsxxx (below when done)   a1top  srch bookmarks
    'SKEL COPY (eg's for ascension order)

    'NOTE:  put:  END IF 'STOP 100  etc  thru out code to isolate portions out  (IE:  COPY - PASTE THESE TO BELOW)

    '>>>>>>>>>>>>
    'End If 'stop80
    '>>>>>>>>>>>>

    '>>>>>>>>>>>>
    'End If 'stop81
    '>>>>>>>>>>>>

    '>>>>>>>>>>>>
    'End If 'stop82
    '>>>>>>>>>>>>

    '>>>>>>>>>>>>
    'End If 'stop83
    '>>>>>>>>>>>>

    '>>>>>>>>>>>>
    'End If 'stop84
    '>>>>>>>>>>>>

    '>>>>>>>>>>>>
    'End If 'stop85
    '>>>>>>>>>>>>

    '>>>>>>>>>>>>
    'End If 'stop86
    '>>>>>>>>>>>>

    '>>>>>>>>>>>>
    'End If 'stop87
    '>>>>>>>>>>>>

    '>>>>>>>>>>>>
    'End If 'stop88
    '>>>>>>>>>>>>

    '>>>>>>>>>>>>
    'End If 'stop89
    '>>>>>>>>>>>>

















    '>>>>>>>>>>>>
    'End If 'stop90
    '>>>>>>>>>>>>

    '>>>>>>>>>>>>
    'End If 'stop91
    '>>>>>>>>>>>>

    '>>>>>>>>>>>>
    'End If 'stop92
    '>>>>>>>>>>>>

    '>>>>>>>>>>>>
    'End If 'stop93
    '>>>>>>>>>>>>

    '>>>>>>>>>>>>
    'End If 'stop94
    '>>>>>>>>>>>>

    '>>>>>>>>>>>>
    'End If 'stop95
    '>>>>>>>>>>>>

    '>>>>>>>>>>>>
    'End If 'stop96
    '>>>>>>>>>>>>

    '>>>>>>>>>>>>
    'End If 'stop97
    '>>>>>>>>>>>>

    '>>>>>>>>>>>>
    'End If 'stop98
    '>>>>>>>>>>>>

    '>>>>>>>>>>>>
    'End If 'stop99
    '>>>>>>>>>>>>

    '>>>>>>>>>>>>
    'End If 'stop100
    '>>>>>>>>>>>>






    • Edited by Davexx Monday, December 11, 2017 8:50 AM
    Monday, December 11, 2017 7:52 AM
  • Hi Davexx,

    still I can see that the description of the thread is much more longer.

    it contains several code snippets and some ideas.

    but still we are not able to understand the exact issue.

    I suggest you to describe your issue with key points. like what output you want, what exact thing you want to do, inform us about the error if you get any.

    let's start with the beginning.

     just provide your overall goal. example : you want to check the value of Cell A1 to A100 on Cell_Chnage event.

    further, we will suggest you the best way to achieve your goal with some code examples.

    by this way, it will be much more easier to troubleshoot your issue.

    I also suggest you to break your requirement in to several parts, if it is big or complex.

    we will try to solve it one by one.

    I also suggest you to create a new thread for each small part of your issue.

    I hope , you can again try to describe your requirement as I suggest you above.

    thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 13, 2017 5:35 AM
    Moderator
  • NOTE:  status is that i did not have the answer on the first post.  i think i did have the answer by the time i replied your post.

    summary,  if this is a short answer:  by having a series of:  IF 1 THEN  &  corresponding END IF's thru out document to test where a symptom stops (by turning IF1  to  IF0 1 at a time to isolate the problem.  funny thing is you can leave the  IF1's in place for later use. (IF1 leaves vb turned on).  i think this can be made more seamless / automatic by having (optional / optional viewable: 2 different things?) virtual page breaks in the vb, thereby using a different method of hidden? ENDIF's.  having some kind of page breaks should allow better search for your vb:  to put destination at top of view & make things move along faster-easier for code review.  thanks.

    if it makes anything easier, i had come with a few things in the past:  use of the hyperlink function told for years, could not be done.  coming up with new hyperlink (QLINK) for a scroll roll to top of view (no more offsets),  having buttons in a window for web pages instead of plastered on button bar:  aka tabbed browsing.  use a dot prompt, hiding ie header like old task manager hide header, dozen more).  anyways, i could come with a hundred more instead of this 1 aat stuff. 

    ==========

    hi,  thanks for the reply.  sorry gone so long.  not running so fast anyways but had a 2 week flu.  pardon if write a lot of ideas & not all seem directly connected.  i have had some ideas for upgrading the excel experience... for some time but not sure all taken seriously where they might seem out of ordinary.  a big? one posted was idea of problem for having work cells (to reference work columns rows cells.. and subsequent problem of having to reestablish dims for each on 'every' bit of programming for eg:  Dim D1 As String: D1 = RANGE("D1")

    where D1 may show:   AP:AP

    D1 has:  =SUBSTITUTE(SUBSTITUTE(CELL("address",$AP1),"$",""),ROW(),"")&":"&SUBSTITUTE(SUBSTITUTE(CELL("address",$AP1),"$",""),ROW(),"")

    ----------

    and too many other ideas i'm guessing..  (for stream lining effort..  the above might be 1/100th of what have come across)

    for here, the big item recently came across for troubleshooting location of problem vb.  (not feeling so well, but hope a brief - good example of what doing..)  do you get this line/ purpose of what doing??

    i have found that i can leave a series of  IF - THENS, right in my vb, that will remain neutral by using the IF's at the top, to all have the value of:  1,  ie:  IF 1 THEN  (then all items below that are turned on / valid vb).  putting all the IF's at the top at 1 location at the top, and putting the:  END IF's thru out the document will separate each area to be tested.  (again:  all IF 1 THEN   items at the top, instead of:  IF 0 THEN,  will leave all vb turned on :).  example i got this from is by using a cell A1  to equal "." to turn all macros on/off as a safety switch.  this example:

    'NOTE:  this would be an answer at this point.  so unless anyone has a way to improve this..  it is my guess that MS could enhance this greatly, incorporating it right into the vb environment to make things more seamless.

    ==========

    Private Sub Worksheet_Change(ByVal Target As excel.RANGE)   'TOP MAIN WORKSHEET MACROS
        Dim A1 As String: A1 = RANGE("A1")  'whole mess of these (problem listed above)
        Dim A2 As String: A2 = RANGE("A2")
        Dim A3 As String: A3 = RANGE("A3")
        Dim A4 As String: A4 = RANGE("A4")
    With Target                     ' (end with at bottom)
      If Target.Count > 1 Then goEXIT                 'EVENTS   no dif:   application.EnableEvents = True: Exit Sub
    '==========  ==========  ==========  ==========  ==========
    'MACROS ON-OFF SWITCH  AAAAAAAAAAA1 >>
    If RANGE("A1").Value = "." Then      'macro safety on/off  MAIN START
    '==========  ==========  ==========  ==========  ==========
    If Left(Cells(activecell.row, "A:A").Text, 1) <> "." Then   'st yyy yes (endif at bot) 
    'by changing stop3 to:  IF 0 THEN,  and check if problem symptoms go away, you can cut location in half.

    IF 1 THEN    'STOP5    1.  NOPROB,  CHANGE TO "IF 0 THEN"  if no prob here, then move up 50% to stop 3

    IF 1 THEN    'STOP4    3.  YESPROB,  problem is 'AT' / below stop 4

    IF 1 THEN    'STOP3    2.  YESPROB,  if no prob here, then move up 50% to stop 4

    IF 1 THEN    'STOP2

    IF 1 THEN    'STOP1   

    'any / your vb,  dates eg:

        If Not Intersect(Me.RANGE(D4), .Cells) Is Nothing Then  'n2 hilite
          goEVENTSF               'EVENTS
          With Me.Cells(.row, E2).OFFSET(, 2)         'Destination    'now date or time,  colorindex 3: red, 0: black
            .NumberFormat = "yy.mm.dd": .Font.Size = 3: .Value = now: End With: goMODE        'EVENTS
        End If

    '>>>>>>>>>>>>
    'End If 'stop1
    '>>>>>>>>>>>>

    'your vb

    '>>>>>>>>>>>>
    'End If 'stop2
    '>>>>>>>>>>>>

    'your vb

    '>>>>>>>>>>>>
    'End If 'stop3
    '>>>>>>>>>>>>

    'your vb

    '>>>>>>>>>>>>
    'End If 'stop4
    '>>>>>>>>>>>>
    'your vb

    '>>>>>>>>>>>>
    'End If 'stop5
    '>>>>>>>>>>>>

    end if: end if
    end with
    end sub

    Wednesday, December 27, 2017 3:13 PM