Revision in code requested to avoid hanging of Excel Workbook

H

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
1,213
Office Version
  1. 2010
Platform
  1. Windows
  2. Mobile
I am using a Function findhigh as

Rich (BB code): 
Function findhigh(a As Range, b As Range, c As Range) As String

In the code.
Then I am finding number of 1s in c Range using
Rich (BB code): 
y = Application.CountIf(c, 1)
Now the code works smoothly when

Rich (BB code): 
If y >= 3 Then
     If a(y) = 0 Or a(y) = "" Then GoTo finish
     If b(y) = 0 Or b(y) = "" Then GoTo finish
Do While y > 1
          hv = a(y): lv = b(y)
      For q = y - 1 To 1 Step -1
            If a(q) > hv And b(q) < lv Then
                     findhigh = Format(a(q), "0.00")
                     GoTo finish
            End If
          y = y - 1
       Next q
Loop
End If

But the excel workbook gets hanged when

Rich (BB code): 
If y = 2 Then
       If a(y) = 0 Or a(y) = "" Then GoTo finish
        If b(y) = 0 Or b(y) = "" Then GoTo finish
                     If a(y - 1) > a(y) And b(y - 1) < b(y) Then
                                               findhigh = Format(a(y - 1), "0.00")
                                               GoTo finish

                      End If
End If

I am finishing the code with

Rich (BB code): 
findhigh = "null"
         finish:
End With
End Function

What mistake is there in the code? Any revision/help so that hanging issue of the workbook gets solved will be highly appreciated.
 
Sort by date Sort by votes
There's nothing obvious in the code you've posted that would hang the workbook. You've also said that the code seems to work fine in a new workbook, and to work in most circumstances in your current workbook.

It's clear from your descriptions above, and the lengthy discussion here: Can formula be removed by Excel that there is a lot going on in your workbook - including live data feeds, and other code that triggers based on the system time. We won't be able to replicate your workbook's behaviour.

Your other thread refers to code being triggered at 9:30. In this thread you say the problem happens between 9:45 and 10:00. That's a pretty strong hint that you should start by looking at what your other code might be doing around that time?
 
Upvote 0

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
StephenCrump said:
There's nothing obvious in the code you've posted that would hang the workbook. You've also said that the code seems to work fine in a new workbook, and to work in most circumstances in your current workbook.

It's clear from your descriptions above, and the lengthy discussion here: Can formula be removed by Excel that there is a lot going on in your workbook - including live data feeds, and other code that triggers based on the system time. We won't be able to replicate your workbook's behaviour.

Your other thread refers to code being triggered at 9:30. In this thread you say the problem happens between 9:45 and 10:00. That's a pretty strong hint that you should start by looking at what your other code might be doing around that time?
Click to expand...
  1. Your quote You've also said that the code seems to work fine in a "new workbook" NEVER SAID
  2. There is some problem in the code since when I delete the entire code from the Module, my Workbook & all its ws & some ws with Worksheet codes (28 such ws with ws code precisely) WORKS FINE.
  3. The problem PERSISTS ONLY WHEN ‘D10=1 AND E10=0’ OR ‘D10=1 AND E10=1’ & as soon as F10=1, ALL PROBLEMS VANISHES.
  4. So it leads to the conclusion that the trouble is in 2 parts of the codes.
  • 1st part: the line/(s) starting with
If y = 2 And Sheets("ZZZ").Range("F10").Value < 1 Then ….& ending with Next q Loop End If

  • 2nd part: the line/(s) starting with
  • If y = 1 And Sheets("ZZZ").Range("E10").Value < 1 Then……& ending with GoTo finish End If
  • These 2 parts are the ‘grey areas’ of the code.
 
Upvote 0
hsandeep said:
Your quote You've also said that the code seems to work fine in a "new workbook" NEVER SAID
Click to expand...
Yes, sorry. I got that wrong. I should have re-read the thread, rather than relying on memory.

