Tests Copying, Pasting, API Cliipboard issues. and Rough notes on Advanced API stuff

[DocAElstein]

In support of this Thread
http://www.eileenslounge.com/viewtopic.php?f=30&t=35600

Sub MakeSomeStringsToCopyAndPasteIntoACode()
Dim CodeText As String
Dim Cnt As Long
Rem 1 For an ASCII array from  Split
'1a) Spaces :     Note: PROBLEM* If you use the  Split  way, then best is to avoid using  a single character as the separator. Otherwise you may have problems if you want that character in your Horizontal Array  of ASCII characters because it will be seen as a separator for Split. This means that you will not get that character in your list. Instead you will have 2 extra empty elements in your array, and all characters after where the character ( here the space)  should have been will appear offset by one place to the right in the horizontal array
    For Cnt = 1 To 200
     Let CodeText = CodeText & " & "" "" & ChrW(" & Cnt & ")"
    Next Cnt
 Let CodeText = Mid(CodeText, 10) '  take of first 9 bits of    Space&Space"Space"Space&Space
 Debug.Print CodeText
 Debug.Print
 Let CodeText = "" ' Empty so that i can use the varable again below
'1b) Use any 2 characters as the seperator to avoid PROBLEM*
    For Cnt = 1 To 200
     Let CodeText = CodeText & " & ""Sp"" & ChrW(" & Cnt & ")"
    Next Cnt
 Let CodeText = Mid(CodeText, 11) '  take of first 10 bits of    Space&Space"Sp"Space&Space
 Debug.Print CodeText
 Debug.Print
Rem 2 For ASCII array from  VBA Array(  ) function
 Let CodeText = "" ' Empty so that i can use the varable again below
    For Cnt = 1 To 200
     Let CodeText = CodeText & ", ChrW(" & Cnt & ")"
    Next Cnt
 Let CodeText = Mid(CodeText, 3) '  take off the first two characters   " ,"
 Debug.Print CodeText
 Debug.Print
End Sub
'   http://www.eileenslounge.com/viewtopic.php?f=30&t=35600

[DocAElstein]

Test post for later use

[DocAElstein]

Some extra notes for a few Posts
http://www.eileenslounge.com/viewtop...278892#p278892
https://www.myonlinetraininghub.com/...andling-in-vba
https://excelfox.com/forum/showthrea...GoRoT-N0Nula-1
https://excelfox.com/forum/showthrea...0559#post10559




On Error GoTo -1 is not equivalent of using Err.Clear. It does ( also) clear the error object, (equivalent of using Err.Clear ).
On Error GoTo -1 takes Excel out of the so called “exception state”. It also does clear the Err object registers, (equivalent of using Err.Clear ). But the later is secondary to its main function of “clearing the exception”.
The next macro has 3 identical erroring code lines . Just before each error we have an error handler, which we might expect would trap the error following it . All three error handlers are similar and are of the type On Error GoTo [LABEL] But we find that only the first two error handlers work….
In this macro the first and the second error handlers, of the type On Error GoTo [LABEL] are enabled, and so when an error occurs the coding jumps to the appropriate Label
The second Error handler would not have worked, that is to say the second error would not have been trapped without the code line of On Error GoTo -1 . On Error GoTo -1 has cleared the exception state.
The third error handler, also of the type On Error GoTo [LABEL], does not work. It does not work, that is to say the error is not trapped , because we are in the exception state. One of the characteristics of the exception state is that any attempt to enable an error handler will be ignored. Another characteristic of the exception state is that any enabled error handler, ( in this case the second one ) , will also be ignored.
It is also sometimes said in this situation that the second error handler is active and is still handling the second error. It cannot handle another error , and any further errors will be handled by the VBA default error handler

Sub OnErrorGoToMinus1_takes_Excel_out_of_the_so_called_exception_state() ' It also  does clear the Err object registers, (equivalent of using Err.Clear ). But the later is secondary to its main function of "clearing the exception"
Dim Rslt As Double
 
 On Error GoTo ErrHndlr1
 Let Rslt = 1 / 0   ' This error gets trapped by  ErrHndlr1
 MsgBox Prompt:="You will never see this", Title:="You will never see this"
Exit Sub   '   You will never come here in this demo macro, but its good practice to get in the habit of always doing this exit sub

