![Avatar](https://www.myonlinetraininghub.com/wp-content/sp-resources/forum-avatars/defaults/userdefault.png)
![Level 0 (0) Level 0](https://www.myonlinetraininghub.com/wp-content/sp-resources/forum-reputation/reputation_level_0.png)
July 29, 2018
![sp_UserOfflineSmall](https://www.myonlinetraininghub.com/wp-content/sp-resources/forum-themes/reboot/images/lightpack/sp_UserOfflineSmall.png)
Dear,
I need help. I just want to copy registration codes from three different columns (columns I8, N8, S8), put them in an array and have it sorted.
Then print the content of an array to cells C8 downward.
[CODE]
Private Sub Worksheet_Activate()
Dim vItemCode As Range
Dim myArrayLst
Application.ScreenUpdating = False
With Sheets("SM - Traded Stocks")
For Each vItemCode In .Range("I8", .Range("I" & Rows.Count).End(xlUp))
If Left(vItemCode.Value, 1) <> "(" And vItemCode.Value <> "" Then
'if not yet added, add vItemCode to myArrayLst (to avoid having duplicate value)
'sort myArraLst content
End If
Next vItemCode
For Each vItemCode In .Range("N8", .Range("N" & Rows.Count).End(xlUp))
If Left(vItemCode.Value, 1) <> "(" And vItemCode.Value <> "" Then
'if not yet added, add vItemCode to myArrayLst and sort content
End If
Next vItemCode
For Each vItemCode In .Range("S8", .Range("S" & Rows.Count).End(xlUp))
If Left(vItemCode.Value, 1) <> "(" And vItemCode.Value <> "" Then
'if not yet added, add vItemCode to myArrayLst and sort content
End If
Next vItemCode
End With
'myArrayLst.Sort
'Debug.Print myArrayLst
'Sheets("SM - Traded Stocks").Range("C8").Resize(myArrayLst.Count).Value = Application.Transpose(myArrayLst.ToArray)
End Sub
[/CODE]
Hope somebody will help me with this problem.
Thank you.
![Avatar](https://www.myonlinetraininghub.com/wp-content/sp-resources/forum-avatars/defaults/userdefault.png)
![Level 0 (0) Level 0](https://www.myonlinetraininghub.com/wp-content/sp-resources/forum-reputation/reputation_level_0.png)
July 31, 2020
![sp_UserOfflineSmall](https://www.myonlinetraininghub.com/wp-content/sp-resources/forum-themes/reboot/images/lightpack/sp_UserOfflineSmall.png)
How about
Private Sub Worksheet_Activate()
Dim vItemCode As Range
Dim myArrayLst As Object
Set myArrayLst = CreateObject("system.collections.arraylist")
Application.ScreenUpdating = False
With Sheets("SM - Traded Stocks")
For Each vItemCode In .Range("I8", .Range("I" & Rows.Count).End(xlUp))
If Left(vItemCode.Value, 1) <> "(" And vItemCode.Value <> "" Then
If Not myArrayLst.contains(vItemCode.Value) Then myArrayLst.Add vItemCode.Value
End If
Next vItemCode
For Each vItemCode In .Range("N8", .Range("N" & Rows.Count).End(xlUp))
If Left(vItemCode.Value, 1) <> "(" And vItemCode.Value <> "" Then
If Not myArrayLst.contains(vItemCode.Value) Then myArrayLst.Add vItemCode.Value
End If
Next vItemCode
For Each vItemCode In .Range("S8", .Range("S" & Rows.Count).End(xlUp))
If Left(vItemCode.Value, 1) <> "(" And vItemCode.Value <> "" Then
If Not myArrayLst.contains(vItemCode.Value) Then myArrayLst.Add vItemCode.Value
End If
Next vItemCode
End With
myArrayLst.Sort
'Debug.Print myArrayLst
Sheets("SM - Traded Stocks").Range("C8").Resize(myArrayLst.Count).Value = Application.Transpose(myArrayLst.ToArray)
End Sub
![Avatar](https://www.myonlinetraininghub.com/wp-content/sp-resources/forum-avatars/defaults/userdefault.png)
![Level 0 (0) Level 0](https://www.myonlinetraininghub.com/wp-content/sp-resources/forum-reputation/reputation_level_0.png)
July 31, 2020
![sp_UserOfflineSmall](https://www.myonlinetraininghub.com/wp-content/sp-resources/forum-themes/reboot/images/lightpack/sp_UserOfflineSmall.png)
Sounds like you need to install the .NET Framework 2.0 which you can get here
![Avatar](https://www.myonlinetraininghub.com/wp-content/sp-resources/forum-avatars/defaults/userdefault.png)
![Level 0 (0) Level 0](https://www.myonlinetraininghub.com/wp-content/sp-resources/forum-reputation/reputation_level_0.png)
July 29, 2018
![sp_UserOfflineSmall](https://www.myonlinetraininghub.com/wp-content/sp-resources/forum-themes/reboot/images/lightpack/sp_UserOfflineSmall.png)
Dear Sir Fluff,
.NET Framework 2.0 installed but got the SAME result / problem.
For the mean time, I changed the approach and instead used this code to move on with my project. It's working fine (applied to my original excel file, long VBA code):
Private Sub Worksheet_Activate()
Dim vItemCode As Range
Dim myArrayLst
Dim vRNumApplication.ScreenUpdating = False
vRNum = 7With Sheets("03. TJ2 (JNL & Portfolio)")
For Each vItemCode In .Range("AB12", .Range("AB" & Rows.Count).End(xlUp))
If Left(vItemCode.Value, 1) <> "(" And vItemCode.Value <> "" Then
Range("O" & vRNum).Value = vItemCode
End If
vRNum = vRNum + 1
Next vItemCodevRNum = vRNum - 1
For Each vItemCode In .Range("AR12", .Range("AR" & Rows.Count).End(xlUp))
If Left(vItemCode.Value, 1) <> "(" And vItemCode.Value <> "" Then
Range("O" & vRNum).Value = vItemCode
End If
vRNum = vRNum + 1
Next vItemCodevRNum = vRNum - 1
For Each vItemCode In .Range("BH12", .Range("BH" & Rows.Count).End(xlUp))
If Left(vItemCode.Value, 1) <> "(" And vItemCode.Value <> "" Then
Range("O" & vRNum).Value = vItemCode
End If
vRNum = vRNum + 1
Next vItemCodevRNum = vRNum - 1
For Each vItemCode In .Range("BX12", .Range("BX" & Rows.Count).End(xlUp))
If Left(vItemCode.Value, 1) <> "(" And vItemCode.Value <> "" Then
Range("O" & vRNum).Value = vItemCode
End If
vRNum = vRNum + 1
Next vItemCode
End WithRange("O8", Range("O" & Rows.Count).End(xlUp)).RemoveDuplicates Columns:=1, Header:=xlNo
Range("O8", Range("O" & Rows.Count).End(xlUp)).Sort [O8], xlAscending
End Sub
But still, I want to learn how to use an ARRAY and how to manipulate its content so am still looking for the solution based on the given data.
Thanks, Sir Fluff.
![Avatar](/wp-content/sp-resources/forum-gravatar-cache/b36072d27fe0570e713521966c2ebd1a.jpeg)
![Level 0 (0) Level 0](https://www.myonlinetraininghub.com/wp-content/sp-resources/forum-reputation/reputation_level_0.png)
December 29, 2020
![sp_UserOfflineSmall](https://www.myonlinetraininghub.com/wp-content/sp-resources/forum-themes/reboot/images/lightpack/sp_UserOfflineSmall.png)
Hello SoniboiTM,
One small but important thing to start with.
You have at the start of your macro
Application.ScreenUpdating = False
But you have forgotten to put
Application.ScreenUpdating = True
at the end of your macro. That is very, very bad practice. You should always remember to include
Application.ScreenUpdating = True
at the end of your macro.
Better still, in my opinion, forget all about Application.ScreenUpdating until you have a working macro. Then later you can see if adding the Application.ScreenUpdating pair helps make your macro quicker. The use of these pair is only to speed up a macro, but it is very dangerous to use a Application.ScreenUpdating = False if you may forget to always have also a Application.ScreenUpdating = True
My personal advice to you would be to forget about Application.ScreenUpdating until after you have a full working macro. Then you can try later to see if including such a pair will speed up your macro
If you think you may have already previously used Application.ScreenUpdating = False , but you did then have forgotten to put Application.ScreenUpdating = True , then you may get some strange problems and/ or errors later.
If this is the case, then run this macro once. It may cure the problems
Sub Oops() ' This macro may help cure problems caused by if you forgot to "turn things back on"
Let Application.ScreenUpdating = True
Let Application.EnableEvents = True
Let Application.Calculation = xlCalculationAutomatic
End Sub
_._____________________________
I did try macro given to you (https://www.myonlinetraininghu.....lls#p19065 )
Fluffy’s macro is working for me. I tried in your file, as returned here below
Private Sub Worksheet_Activate() ' Fluffy macro https://www.myonlinetraininghu.....lls#p19065
Dim vItemCode As Range
Dim myArrayLst As Object
Set myArrayLst = CreateObject("system.collections.arraylist")
' Application.ScreenUpdating = False
With Sheets("SM - Traded Stocks")
For Each vItemCode In .Range("I8", .Range("I" & Rows.Count).End(xlUp))
If Left(vItemCode.Value, 1) <> "(" And vItemCode.Value <> "" Then
If Not myArrayLst.contains(vItemCode.Value) Then myArrayLst.Add vItemCode.Value
End If
Next vItemCode
For Each vItemCode In .Range("N8", .Range("N" & Rows.Count).End(xlUp))
If Left(vItemCode.Value, 1) <> "(" And vItemCode.Value <> "" Then
If Not myArrayLst.contains(vItemCode.Value) Then myArrayLst.Add vItemCode.Value
End If
Next vItemCode
For Each vItemCode In .Range("S8", .Range("S" & Rows.Count).End(xlUp))
If Left(vItemCode.Value, 1) <> "(" And vItemCode.Value <> "" Then
If Not myArrayLst.contains(vItemCode.Value) Then myArrayLst.Add vItemCode.Value
End If
Next vItemCode
End With
myArrayLst.Sort
'Debug.Print myArrayLst
Sheets("SM - Traded Stocks").Range("C8").Resize(myArrayLst.Count).Value = Application.Transpose(myArrayLst.ToArray)
End Sub
Macro from Fluff is good, I have no problems with it. It work for me in Excel 2003, 2007, 2010, 2013
_._________________________________________
Would you like a simple array solution?, like simple VBA array and VB string manipulation, but without using system.collections.arraylist ?
Such a simple approach would be how I personally would do such a macro.
I will do you such a solution later if you want it, and I will add explaining 'comments on it
Alan
![Avatar](/wp-content/sp-resources/forum-gravatar-cache/b36072d27fe0570e713521966c2ebd1a.jpeg)
![Level 0 (0) Level 0](https://www.myonlinetraininghub.com/wp-content/sp-resources/forum-reputation/reputation_level_0.png)
December 29, 2020
![sp_UserOfflineSmall](https://www.myonlinetraininghub.com/wp-content/sp-resources/forum-themes/reboot/images/lightpack/sp_UserOfflineSmall.png)
For example:
This is an approach similar to yours last :
You did do it …_
_... build up long row list in Excel column O , then take out duplicates, then sort it
I did do, do it have like:
_.. build up a long text string, ( but I don’t put in it anything that is already in it, so don’t need to take out any duplicates ) , then make an array from that text string, then sort that array, then write that array to cells
Sub SimpleListBuildAndSortArrays()
Dim strMyList As String: Let strMyList = vbCr & vbLf ' This will become a single string text containing all the unique STOCK CODE vbCr & vbLf is chosen arbrirarily as any character string to seperate our text data. I personally like to use vbCr & vbLf because then I can Debug.Print or MsgBox the string to see it as a virtical list
Dim arrTemp() As Variant ' below I will Assign Values to Array ( 3 times ) using .Value property The .Value property returns a field of elements The elements have variant types So we must declare our array as Variant or we would get a type mismatch error
With Sheets("SM - Traded Stocks")
Let arrTemp() = .Range("I8:I" & .Range("I" & Rows.Count).End(xlUp).Row & "").Value ' Assign Values to Array
Dim Rwnt As Long: Let Rwnt = 1 ' this will be used as the index number of our array
Do While Not arrTemp(Rwnt, 1) = "" ' I have assigned values, not formulas to my array, so I will have "" at some point and need to go no further
If Left(arrTemp(Rwnt, 1), 1) <> "(" And InStr(1, strMyList, vbCr & vbLf & arrTemp(Rwnt, 1) & vbCr & vbLf, vbBinaryCompare) = 0 Then Let strMyList = strMyList & arrTemp(Rwnt, 1) & vbCr & vbLf ' The Instr Function returns a 0 if it does not find the vbCr & vbLf & arrTemp(Rwnt, 1) & vbCr & vbLf in our main text string
Let Rwnt = Rwnt + 1
Loop ' While Not arrTemp(Rwnt, 1) = ""
Debug.Print strMyList ' The Debug.Print code lines are just for testing purposes. - Hit keys Ctrl+g from the VB editor to get the Immediatze Window up to see this print out. These can be removed
Let Rwnt = 1
Let arrTemp() = .Range("N8:N" & .Range("N" & Rows.Count).End(xlUp).Row & "").Value ' Assign Values to Array
Do While Not arrTemp(Rwnt, 1) = ""
If Left(arrTemp(Rwnt, 1), 1) <> "(" And InStr(1, strMyList, vbCr & vbLf & arrTemp(Rwnt, 1) & vbCr & vbLf, vbBinaryCompare) = 0 Then Let strMyList = strMyList & arrTemp(Rwnt, 1) & vbCr & vbLf
Let Rwnt = Rwnt + 1
Loop ' While Not arrTemp(Rwnt, 1) = ""
Debug.Print strMyList
Let Rwnt = 1
Let arrTemp() = .Range("S8:S" & .Range("S" & Rows.Count).End(xlUp).Row & "").Value ' Assign Values to Array
Do While Not arrTemp(Rwnt, 1) = ""
If Left(arrTemp(Rwnt, 1), 1) <> "(" And InStr(1, strMyList, vbCr & vbLf & arrTemp(Rwnt, 1) & vbCr & vbLf, vbBinaryCompare) = 0 Then Let strMyList = strMyList & arrTemp(Rwnt, 1) & vbCr & vbLf
Let Rwnt = Rwnt + 1
Loop ' While Not arrTemp(Rwnt, 1) = ""
Debug.Print strMyList
End With
Let strMyList = Mid(strMyList, 3, Len(strMyList) - 4) ' this takes off the first and last vbCr & vbLf which I no longer need
Debug.Print strMyList
' Now use Split function to make a 1 dimensional array from out string
Dim arrSpt() As String ' The VB string function returns Elements of string type, so we have to declare appropriately or we will get a type mismatch error in the nect code line
Let arrSpt() = Split(strMyList, vbCr & vbLf, -1, vbBinaryCompare) ' Note that by default the indicies start at 0 , like arrSpt(0)="APL" , arrSpt(1)="DD" , etc and for your sample data the last element is arrSpt(13) and we will have 14 elements with indicies of 0 1 2 3 4 5 6 7 8 9 10 11 12 13
' Sort
' Use a simple bubble sort on the array https://en.wikibooks.org/wiki/.....nal_Arrays
Dim Outer As Long, Inner As Long, strTemp As String
For Outer = 0 To UBound(arrSpt()) - 1 ' This loop goes from the first to last but one in the array
For Inner = Outer + 1 To UBound(arrSpt()) ' This loop goes from one above where the outer loop is up to the end of the array
If arrSpt(Outer) > arrSpt(Inner) Then ' If this is true, then the arrSpt(Outer) is bigger than the next being compared, so we swap them , which effectively moves the current arrSpt(Outer) up
Let strTemp = arrSpt(Inner)
Let arrSpt(Inner) = arrSpt(Outer) ' the bigger effectively goes up
Let arrSpt(Outer) = strTemp ' the smaller effectively goes down
Else
End If
Next Inner
Next Outer
' Write Values to Cells , noting that Ubound(arrSpt()) is 13 for your test data but we have 14 elements so need to resize to UBound(arrSpt()) + 1 = 13 + 1 = 14
Sheets("SM - Traded Stocks").Range("C8").Resize(UBound(arrSpt()) + 1).Value = Application.Transpose(arrSpt())
' or this below is my preferred way to do the same
Let Worksheets("SM - Traded Stocks").Range("C8").Resize(UBound(arrSpt()) + 1).Value = Application.Index(arrSpt(), Evaluate("=row(1:" & UBound(arrSpt()) + 1 & ")/row(1:" & UBound(arrSpt()) + 1 & ")"), Evaluate("=row(1:" & UBound(arrSpt()) + 1 & ")"))
End Sub
Let me know if you have any questions
Alan
![sp_AnswersTopicAnswer](https://www.myonlinetraininghub.com/wp-content/sp-resources/forum-themes/reboot/images/sp_AnswersTopicAnswer.png)
Answers Post
![Avatar](https://www.myonlinetraininghub.com/wp-content/sp-resources/forum-avatars/1673615137FB_Headtilt_small.jpg)
![](https://www.myonlinetraininghub.com/wp-content/sp-resources/forum-badges/vip.gif)
Trusted Members
Moderators
![Level 4 (870) Level 4](https://www.myonlinetraininghub.com/wp-content/sp-resources/forum-reputation/reputation_level_0.png)
November 1, 2018
![sp_UserOfflineSmall](https://www.myonlinetraininghub.com/wp-content/sp-resources/forum-themes/reboot/images/lightpack/sp_UserOfflineSmall.png)
It is not actually necessary to reset Screenupdating to True, unless you specifically need to force a screen redraw at some point within your code. That will happen automatically as long as your routine finishes normally - and if it doesn't the code at the end won't run anyway.
![Avatar](/wp-content/sp-resources/forum-gravatar-cache/b36072d27fe0570e713521966c2ebd1a.jpeg)
![Level 0 (0) Level 0](https://www.myonlinetraininghub.com/wp-content/sp-resources/forum-reputation/reputation_level_0.png)
December 29, 2020
![sp_UserOfflineSmall](https://www.myonlinetraininghub.com/wp-content/sp-resources/forum-themes/reboot/images/lightpack/sp_UserOfflineSmall.png)
I don’t understand the last bit of what you are saying. … and if it doesn't the code at the end won't run anyway … Huh?
Never mind,
But generally speaking about the Application.ScreenUpdating stuff….
I always had assumed it should be “switched back on”, I expect that was because a lot of people and Microsoft say so. But that, I have learned, doesn’t necessarily mean too much.
I thought I had occasionally experienced problems when I forgot to switch it back on, or possibly I was effected by one of the other things, hence I included in my Sub Oops() the three main things that sometimes get "forgotten to be “switched back on” "
I just had another quick internet search on the subject.
Opinions seem to be split. Here is a Blog you might want to take a look at, if you haven’t already.
https://www.atlaspm.com/toms-t.....heres-why/
It might be interesting for you to drop in a comment there, Veloria.. You seem to be clearly disagreeing with him about it being reset automatically, if that is what you are saying, I am not sure. ( If your argument is that its an old Blog and it is different now, then that’s no argument at all , since Microsoft we all know change things back and forth seemingly randomly ).
I may not have fully understood all the issues myself, but that guy, Tom Urtis, sounds convincing, so it could be worthwhile for you to argue the point there with him.
My uneducated guess is that it does no harm, and there is a chance at least, that it might be good idea, if not in this case, then possible in other situations. If nothing else I think its probably a good habit to get in the practise of switching things back after, or leaving things as you found them.
I have had a very bad habit of not switching water taps off sometimes. It hasn’t always been necessary, but certainly in the long run I think I would have been better off if I had got on the habit of putting things back as I had found them. I totally flooded a research lab in a University a few times. I think with hindsight it would have been better if I had got into the habit of switching water taps off, even if there may be no necessity, for example if you have no water meter, as I had for a very long time in my house in England, as I recall.
I think my comments / advice in screen updating were aimed at someone perhaps learning or new to VBA. I think I will stick to them… _
_....Always put Application.ScreenUpdating back to True if you put it to False, and better still , forget about Application.ScreenUpdating until after you have a working macro. Then you can go back later and investigate if a Application.ScreenUpdating pair improves the speed performance of your macro
If you then see any advantage then keep your pair.
Aesthetically it can also look quite pleasing to the eye, even beautiful with a nicely matched pair
Let Application.ScreenUpdating = False
'
'
'
'
'
‘
Let Application.ScreenUpdating = True
Alan
Edit P.S.
I suppose one could ask the question … why not get in the habit of putting Application.ScreenUpdating back to True?
I have heard an argument to not keep Setting objects back to Nothing, since in VBA apparently it is only very rarely necessary. The argument is then that its good practice to Set objects back to Nothing when you need to and not when you don’t need to , as this helps show the case when you do need to do it.
![Avatar](https://www.myonlinetraininghub.com/wp-content/sp-resources/forum-avatars/1673615137FB_Headtilt_small.jpg)
![](https://www.myonlinetraininghub.com/wp-content/sp-resources/forum-badges/vip.gif)
Trusted Members
Moderators
![Level 4 (870) Level 4](https://www.myonlinetraininghub.com/wp-content/sp-resources/forum-reputation/reputation_level_0.png)
November 1, 2018
![sp_UserOfflineSmall](https://www.myonlinetraininghub.com/wp-content/sp-resources/forum-themes/reboot/images/lightpack/sp_UserOfflineSmall.png)
What I mean is that it will only cause a problem if you terminate your code early (eg an error occurs and you press End rather than debug). In cases like that, having code at the end of your routine to reset screenupdating won't help.
However, always resetting it to True is not good practice. Just because you set it to False does not mean it was necessarily True at the time. At a minimum, your code should store the original value of Screenupdating when it starts and then reset it only if necessary. Otherwise, if you end up calling one routine from another (which is pretty much inevitable if your code is properly written), you'll end up turning screenupdating back on when you don't want it. However, if you only ever set it to False (unless you explicitly need a screen redraw for some reason), you don't need to worry about that. It will only be reset to True when all the code has finished.
A lot of things about coding in VBA are a matter of opinion, many of which are based on things that were true at one time. (the one about setting objects to Nothing is a classic case in point.)
![Avatar](/wp-content/sp-resources/forum-gravatar-cache/b36072d27fe0570e713521966c2ebd1a.jpeg)
![Level 0 (0) Level 0](https://www.myonlinetraininghub.com/wp-content/sp-resources/forum-reputation/reputation_level_0.png)
December 29, 2020
![sp_UserOfflineSmall](https://www.myonlinetraininghub.com/wp-content/sp-resources/forum-themes/reboot/images/lightpack/sp_UserOfflineSmall.png)
Thanks for the clarification, I think I see what your saying. You make some good points. I suppose I agree with them, but I didn’t want to over complicate the issue for the OP originally
About this …. will only cause a problem if you terminate your code early (eg an error occurs and you press End rather than debug). In cases like that, having code at the end of your routine to reset screenupdating won't help…… Often to get over that, I think often people will put the Application.ScreenUpdating back to True as part of error handling , some variation then of
Sub jecture()
On Error GoTo Bed
'
Let Application.ScreenUpdating = False
'
'
'
Bed:
Let Application.ScreenUpdating = True
End Sub
I didn’t want to over complicate it with that for the OP.
I personally never thought of any drawing / force or not re drawing issue. I have next to nothing to do with any sort of graph / graphic / drawing stuff. .
I was taking a guess that the OP was
_ not concerned with any drawing / force or not re drawing issues
and
_ that he likely had Application.ScreenUpdating set to True before the macro started, as I expect that’s usually the case.
I would guess further that the odds that an average OP’s macro will be called in something that might be badly effected by a Application.ScreenUpdating = True is less likely, but I think I agree with your general expanded point there , perhaps, more or less**
I think if you want to keep the thing simple and correct to a first approximation, then I stay by my advice, but as you say it’s a matter of opinion. IMO on average OPs are getting less likely to want to learn and advance. Sad but true, I think. The simple advice is turn things back on, but I do take the point that a more advanced way of thinking is put it back as it was, which might occasionally be turned off, but I think that will be quite rare. - **… code should store the original value of Screenupdating when it starts and then reset it only if necessary…” I suppose that might be right taking the thing a bit more advanced… but I didn’t want to over complicate the issue for the OP.
( I still think it might be worth you dropping a comment somewhere like at that Blog post, but that’s up to you, of course ;). In particular.. …. be reset to True when all the code has finished …..from you sounds to me like you are contradicting that chaps point… Supporters of this claim mistakenly believe that VBA “resets itself” at the end of a macro or procedure, ….. maybe the answer is , it did or didn't or / but nobody knows for sure anymore one way or the other...
I suppose my bottom line might be to stick with what I said originally, but then maybe in future, based on our discussions here I might add in brackets somethong like... , ( there might be advanced situations where we might need to re consider this, but for most situations its valid what I said )
So I geuss I've picked somethng up from your comments , (which I might add to some of my 'comments), so thanks
Alan
![Avatar](https://www.myonlinetraininghub.com/wp-content/sp-resources/forum-avatars/1673615137FB_Headtilt_small.jpg)
![](https://www.myonlinetraininghub.com/wp-content/sp-resources/forum-badges/vip.gif)
Trusted Members
Moderators
![Level 4 (870) Level 4](https://www.myonlinetraininghub.com/wp-content/sp-resources/forum-reputation/reputation_level_0.png)
November 1, 2018
![sp_UserOfflineSmall](https://www.myonlinetraininghub.com/wp-content/sp-resources/forum-themes/reboot/images/lightpack/sp_UserOfflineSmall.png)
If your code runs through error handling, then it terminates normally, so we're back to the original situation. My point was that the only real danger is if you terminate your code yourself (eg you are debugging and press End because you want to change something quickly rather than waste time running the rest of the code) then you can end up in a situation where the screen doesn't update.
Also, to clarify, when I talk about forcing a redraw, I mean a redraw of the screen (which is what setting Screenupdating = True actually does) not anything to do with graphics.
![Avatar](/wp-content/sp-resources/forum-gravatar-cache/b36072d27fe0570e713521966c2ebd1a.jpeg)
![Level 0 (0) Level 0](https://www.myonlinetraininghub.com/wp-content/sp-resources/forum-reputation/reputation_level_0.png)
December 29, 2020
![sp_UserOfflineSmall](https://www.myonlinetraininghub.com/wp-content/sp-resources/forum-themes/reboot/images/lightpack/sp_UserOfflineSmall.png)
_(i) …. If your code runs through error handling, then it terminates normally so we're back to the original situation …. I am not sure what you are referring to as .. back to the original situation .. in the meantime with that. But I get the feeling you are saying ScreenUpdating gets reset when a macro terminates normally. I don’t know personally if that is true or not. But others seem to disagree with you on that, if that’s what you are saying. Or as you say it wasn’t, then was, and who knows what it might be tomorrow ..
_(ii) …. My point was that the only real danger is if you terminate your code yourself (eg you are debugging and press End because you want to change something quickly rather than waste time running the rest of the code) then you can end up in a situation where the screen doesn't update….
If I am in debug mode ( say stepping with key F8 in a macro in the VB Editor ) and then hit my End key, then nothing happens.
Possibly that might be because I have a German Keyboard, - Often my keys don’t do like they seem to do in the VB editor or Excel on English computers
Or
Maybe you mean using the stop button during a debug step run like say when stepping with key F8 in a macro in the VB Editor. I have a feeling that this is the situation which may have caused me problems in the past, although I could not get any problems just now when I did that.
I am beginning to wonder if I might not have the problems I thought I had, and based my ideas on what others and Microsoft have said.
I think I still think its a general good habit of putting things back on , ( or as you suggest, better put them back as they were , which in this case is usually the on ), and I don’t think it does any harm to do it for the case of Application.ScreenUpdating
But I will be looking a bit closer next time something goes wrong and I had attributed it to “missing” a Application.ScreenUpdating = True
Its possible my preconceived ideas may have led me to oversee what was really going on in those situations……
My vote still goes for mostly putting it to true if you had put it to false
Maybe that policy of mine might change. If it does I will say why… hopefully in a way everyone will understand…
1 Guest(s)
![sp_Information](https://www.myonlinetraininghub.com/wp-content/sp-resources/forum-themes/reboot/images/lightpack/sp_Information.png)