hsandeep said:
The problem PERSISTS ONLY WHEN ‘D10=1 AND E10=0’ OR ‘D10=1 AND E10=1’ & as soon as F10=1, ALL PROBLEMS VANISHES.
Click to expand...

If D10 is 1 and E10 is 0 (and I think this means F10:M10 is all zero), then your Findhigh function distils down to this:

VBA Code: 
Function findhigh(a As Range, b As Range, c As Range) As String

    y = Application.WorksheetFunction.CountIf(Sheets("ZZZ").Range("D10:M10"), "=1")
    'So y =1
  
    'and this IF condition will be TRUE
    If y = 1 And Sheets("ZZZ").Range("E10").Value < 1 Then
        If a(y) = 0 Or a(y) = "" Then GoTo finish
        If b(y) = 0 Or b(y) = "" Then GoTo finish
        findhigh = Format(a(y), "0.00")
        GoTo finish
    End If

finish:

End Function

So: =findhigh(D4:M4,D6:M6,SomeRangeC) will return either a blank string (if D4 or D6 is blank or zero) or the formatted value of D4 if non-zero.

By itself, this function can't hang the workbook.

If you can post a link to a workbook where the function does hang Excel, I'd be very happy to take a look.
 
Last edited:
Upvote 0
StephenCrump said:
Yes, sorry. I got that wrong. I should have re-read the thread, rather than relying on memory.



If D10 is 1 and E10 is 0 (and I think this means F10:M10 is all zero), then your Findhigh function distils down to this:

VBA Code: 
Function findhigh(a As Range, b As Range, c As Range) As String

    y = Application.WorksheetFunction.CountIf(Sheets("ZZZ").Range("D10:M10"), "=1")
    'So y =1
 
    'and this IF condition will be TRUE
    If y = 1 And Sheets("ZZZ").Range("E10").Value < 1 Then
        If a(y) = 0 Or a(y) = "" Then GoTo finish
        If b(y) = 0 Or b(y) = "" Then GoTo finish
        findhigh = Format(a(y), "0.00")
        GoTo finish
    End If

finish:

End Function

So: =findhigh(D4:M4,D6:M6,SomeRangeC) will return either a blank string (if D4 or D6 is blank or zero) or the formatted value of D4 if non-zero.

By itself, this function can't hang the workbook.