ErrHndlr1:
Debug.Print Err.Number & vbCr & vbLf & Err.Description ' 11  Division durch Null
 On Error GoTo -1      ' the next line will give us no error infomation because the  On Error GoTo -1  has cleared the  Err  object registers
Debug.Print Err.Number & vbCr & vbLf & Err.Description '  0
 
 On Error GoTo ErrHndlr2 ' the main function of  On Error GoTo -1  is to "clear the exception" which means  this second error hanhler will work
 Let Rslt = 1 / 0   ' This error gets trapped by  ErrHndlr2
 MsgBox Prompt:="You will never see this", Title:="You will never see this"
Exit Sub   '   You will never come here in this demo macro, but its good practice to get in the habit of always doing this exit sub
 
ErrHndlr2:
Debug.Print Err.Number & vbCr & vbLf & Err.Description ' 11  Division durch Null
  ' I will not do  On Error GoTo -1  and see what happens...
  
 On Error GoTo ErrHndlr3
 Let Rslt = 1 / 0 ' This will be handled by the VBA default error handler: The error will not be trapped by the second error handler ,  ErrHndlr2
 MsgBox Prompt:="You will never see this", Title:="You will never see this"
Exit Sub   '   You will never come here in this demo macro, but its good practice to get in the habit of always doing this exit sub

ErrHndlr3:
' You will never come here.  The third error is not trapped: It will be handled by the VBA default error handler

End Sub

The following other error things also , in addition to their main function, clear the Err object registers –
_ On Error GoTo 0 ,
_ changing the error handler
_ Resume, ( Resume; Resume Next; Resume [label] )
-.....see next post

[DocAElstein]

Some extra notes for a few Posts
http://www.eileenslounge.com/viewtop...278892#p278892
https://www.myonlinetraininghub.com/...andling-in-vba
https://excelfox.com/forum/showthrea...GoRoT-N0Nula-1
https://excelfox.com/forum/showthrea...0559#post10559


_.....continued from last post

The following other error things also , in addition to their main function, clear the Err object registers –
_ On Error GoTo 0 ,
_ changing the error handler
_ Resume, ( Resume; Resume Next; Resume [label] ) ,
Here are 5 demos
1x On Error GoTo 0
1x changing the error handler
3x Resume, ( 1xResume; 1xResume Next; 1xResume [label] )

Sub OnErrorGoTo0ClearsErr() ' _ On Error GoTo 0
Dim Rslt As Double
 On Error Resume Next ' In simple terms this allows the code to contiunue as if no error had occured. It is not quite that simple, for example, the  Err  and  Error   are filled
Debug.Print Err & vbCr & vbLf & Error '   gives 0    The default  Err  property is the error number, so in this situation  Err  is taken as  Err.number    The exact working of  Error  is unclear, but if an error occurs it seems to do a few things, and one of them is that it returns the error description
 Let Rslt = 1 / 0
Debug.Print Err & vbCr & vbLf & Error '   gives   11  Division durch Null
 On Error GoTo 0      '  The main purpose of this is to disable our error handler and return to the default VBA error handler.  As a secodary function it seems to clear the  Err  registers
Debug.Print Err & vbCr & vbLf & Error '   gives 0
End Sub

Sub ChangingTheErrorHandlerClearsErr() ' _ changing the error handler
Dim Rslt As Double
 On Error Resume Next ' In simple terms this allows the code to contiunue as if no error had occured. It is not quite that simple, for example, the  Err  and  Error   are filled
Debug.Print Err & vbCr & vbLf & Error '   gives 0    The default  Err  property is the error number, so in this situation  Err  is taken as  Err.number    The exact working of  Error  is unclear, but if an error occurs it seems to do a few things, and one of them is that it returns the error description
 Let Rslt = 1 / 0
Debug.Print Err & vbCr & vbLf & Error '   gives   11  Division durch Null
 On Error Resume Next
Debug.Print Err & vbCr & vbLf & Error '   gives 0    because I have changed the error handler , ( admitedly in this case changed it to the same type )
 Let Rslt = 1 / 0
Debug.Print Err & vbCr & vbLf & Error '   gives   11  Division durch Null
 On Error GoTo Bed
Debug.Print Err & vbCr & vbLf & Error '   gives 0    because I have changed the error handler
 Let Rslt = 1 / 0
