Subroutine Processing Visuals

[steveh8204]

I've just finished creating my VBA spreadsheet and while it's very useful and 'a bit of a beast' it's not so impressive to look at while running. I played around with turning screenupdating on and off and either way doesn't make much difference so was just wondering if anyone had any tips on making it look better?

I'm running windows 10 and even though it appears to hang (the status bar says Not Responding for a few seconds) it does run but the screen is mainly white and with the aforementioned unresponsive message to the casual observer it looks sloppy. It runs for about 8-10 secs in total.

I did read a thread about a progress bar but the example didn't work as my sub routine is to run several sub routines so I wasn't able to base it on just on one calculation.

Any tips to 'clean it up' would be appreciated as it's the finishing touchs to a very long and painstaking project.

 

[steveh8204]

There's quite a bit of unneeded activating/selecting in that code which could explain, in part at least, your problem.

I would post alternative code but I'm having trouble understanding some of it.

For example, what's happening here?

  a = Range("A2:d200").Copy
    Workbooks("Macs Auto Stock Check.xlsm").Activate
    Paste = a
     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A3").Select

I'm assuming you are copying A2:D200 from a worksheet in the workbook you've opened to another worksheet in another workbook but I've never seen code like this for doing that, I can't actually get the code to compile.

This is to import data from an MHTML file so is the code that was generated by recording a Macro to basically Paste Values only. The file is exported from the source in that format so thats why I needed to Paste values only. I imagine there is a far easier way to do that just never got around to doing it.

 

[steveh8204]

As for the rest of the code I've just pasted it in and it does run a lot better. The screen stays as it should and it doesnt hang (thanks) although I've had an error (Run Time error 1004 "Application-defined or object-defined error") and the debugger has brought up the following:

Sub highlightvigo()
    For i = 3 To vcount + 2
        a = 0
        With Sheets("VigoStock")
            a = Application.WorksheetFunction.CountIf(Range("SAP!C:C"), .Cells(i, 1))
        
            If a = 0 Then
                With .Cells(i, l).Resize(, 7).Font ***This is the highlighted line in the debugger***
                    .Bold = True
                    .Color = -11489280
                    .TintAndShade = 0
                End With
            End If
        End With

The rest of the code works fine and completes as it should though. Just wish the status bar still don't go green with my grey writing though

 

[Norie]

Does the piece of code work at all?

When you get that error what's the value of i?

 

[steveh8204]

Sorry, I've had one of them days today in work. Managed to pop to my computer on the way out and found the value of i is 32.

The rest of the code works fine. This piece of code is the bit that is supposed to go through the spreadsheet and highlight any rows that arent on the other. I did initially try using conditional formatting for this but I couldnt get it to highlight the entire row so went for this bit of code instead.

 

[Norie]

Strange, I didn't really change much in that code.

The main thing was replacing the loop with Resize so the formatting was applied to all 7 columns in the range at the same time.

 

[steveh8204]

I've just had another look at the code and the loop youve removed is stil referred to in that line (variable l) so maybe its that. Line 32 could have been the first negative which has prompted the rest of the line (hence the error). I'll try it in work tomorrow.

I've just had a quick Google about the resize command too. A handy one to know. Thanks for that and your assistance with this issue. It's much appreciated.

 

[Norie]

It should be Cells(i,1) not Cells(i,l), hard to tell the difference, for me anyway, when using lower case characters.

 

[steveh8204]

Right the code works now (thanks again) except the very last sub. It stops and highlights the line below with the following error.

Sub vigozero()
Dim z As Variant, y As Variant, f As Double, h As Double
    With Sheets("SAP")
        For f = 3 To rcount
            z = .Cells(f, 4).Value
            y = .Cells(f, 6).Value
            For h = 1 To 4
            If z + y = 0 Then
                With .Cells(f, 1).Resize(, 4).Font
                    .Bold = True
                    .Color = -16776961
                    .TintAndShade = 0
                End With
            End If
        Next f ******Compile error: Invalid Next control variable reference******
    End With
    
End Sub

any ideas?

 

[Norie]

First thought is get rid of this.

For h = 1 To 4

 

[steveh8204]

First thought is get rid of this.

For h = 1 To 4

Yea good point thats not really doing much is it. Right runs a lot better now. I'm still getting a blank screen but I've got my ribbon now. I think a lot of it is because its a network file. When I was debugging it on a local drive it looked a lot better.

I don't get 'Not responding' anymore so I'm happy now. Thanks for your help. I'm going to compare the two codes as well to see where I can get my code more efficient. The Resize command has been a useful one to learn.

The only thing I can't understand though is the top left cell on one of the spreadsheets is highlighted and bold red all the time. I imagine its something to do with how the loop starts in the VigoZero sub (below) but I cant quite figure it out.

Sub vigozero()
Dim z As Variant, y As Variant, f As Double, h As Double
For f = 3 To rcount
z = Cells(f, 4).Value
y = Cells(f, 6).Value
For h = 1 To 4
If z + y = 0 Then Cells(f, 1).Resize(, 4).Select
    With Selection.Font
        .Bold = True
        .Color = -16776961
        .TintAndShade = 0
    End With
    Next h
    Next f
End Sub

Right gotta do some work thanks again.