If you can post a link to a workbook where the function does hang Excel, I'd be very happy to take a look.
Click to expand...
If D10 is 1 and E10 is 0 (and I think this means F10:M10 is all zero) YES F10:M10 IS ALL ZERO WHEN D10 IS 1 AND E10 IS 0
THE ABOVE CODE LINES ARE ALREADY THERE IN VBA (IS THERE ANY DIFFERENCE BTW YOUR'S & MINE?)
=findhigh(D4:M4,D6:M6,SomeRangeC) Could not understand what is SomeRangeC
 
Upvote 0
hsandeep said:
=findhigh(D4:M4,D6:M6,SomeRangeC) Could not understand what is SomeRangeC
Click to expand...

Your code used to do this:

VBA Code: 
Function findhigh(a As Range, b As Range, c As Range) As String

    '...
    y = Application.CountIf(c, 1)
    '...

But your latest test version now hard-codes D10:M10 and doesn't use c, hence it doesn't matter what c is specified:

Code: 
Function findhigh(a As Range, b As Range, c As Range) As String

    '...
    y = Application.WorksheetFunction.CountIf(Sheets("ZZZ").Range("D10:M10"), "=1")
    '...

In practice, your cell formula is probably still: =findhigh(D4:M4,D6:M6,D10:M10). So let's use that.

hsandeep said:
If D10 is 1 and E10 is 0 (and I think this means F10:M10 is all zero) YES F10:M10 IS ALL ZERO WHEN D10 IS 1 AND E10 IS 0
THE ABOVE CODE LINES ARE ALREADY THERE IN VBA (IS THERE ANY DIFFERENCE BTW YOUR'S & MINE?)
Click to expand...
If D10 is 1, and E10:M10 are all 0, then in Findhigh, y will be 1. This means that most of the code won't be executed, e.g. all this:

Rich (BB code): 
If y >= 3 Then
    If a(y) = 0 Or a(y) = "" Then GoTo finish
    If b(y) = 0 Or b(y) = "" Then GoTo finish
    Do While y > 1
        hv = a(y): lv = b(y)
        For q = y - 1 To 1 Step -1
            If a(q) > hv And b(q) < lv Then
                findhigh = Format(a(q), "0.00")
                GoTo finish
            End If
            y = y - 1
        Next q
    Loop
End If

If y = 2 And Sheets("ZZZ").Range("F10").Value < 1 Then
    If a(y) = "" Then GoTo finish
    If b(y) = "" Then GoTo finish
    If a(y) = 0 Or b(y) = 0 Then
        findhigh = Format(a(y - 1), "0.00")
        GoTo finish
    End If
    
    q = y - 1
    Do While q <> 0
        hv = a(y): lv = b(y)
        For q = y - 1 To 1 Step -1
            If a(q) > hv And b(q) < lv Then
                findhigh = Format(a(q), "0.00")
                GoTo finish
            End If
            y = y - 1
        Next q
    Loop
End If

The code I posted in #23 is what's left. Very simple code, no looping, nothing that should hang Excel.

This must be very frustrating for you, and we'd like to help. But based on everything posted so far, I can't replicate the problem, and I can't see anything in your findhigh and findlow functions that would hang the workbook.

As I said above, If you can post a link to a workbook where the function does hang Excel, I'd be very happy to take a look.
 
Upvote 0
StephenCrump said:
Your code used to do this:

VBA Code: 
Function findhigh(a As Range, b As Range, c As Range) As String

    '...
    y = Application.CountIf(c, 1)
    '...

But your latest test version now hard-codes D10:M10 and doesn't use c, hence it doesn't matter what c is specified:

Code: 
Function findhigh(a As Range, b As Range, c As Range) As String

    '...
    y = Application.WorksheetFunction.CountIf(Sheets("ZZZ").Range("D10:M10"), "=1")
    '...

In practice, your cell formula is probably still: =findhigh(D4:M4,D6:M6,D10:M10). So let's use that.


If D10 is 1, and E10:M10 are all 0, then in Findhigh, y will be 1. This means that most of the code won't be executed, e.g. all this:

Rich (BB code): 
If y >= 3 Then
    If a(y) = 0 Or a(y) = "" Then GoTo finish
    If b(y) = 0 Or b(y) = "" Then GoTo finish
    Do While y > 1
        hv = a(y): lv = b(y)
        For q = y - 1 To 1 Step -1
            If a(q) > hv And b(q) < lv Then
                findhigh = Format(a(q), "0.00")
                GoTo finish
            End If
            y = y - 1
        Next q
    Loop
End If

If y = 2 And Sheets("ZZZ").Range("F10").Value < 1 Then
    If a(y) = "" Then GoTo finish
    If b(y) = "" Then GoTo finish
    If a(y) = 0 Or b(y) = 0 Then
        findhigh = Format(a(y - 1), "0.00")
        GoTo finish
    End If
   
    q = y - 1
    Do While q <> 0
        hv = a(y): lv = b(y)
        For q = y - 1 To 1 Step -1
            If a(q) > hv And b(q) < lv Then
                findhigh = Format(a(q), "0.00")
                GoTo finish
            End If
            y = y - 1
        Next q
    Loop
End If

The code I posted in #23 is what's left. Very simple code, no looping, nothing that should hang Excel.

This must be very frustrating for you, and we'd like to help. But based on everything posted so far, I can't replicate the problem, and I can't see anything in your findhigh and findlow functions that would hang the workbook.

As I said above, If you can post a link to a workbook where the function does hang Excel, I'd be very happy to take a look.
Click to expand...
StephenCrump
Now my gut feeling is that you will solve this ‘troubling’ issue i.e. findhigh & findlow populating #VALUE! (Excel is hanged ONLY WHEN #VALUE! is populated ELSE NOT HANGED) When y=1 or 2 (y=1 WHEN ‘D10=1 AND E10=0’; y=2 WHEN ‘D10=1 AND E10=1’). Here I would like to ask whether the below code line being used currently in the vba for performing this is correct or not?

Rich (BB code): 
Dim y As Integer
y = Application.WorksheetFunction.CountIf(Sheets("ZZZ").Range("D10:M10"), "=1")
  1. Worksheet(“ZZZ”) has D10:M10 (which was c As Range) & generates values=0 to 10. By default D10:M10=0 so c (or y in new vba)=0
  2. a Range & b Range never generates “” (null) or space/(s). It generates 0 or +ve numerical values only
I want the code should perform as
  1. When y=0 Then ‘do nothing’ i.e. findhigh & findlow=blank (blank is default value of findhigh & findlow cells)
  2. y=1, findhigh=a(1); findlow=b(1)
  3. y=2, findhigh=a(1) if a(1)>a(2) AND b(1)<b(2); findlow=b(1) if a(1)>a(2) AND b(1)<b(2) ELSE findhigh=a(1); findlow=b(1)
  4. y>=3, code works properly.
  5. Additional: If WA10=1 Then findhigh=blank;findlow=blank (when WA10 will become 1 from 0, then y would be equal to 10; by default WA10=0)
 
Upvote 0
Can you please post an example of Function findhigh returning #VALUE, i.e. the cell formula and all the range a, range b and Sheets("ZZZ").Range("D10:M10") cell values?

If the cell values are all numeric, as you say, I don't see how the function can return #VALUE.
 
Upvote 0
StephenCrump said:
Can you please post an example of Function findhigh returning #VALUE, i.e. the cell formula and all the range a, range b and Sheets("ZZZ").Range("D10:M10") cell values?

If the cell values are all numeric, as you say, I don't see how the function can return #VALUE.
Click to expand...
StephenCrump
First I would say you are MVP & that means something with messages more than 4800
Secondly you are a good human being who tries to understand & listen to someone’s problem.
Thirdly, my feeling is more stronger that you can get me out of this trouble.
Now, the peculiar thing is the code performs well when y=0 or y>=3. But it gives trouble when y=1 & then y=2.
Here I would like to ask the code lines
Rich (BB code): 
Dim y As Integer
y = Application.WorksheetFunction.CountIf(Sheets("ZZZ").Range("D10:M10"), "=1")
Is it correctly written? OR any change is recommended?
Is Dim y As Integer needed?
What this line results into i.e. what output/(s) can be y?
 
Upvote 0
hsandeep said:
Here I would like to ask the code lines
Rich (BB code): 
Dim y As Integer
y = Application.WorksheetFunction.CountIf(Sheets("ZZZ").Range("D10:M10"), "=1")
Is it correctly written? OR any change is recommended?
Click to expand...
These two lines won't cause any problem.

And I don't think any of the code you've posted is causing the workbook to hang.

Are you able to provide an example for my previous question?
StephenCrump said:
Can you please post an example of Function findhigh returning #VALUE, i.e. the cell formula and all the range a, range b and Sheets("ZZZ").Range("D10:M10") cell values?

If the cell values are all numeric, as you say, I don't see how the function can return #VALUE.
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question
VBA to remove formulas with values causing wrong calculations
2
Replies
12
Views
272
Peter_SSs
Peter_SSs
J
  • Question
Combining VBA code together...need help!
Replies
3
Views
167
Fluff
Fluff
gtd526
  • Solved
Move selected row to top of range
Replies
6
Views
128
gtd526
gtd526
S
  • Solved
Golf Skins
Replies
3
Views
296
Fluff
Fluff
gtd526
  • Solved
vba selecting entire range from offset
Replies
5
Views
167
Tetra201
Tetra201

Forum statistics

Threads
1,214,944
Messages
6,122,392
Members
449,081
Latest member
JAMES KECULAH

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top