Exit Sub ' I don't need this since i never come here, but its good practice to get in the habit of having this above a typical Error handling code section.
Bed:
Debug.Print Err & vbCr & vbLf & Error '   gives   11  Division durch Null
End Sub

Sub Resume_ClearsErr() ' _ Resume, ( Resume )
Dim Rslt As Double, Demonostrator As Long
 On Error GoTo ErrHndler ' In simple terms this tells VBA to go the the label,  ErrHndler   Note however that if an error causes me to go there, then I will then be in the exception state.
Debug.Print Err & vbCr & vbLf & Error    '   gives 0    The default  Err  property is the error number, so in this situation  Err  is taken as  Err.number    The exact working of  Error  is unclear, but if an error occurs it seems to do a few things, and one of them is that it returns the error description
 Let Rslt = 1 / Demonostrator  '  Initially this causes me to go to  ErrHndler   but then the  Resume  brings me back to re try this code line
Debug.Print Err & vbCr & vbLf & Error    '   gives 0    The  Resume  cleared the  Err  registers
Exit Sub

ErrHndler:  ' Start of a what is commonly called an "error handling code section"
Debug.Print Err & vbCr & vbLf & Error '   gives   11  Division durch Null   The  Err   register  is always filled in all situatiuons when an error occurs, regardless of what error handler is or isn't in place.
 Let Demonostrator = 1  ' It is important to cure the problem causing the error here, or otherwise the next code line will cause an infinite loop because the next code line instructs VBA to go back and try the erroring code line again.   Note also that the  Resume  in the next code line also clears the error exception and clears the  Err   registers
Resume ' This clears the exception,  clears the  Err  registers, and instructs VBA to go back to the code line that errored and try again.  Because it instructs VBA to go back and try the erroring code line again, It is important to cure the problem causing the error before this code line, or else we will have an infinite loop
End Sub
Sub Resume_Next_ClearsErr() ' 'Resume, (  Resume Next )
Dim Rslt As Double
 On Error GoTo ErrHndler ' In simple terms this tells VBA to go the the label,  ErrHndler   Note however that if an error causes me to go there, then I will then be in the exception state.
Debug.Print Err & vbCr & vbLf & Error    '   gives 0    The default  Err  property is the error number, so in this situation  Err  is taken as  Err.number    The exact working of  Error  is unclear, but if an error occurs it seems to do a few things, and one of them is that it returns the error description
 Let Rslt = 1 / 0  '  This causes me to go to  ErrHndler  The  Resume Next  brings me back to just after this code line
Debug.Print Err & vbCr & vbLf & Error    '   gives 0    The  Resume Next  cleared the  Err  registers
Exit Sub

ErrHndler:  ' Start of a what is commonly called an "error handling code section"
Debug.Print Err & vbCr & vbLf & Error '   gives   11  Division durch Null   The  Err   register  is always filled in all situatiuons when an error occurs, regardless of what error handler is or isn't in place.
Resume Next ' This clears the exception,  clears the  Err  registers, and instructs VBA to go back to the code line just after that code line that errored
End Sub
Sub Resume_LABEL_ClearsErr() ' 'Resume, (  Resume [label] )
Dim Rslt As Double
 On Error GoTo ErrHndler ' In simple terms this tells VBA to go the the label,  ErrHndler   Note however that if an error causes me to go there, then I will then be in the exception state.
Debug.Print Err & vbCr & vbLf & Error    '   gives 0    The default  Err  property is the error number, so in this situation  Err  is taken as  Err.number    The exact working of  Error  is unclear, but if an error occurs it seems to do a few things, and one of them is that it returns the error description
 Let Rslt = 1 / 0  '  This causes me to go to  ErrHndler  The  Resume Lbl  brings me back to just after the label,  Lbl:

Lbl:
Debug.Print Err & vbCr & vbLf & Error    '   gives 0    The  Resume Lbl  cleared the  Err  registers
Exit Sub

ErrHndler:  ' Start of what is commonly called an "error handling code section"
Debug.Print Err & vbCr & vbLf & Error '   gives   11  Division durch Null   The  Err   register  is always filled in all situatiuons when an error occurs, regardless of what error handler is or isn't in place.
Resume Lbl ' This clears the exception,  clears the  Err  registers, and instructs VBA to go to the  code line just after the label  Lbl:
End Sub

