Stop the mahem! The VBA Windows collection changes each time you activate a new windows. How to get some sense back!

Ok – so this guy give me the one tip that mattered. Application.Windows(1) is the active window. ALSO – you can change the caption of the window. IF you have a workbook and need to show  2 or more worksheets in as many windows – then it renames the sheets and adds :1 and :2 – which DO NOT CHANGE. But the windows(1) is NOT reliably window(1) the next time you interact with the windows.

So if you want sheet1’s window to return sheet1 and sheet2’s window to interact with sheet2 – then here is some code to help one understand what is going on. What I am going to do is change the :1 to :DELTA and :2 to :SOURCE and ensure that those windows show the delta and source worksheets respectively

[vb]

Sub Test_WindowIndexes()
‘ Here is the guy to inspired me to fiddle and get some understanding. It is mental but
‘ there is some hope is his bottom line. https://www.engram9.info/excel-vba-programming-3/the-workbook-and-window-objects.html

‘ before you run this – make a workbook with 2 worksheets
‘ rename them something memorable

‘ Next – simply make a new window . Use hte View Ribbon->New Window button
‘ or Set mywb = workbook.newWindow command here in VBA (in future)

Debug.Print "*********************new" ‘ just a line so you can see what
‘is going on between running this and loooking in the immediate window

Debug.Print "activesheet: " & ActiveSheet.Parent.Windows(1).Caption
ActiveSheet.Parent.Windows(1).Caption = VBA.Replace(ActiveSheet.Parent.Windows(1).Caption, ":1", ":DELTA")
Dim vvar As Variant
Dim w As Window

‘ lets set aside some static instances of the windows as they are right now
Dim winds As New Collection
winds.Add ActiveSheet.Parent.Windows(1) ‘ the active screen
winds.Add ActiveSheet.Parent.Windows(2) ‘ some random worksheet

‘ lets cycle through the worksheets.
‘ Set a breakpoint AFTER the Next – play with the windows and re-run this loop.
‘ the Application.windows collection CHANGES INDEXES – how unstable!
For Each vvar In ActiveSheet.Parent.Windows
Set w = vvar
Debug.Print "collection: " & w.Caption & " –> " & w.ActiveSheet.Name
Next

‘ now let VBA muck with the active window
winds(1).activate
Debug.Print ".windows(1):" & Application.Windows(1).Caption
Debug.Print "w1:" & winds(1).Caption
Debug.Print "w2:" & winds(2).Caption

Debug.Print " … now switch to winds(2) as active"
winds(2).activate
Debug.Print ".windows(1):" & Application.Windows(1).Caption
Debug.Print "w1:" & winds(1).Caption
Debug.Print "w2:" & winds(2).Caption

‘ wind(1) and winds(2) are finally static and no longer dependant on windows(1)
‘ got a solution now. How mental is this!? Perhaps this was out the Excel Team’s area
‘ of control. The Excel team would not have let this mahem propagte.

End Sub

[/vb]

Precedents limitations in Excel Formulas – VBA

I am trying to find the fastest way to store and retrieve a reference to a cell in another workbook. The sheet I am making is a delta record of what a spreadsheet that archives where data USED to be and make a quick link to get back to that data. So – I thought I used a simple formula =’somesheet somewhere’!$A$2 then I could intelligently pick up its information using a formula. Well – in short you CAN – if it is not off sheet. All mine are a off sheet – so the trick is to show to “Trace Precedents” arrows and use the Range.NavigateArrow() method to look back. THAT WILL WORK – but I think I will just store the long string and use Range(“‘somesheet somewhere’!$A$2” ) and be done with it. I think it will be less compute intensive. The guy in this article did all that cool stuff I am NOT going to use – very ingenious. Thanks Excel for exposing all sorts of goodies for us to use.

Hard Drives and speed of writing – what we need to know to maximize

First start by reading this very well written article that explains all one needs to know in simple terms. SSDs, Sata I, II, III hard drives, flash drives, thumb drives and SD cards are all discussed. In short lets follow the data flow. The SLOWEST part is what YOU need to address – lets discuss all the parts

a) bus speed – so USB 2, eSATA, 3.0, thunderbolt, 3.1, thunderbolt 2 and thunderbolt 3 are IN ORDER the slowest to fastest. (usb 3 vs thunderbolt 2) This is talking about the computer that one plugs things into. (I might have the order slightly off – so comment if I do) … Continue reading

Dealing with Double Precision Applanix data times – all the links to make it work

I was asked to look into why 1 of 3 parts of data was 6 days earlier than the other 2 parts. Well it turned out to NOT be IGES data – but it was raw binary data dumps from a LiDAR system. The data was Applanix proprietary data format that is likely written the way it is to allow high speed data writing. First, all the data is bound by $GRPXX and $# where XX is a hex group code. To undo all the data and make some sense of the time stamps it took an awful lot of digging. Here are the links and learning I found. Continue reading