If we use On Error GoTo [LABEL] and then use Resume ( Resume; Resume Next; Resume [label] ) then we appear to be having something done very similar, or possibly exactly the same as On Error GoTo -1 , since the exception state is cleared and the Err object is cleared.
To demonstrate this we can do the last three routines again, and simply add another error handler , for example On Error Resume Next , after the return point, and follow this by an error. If the error is handled, that is to say we get no default VBA error message, then we know that the exception had been cleared previously. If it had not been cleared then the new error handler, On Error Resume Next , would have been ignored and we would have seen the default VBA error handler warning pop up message.

_..... continued in next post

[DocAElstein]

Some extra notes for a few Posts
http://www.eileenslounge.com/viewtop...278892#p278892
https://www.myonlinetraininghub.com/...andling-in-vba
https://excelfox.com/forum/showthrea...GoRoT-N0Nula-1
https://excelfox.com/forum/showthrea...0559#post10559


_.... from last post

If we use On Error GoTo [LABEL] and then use Resume ( Resume; Resume Next; Resume [label] ) then we appear to be having something done very similar, or possibly exactly the same as On Error GoTo -1 , since the exception state is cleared and the Err object is cleared.
To demonstrate this we can do the last three routines again, and simply add another error handler , for example On Error Resume Next , after the return point, and follow this by an error. If the error is handled, that is to say we get no default VBA error message, then we know that the exception had been cleared previously. If it had not been cleared then the new error handler, On Error Resume Next , would have been ignored and we would have seen the default VBA error handler warning pop up message.

' If we use On Error GoTo [LABEL]  and then use Resume ( Resume; Resume Next; Resume [label] ) then we appear to be having something done very similar, or possibly exactly the same as On Error GoTo -1 ,  since the exception state is cleared and the Err object is cleared.
Sub Resume_ClearsErr_() ' _ Resume, ( Resume )
Dim Rslt As Double, Demonostrator As Long
 On Error GoTo ErrHndler
Debug.Print Err & vbCr & vbLf & Error
 Let Rslt = 1 / Demonostrator
Debug.Print Err & vbCr & vbLf & Error
 On Error Resume Next ' This would be ignored if I was in exception state.
 Let Rslt = 1 / 0 ' This does not give us an error , so the last code line worked, indicating that we were not in exception state
 On Error GoTo 0  ' I do not need to do this since I am  Exiting Sub  in next code line. But it is good practice to get in the habit of doing this to return to normnal default VBA error handling if i know i am finished using the  Error handler which I enabled
Exit Sub

ErrHndler:
Debug.Print Err & vbCr & vbLf & Error
 Let Demonostrator = 1
Resume
End Sub
Sub Resume_Next_ClearsErr_() ' 'Resume, (  Resume Next )
Dim Rslt As Double
 On Error GoTo ErrHndler
Debug.Print Err & vbCr & vbLf & Error
 Let Rslt = 1 / 0
Debug.Print Err & vbCr & vbLf & Error
 On Error Resume Next ' This would be ignored if I was in exception state.
 Let Rslt = 1 / 0 ' This does not give us an error , so the last code line worked, indicating that we were not in exception state
 On Error GoTo 0  ' I do not need to do this since I am  Exiting Sub  in next code line. But it is good practice to get in the habit of doing this to return to normnal default VBA error handling if i know i am finished using the  Error handler which I enabled
Exit Sub

ErrHndler:
Debug.Print Err & vbCr & vbLf & Error
Resume Next
End Sub
Sub Resume_LABEL_ClearsErr_() ' 'Resume, (  Resume [label] )
Dim Rslt As Double
 On Error GoTo ErrHndler
Debug.Print Err & vbCr & vbLf & Error
 Let Rslt = 1 / 0

Lbl:
Debug.Print Err & vbCr & vbLf & Error
 On Error Resume Next ' This would be ignored if I was in exception state.
 Let Rslt = 1 / 0 ' This does not give us an error , so the last code line worked, indicating that we were not in exception state
 On Error GoTo 0  ' I do not need to do this since I am  Exiting Sub  in next code line. But it is good practice to get in the habit of doing this to return to normnal default VBA error handling if i know i am finished using the  Error handler which I enabled
Exit Sub

ErrHndler:
Debug.Print Err & vbCr & vbLf & Error
Resume Lbl
End Sub

[DocAElstein]

Some extra notes for a few Posts
http://www.eileenslounge.com/viewtop...278892#p278892
https://www.myonlinetraininghub.com/...andling-in-vba
https://excelfox.com/forum/showthrea...GoRoT-N0Nula-1
https://excelfox.com/forum/showthrea...0559#post10559


As far as I can tell , the Err object is always filled with information about the last error that occurred, and it seems to me that its sole purpose is to have information about the last error. It can be cleared with Err.Clear , and , is also cleared as a secondary function of other things, including On Error GoTo -1
( In fact it appears the Err is actually a function or an object, possibly working like something similar to Range(xx) which can be regarded as an object or property or function depending on how you use it. We can probably say that Err is a function which returns the Err object. I think that possibly Error is also a similar function. I am not sure exactly what it does, but one thing it does is return the same as Err.Decription, so it can be used in place of Err.Description )

The main purpose of On Error GoTo -1 is to take Excel out of the exception state. The exception state is generally caused by an error occurring. An exception to this being , possibly, of when On Error Resume Next is used: But this is not clear to anyone, as far as I can tell: Its not clear whether
either:
On Error Resume Next prevents the excepting state occurring
or
On Error Resume Next cause the exception state to be cleared immediately after an error occurs.

If On Error Resume Next is used and an error occurs, then something similar to doing On Error GoTo -1 happens. But it is not exactly the same, since the Err object is not cleared, as it is by On Error GoTo -1

Sub OnErrorResumeNext() '  If On Error Resume Next is used and an error occurs, then something similar to doing On Error GoTo -1 happens. But it is not exactly the same, since the Err object is not cleared, as it is by On Error GoTo -1
Dim Rslt As Double
 On Error Resume Next
 Let Rslt = 1 / 0 ' It is generally thought that we are not in the exception state, but the next line does tell us what error occured, so the  On Error Resume Next  has not simply done a  On Error GoTo -1  , since  On Error GoTo -1  would have resulted inj the  Err  object being cleared which would mean that the next code line retuned us  0
Debug.Print Err & vbCr & vbLf & Error '   gives   11  Division durch Null   The  Err   register  is always filled in all situatiuons when an error occurs, regardless of what error handler is or isn't in place.
 On Error GoTo Bed
 Let Rslt = 1 / 0 ' We do not get a VBA default error here. We go to  Bed:  So the error handler worked indicating that we were not in the exception state
 On Error GoTo 0  ' I do not need this or the next code line, but its good to get into the habit of turning off any used error handler and having an  Exit Sub  above a typiucal endind error handling code section
 Exit Sub
Bed:
Debug.Print Err & vbCr & vbLf & Error '   gives   11  Division durch Null   The  Err   register  is always filled in all situatiuons when an error occurs, regardless of what error handler is or isn't in place.
End Sub

[DocAElstein]

Some extra notes for a few Posts
http://www.eileenslounge.com/viewtop...278892#p278892
https://www.myonlinetraininghub.com/...andling-in-vba
https://excelfox.com/forum/showthrea...GoRoT-N0Nula-1
https://excelfox.com/forum/showthrea...0559#post10559

The Exceptions State

The concept of the exception state is rarely understood.
The most noticeable effect of the error state is that further errors are dealt with by the default VBA error handling. It’s not relevant whether we are in the so called “error handling block” or not.

This frequently catches people out, in particular in a loop situation when error handling only works once, when they had been expecting it to trap all errors occurring: In the exception state, any enabled error handler won’t work again, and any attempt to use / enable another will be ignored.
( In this exception state, the On Error statement , On Error GoTo 0 , would do its main job of disabling any enabled error handler, but it won’t have any effect directly on anything, because it doesn’t clear the exception state. Its effect would only be noticed if the exception was cleared).

See here:
http://www.eileenslounge.com/viewtop...278892#p278892
http://www.eileenslounge.com/viewtop...278909#p278909
http://www.eileenslounge.com/viewtop...278922#p278922

[DocAElstein]

Some extra notes for a few Posts
http://www.eileenslounge.com/viewtop...278892#p278892
https://www.myonlinetraininghub.com/...andling-in-vba
https://excelfox.com/forum/showthrea...GoRoT-N0Nula-1
https://excelfox.com/forum/showthrea...0559#post10559


hhfhhsfhhfaskh

[DocAElstein]

macro or this Post:
https://excelfox.com/forum/showthrea...ll=1#post15165

Sub ConcatWithStyles() '  https://www.mrexcel.com/board/threads/the-hardest-question-in-excel-history-a-very-smart-vba-macro-to-combine-cell-with-their-styles.808781/#post-3954687
Rem 0a save the formulas, and replace with values
Dim arrFormulas() As Variant
 Let arrFormulas() = Range("A1:F1").Formula
 Let Range("A1:F1").Value = Range("A1:F1").Value
  Dim X As Long, Cell As Range, Text As String, Position As Long
  Range("A3").Value = Space(Evaluate("SUM(LEN(A1:F1))+COLUMNS(A1:F1)-1"))
  Position = 1
'  Application.ScreenUpdating = False
  For Each Cell In Range("A1:F1")
    With Range("A3")
      .Characters(Position, Len(Cell.Value)).Text = Cell.Characters(1, Len(Cell.Value)).Text
      For X = 1 To Len(Cell.Value)
        With .Characters(Position + X - 1, 1).Font
          .Name = Cell.Characters(X, 1).Font.Name
          .Size = Cell.Characters(X, 1).Font.Size
          .Bold = Cell.Characters(X, 1).Font.Bold
          .Italic = Cell.Characters(X, 1).Font.Italic
          .Underline = Cell.Characters(X, 1).Font.Underline
          .Color = Cell.Characters(X, 1).Font.Color
          .Strikethrough = Cell.Characters(X, 1).Font.Strikethrough
          .Subscript = Cell.Characters(X, 1).Font.Subscript
          .Superscript = Cell.Characters(X, 1).Font.Superscript
          .TintAndShade = Cell.Characters(X, 1).Font.TintAndShade
          .FontStyle = Cell.Characters(X, 1).Font.FontStyle
        End With
      Next
    End With
    Position = Position + Len(Cell.Value) + 1
  Next
  Application.ScreenUpdating = True
Rem 0b Put the formulas back
 Let Range("A1:F1").Formula = arrFormulas()
End Sub

[DocAElstein]

In support of these Thread answers:
' ' https://www.mrexcel.com/board/thread.../#post-3954687 https://excelfox.com/forum/showthrea...ll=1#post15170


It was seen ( https://excelfox.com/forum/showthrea...ll=1#post15168
https://excelfox.com/forum/showthrea...ll=1#post15167 ) when solving the formula in cell issue, that the cells containing the formula can only have a single style for all characters in the cell. So it’s not necessary to loop through the characters in each cell when setting the style in the concatenated string from those cells.

' '  https://www.mrexcel.com/board/threads/the-hardest-question-in-excel-history-a-very-smart-vba-macro-to-combine-cell-with-their-styles.808781/#post-3954687    https://excelfox.com/forum/showthread.php/2679-Concatenate-with-style?p=15170&viewfull=1#post15170
Sub ConcatWithStyles3()
Dim RngSel As Range: Set RngSel = Selection: Set RngSel = Range("A1:F1")
Rem 0a save any formulas, and replace with values
Dim arrFormulas() As Variant
 Let arrFormulas() = RngSel.Formula ' Assuming wew select more than one cell, we will always be presented by  .Value  a 2 dimensional array, ( even if it is a single row or single column )  This codel line will  error if we are using a selection of one cell, since in that case  .Value  only returns a single value which VBA syntax does not allow to  be assigned to a dynmic array
Dim RwCnt As Long, ClmCnt As Long
'    For RwCnt = 1 To RngSel.Rows.Count ' At each row we...._
'        For ClmCnt = 1 To RngSel.Columns.Count
'            If Left(arrFormulas(RwCnt, ClmCnt), 1) = "=" Then ' case a formula in cell
'             Let RngSel.Item(RwCnt, ClmCnt).Value = RngSel.Item(RwCnt, ClmCnt).Value ' replace the formula with its value
'            Else
'            End If
'        Next ClmCnt
'    Next RwCnt
Dim RwsCnt As Long, ClmsCnt As Long, Itm As Long, ItmCnt As Long
 Let ItmCnt = RngSel.Cells.Count
 Let RwsCnt = RngSel.Rows.Count: Let ClmsCnt = RngSel.Columns.Count
    For Itm = 1 To ItmCnt
        If Left(arrFormulas(Int((Itm - 1) / ClmsCnt) + 1, Int((Itm - 1) / RwsCnt) + 1), 1) = "=" Then ' case a formula in cell
         Let RngSel.Item(Itm).Value = RngSel.Item(Itm).Value ' replace the formula with its value
        Else
        End If
    Next Itm

Dim ExChr As Long, ACel As Range, TeExt As String, Position As Long
' Let Range("A3").Value = Space(Evaluate("SUM(LEN(A1:F1))+COLUMNS(A1:F1)-1"))  ' This makes a teExt of spaces. The number of spaces is the sum of all the teExt in the cells + one less than the number of cells. This gives us enough characters for all the teExt and a space betweeen them
 Let Range("A3").Value = Space(Evaluate("=SUM(LEN(" & RngSel.Address & "))+COLUMNS(" & RngSel.Address & ")-1"))
 Let Position = 1
' Let Application.ScreenUpdating = False    ' adding this code line may speed the macro up a bit

 Let Itm = 0
  For Each ACel In RngSel
 Let Itm = Itm + 1
    With Range("A3") ' The range ( cell ) used for final output of concatenated cell text with styles
      'here in next code line we put the characters in...
      .Characters(Position, Len(ACel.Value)).Text = ACel.Value ' ACel.Characters(1, Len(ACel.Value)).Text  ' ACel.Value  This puts the charascters
     If Left(arrFormulas(Int((Itm - 1) / ClmsCnt) + 1, Int((Itm - 1) / RwsCnt) + 1), 1) = "=" Then  ' We only need to consider the cell style, since individual styles on characters are not possible in a cell with a formula in it
             '  ....it's not necessary to loop through the characters in each cell when setting the style in the concatenated string from those cells containing formulas
         With .Characters(Position, Len(ACel.Value)).Font  ' all the characters from the current cell in the final concatenated string
          .Name = ACel.Font.Name
          .Size = ACel.Font.Size
          .Bold = ACel.Font.Bold
          .Italic = ACel.Font.Italic
          .Underline = ACel.Font.Underline
          .Color = ACel.Font.Color
          .Strikethrough = ACel.Font.Strikethrough
          .Subscript = ACel.Font.Subscript
          .Superscript = ACel.Font.Superscript
          .TintAndShade = ACel.Font.TintAndShade
          .FontStyle = ACel.Font.FontStyle
        End With '
    
     Else ' we need to consider all characters in the cell
        For ExChr = 1 To Len(ACel.Value) ' We are looping for all the tExt Chraracters in the current cell text
        ' here in the next  With End With  section  the next character in the final concatenated string  is  given the styles  that it had in the cell it came from
          With .Characters(Position + ExChr - 1, 1).Font '  A single character in the final concatenated string
            .Name = ACel.Characters(ExChr, 1).Font.Name
            .Size = ACel.Characters(ExChr, 1).Font.Size
            .Bold = ACel.Characters(ExChr, 1).Font.Bold
            .Italic = ACel.Characters(ExChr, 1).Font.Italic
            .Underline = ACel.Characters(ExChr, 1).Font.Underline
            .Color = ACel.Characters(ExChr, 1).Font.Color
            .Strikethrough = ACel.Characters(ExChr, 1).Font.Strikethrough
            .Subscript = ACel.Characters(ExChr, 1).Font.Subscript
            .Superscript = ACel.Characters(ExChr, 1).Font.Superscript
            .TintAndShade = ACel.Characters(ExChr, 1).Font.TintAndShade
            .FontStyle = ACel.Characters(ExChr, 1).Font.FontStyle
          End With '
        Next ExChr
     End If
    End With
    Position = Position + Len(ACel.Value) + 1 ' This takes us to posiion at the end of the current cell text  +1  ( +1
  Next ACel
  Application.ScreenUpdating = True
Rem 0b Put the formulas back
    For RwCnt = 1 To RngSel.Rows.Count ' At each row we...._
        For ClmCnt = 1 To RngSel.Columns.Count
            If Left(arrFormulas(RwCnt, ClmCnt), 1) = "=" Then ' ' case a formula was in cell
             Let RngSel.Item(RwCnt, ClmCnt).Formula = arrFormulas(RwCnt, ClmCnt) ' we put the formula back
            Else
            ' we didnt have a formula , so we do nothing to the cell - if we did then we would likely get just one style in the cell - a text with more than one style would revert to one single style throughout
            End If
        Next ClmCnt
    Next RwCnt
    

End Sub