Notes tests. Excel VBA Folder File Search

**DocAElstein** · 09-16-2015, 03:25 PM

**DocAElstein** · 09-16-2015, 06:44 PM

Coding for these Threads
https://stackoverflow.com/questions/...ication-ontime
http://www.excelfox.com/forum/showth...ll=1#post11870
https://stackoverflow.com/questions/...12342#59812342

Open workbook - MainFile.xls : https://app.box.com/s/prqhroiqcb0qccewz5si0h5kslsw5i5h

Module "Modul1" in MainFile.xls
(This is the main module from which all macros are run)

Code:

Option Explicit
' Public variable code section
Private Pbic_Arg1 As String
Public Pbic_Arg2 As Double


Dim sTemp As String
 ' _
_
 
 
Sub MainMacro()    '    https://stackoverflow.com/questions/31439866/multiple-variable-arguments-to-application-ontime/31464597       http://markrowlinson.co.uk/articles.php?id=10
Rem 1
                                                                                                                                                                                                                          Debug.Print "Rem 1" & vbCr & vbLf & "This workbook module, single arrgument"
' This workbook module, single argument
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'"            &           "!'Modul1.UnderMainMacro 465'": Debug.Print "!'Modul1.UnderMainMacro 465'"
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'"            &           "!'Modul1.UnderMainMacro ""465""'": Debug.Print "!'Modul1.UnderMainMacro ""465""'"
 Application.OnTime Now(), "'Modul1.UnderMainMacro  465'" '  --- more usual simplified form. In this case I nned the extra  Modul1.  because Sub UnderMainMacro( ) is private
                                                                                                                                                                                                                          Debug.Print vbCr & vbLf & "UverFile module, single argument"
' UverFile module, single argument
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & "UverFile.xls" & "'"               &            "!'Modul1.MacroInUverFile 465'": Debug.Print "!'Modul1.MacroInUverFile 465'"
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & "UverFile.xls" & "'"               &             "!'Modul1.MacroInUverFile ""465""'": Debug.Print "!'Modul1.MacroInUverFile ""465""'"
                                                                                                                                                                                                                          Debug.Print vbCr & vbLf & "Thisworkbook module, multiple arguments"
' Thisworkbook module, multiple arguments
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'"             &           "!'Modul1.UnderUnderMainMacro 465, 25'": Debug.Print "!'Modul1.UnderUnderMainMacro 465, 25'"
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'"             &           "!'Modul1.UnderUnderMainMacro 465, ""25""'": Debug.Print "!'Modul1.UnderUnderMainMacro 465, ""25""' "
 Application.OnTime Now(), "'UnderUnderMainMacro 465,  25 '" '  --- more usual simplified form. I don't even need the extra  Modul1.  because it is not private
                                                                                                                                                                                                                          Debug.Print vbCr & vbLf & "UverFile module, multiple argument"
' UverFile module, multiple argument
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & "UverFile.xls" & "'"                  &           "!'Modul1.MacroUnderMacroInUverFile 465, 25'": Debug.Print "!'Modul1.MacroUnderMacroInUverFile 465, 25'"
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & "UverFile.xls" & "'"                  &          "!'Modul1.MacroUndermacroInUverFile 465, ""25""'": Debug.Print "!'Modul1.MacroUndermacroInUverFile 465, ""25""'"
                                                                                                                                                                                                                          Debug.Print vbCr & vbLf & "mess about with argument positions"
' mess about with argument positions
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'"               &           "!'Modul1.UnderUnderMainMacro      465   ,     ""25""          '": Debug.Print "!'Modul1.UnderUnderMainMacro      465   ,     ""25""          '"
                                                                                                                                                                                                                          Debug.Print vbCr & vbLf & "This workbook first worksheet code module, single arrgument"
' This workbook first worksheet code module, single arrgument
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'"                &           "!'" & ThisWorkbook.Worksheets.Item(1).CodeName & ".InLisWbFirstWsCodeModule 465'": Debug.Print "!'" & ThisWorkbook.Worksheets.Item(1).CodeName & ".InLisWbFirstWcCodeModule 465'"
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'"                 &            "!'" & ThisWorkbook.Worksheets.Item(1).CodeName & ".InLisWbFirstWsCodeModule ""465""'": Debug.Print "!'" & ThisWorkbook.Worksheets.Item(1).CodeName & ".InLisWbFirstWcCodeModule ""465""'"
                                                                  Debug.Print vbCr & vbLf & "UverFile  first worksheet code module, single arrgument"
' UverFile  first worksheet code module, single arrgument
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & "UverFile.xls" & "'"                     &           "!'" & "Tabelle1" & ".InUverFileFirstWsCodeModule 465'": Debug.Print "!'" & "Tabelle1" & ".InUverFileFirstWsCodeModule 465'"
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & "UverFile.xls" & "'"                     &            "!'" & "Tabelle1" & ".InUverFileFirstWsCodeModule ""465""'": Debug.Print "!'" & "Tabelle1" & ".InUverFileFirstWsCodeModule ""465""'"
                                                                                                                                                                                                                          Debug.Print vbCr & vbLf & "This workbook first worksheet code module, multiple arguments"
' This workbook first worksheet code module, multiple arguments
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'"                  &             "!'" & ThisWorkbook.Worksheets.Item(1).CodeName & ".InLisWbFirstWsCodeModuleMultipleArguments 465      ,  ""25""         '": Debug.Print "!'" & ThisWorkbook.Worksheets.Item(1).CodeName & ".InLisWbFirstWcCodeModuleMultipleArguments 465      ,  ""25""         '"
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'"                  &            "!'" & ThisWorkbook.Worksheets.Item(1).CodeName & ".InLisWbFirstWsCodeModuleMultipleArguments      ""465""   ,   25    '": Debug.Print "!'" & ThisWorkbook.Worksheets.Item(1).CodeName & ".InLisWbFirstWcCodeModuleMultipleArguments      ""465""   ,   25    '"
                                                                                                                                                                                                                          Debug.Print vbCr & vbLf & "UverFile  first worksheet code module, Multiple  arrgument"
' UverFile  first worksheet code module, Multiple  arrgument
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & "UverFile.xls" & "'"                    &           "!'" & "Tabelle1" & ".InUverFileFirstWsCodeModuleMultipleArguments   465   ,    ""25""       '": Debug.Print "!'" & "Tabelle1" & ".InUverFileFirstWsCodeModuleMultipleArguments   465   ,    ""25""       '"
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & "UverFile.xls" & "'"                    &           "!'" & "Tabelle1" & ".InUverFileFirstWsCodeModuleMultipleArguments ""465""   ,    ""25""  '": Debug.Print "!'" & "Tabelle1" & ".InUverFileFirstWsCodeModuleMultipleArguments ""465""   ,    ""25""  '"
                                                                                                                                                                                                                          Debug.Print vbCr & vbLf & "Doubles do not have to be in quotes either  ' This workbook module, double argument arrgument"
' Doubles do not have to be in quotes either  ' This workbook module, double argument arrgument
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'"                  &           "!'Modul1.DoubleCheck 465.5   , ""25.4""    '": Debug.Print "!'Modul1.DoubleCheck 465.5   , ""25.4""    '"
                                                                                                                              
Rem 2 Variables
                                                                                                                                                                                                                          Debug.Print vbCr & vbLf & "Rem 2 Variables" & vbCr & vbLf & "'2a)  ""Pseudo""  variables use"
'2a) "Pseudo" variables use
Dim Arg1_str465 As String, Arg2_Dbl25 As Double
 Let Arg1_str465 = "465.42": Let Arg2_Dbl25 = 25.4
 ' Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'"                 &            "!'Modul1.DoubleCheck  Arg1_str465   ,   Arg2_Dbl25    '": Debug.Print "!'Modul1.DoubleCheck  Arg1_str465   ,   Arg2Db_l25    '"  ' This code line will not work, that is to say it will not find the varables and take  0  values when VBA later runs the Scheduled macro,  Sub DoubleCheck( )
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'"                   &           "!'Modul1.DoubleCheck   """ & Arg1_str465 & """   ,   """ & Arg2_Dbl25 & """    '": Debug.Print "!'Modul1.DoubleCheck  """ & Arg1_str465 & """  ,   """ & Arg2_Dbl25 & """  '"
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'"                   &            "!'Modul1.DoubleCheck   """ & Arg1_str465 & """   ,   " & Arg2_Dbl25 & "    '": Debug.Print "!'Modul1.DoubleCheck  """ & Arg1_str465 & """  ,   " & Arg2_Dbl25 & "  '"
                                                                                                                                                                                                                          Debug.Print vbCr & vbLf & "'2b) Real varable use"
'2b) Real varable use
 Let Modul1.Pbic_Arg1 = "465.42": Let Pbic_Arg2 = 25.4
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'"                   &           "!'Modul1.DoubleCheck   Modul1.Pbic_Arg1     ,   Pbic_Arg2    '": Debug.Print "!'Modul1.DoubleCheck  Modul1.Pbic_Arg1  ,   Pbic_Arg2  '"

 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'"                   &           "!'Modul1.DoubleCheck Modul1.Pbic_Arg1, Pbic_Arg2'"
                                                                                                                                 ''      Debug.Print Pbic_Arg2 '' This gives 999.99 in  Debug F8  mode , 25.4 in  normal  run

Rem 3 ByRef check
 Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'"                   &           "!'Modul1.ByRefCheck'"
 Application.OnTime Now() + TimeValue("00:00:00"), "'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'"          & "!'Modul1.ByRefCheck'"
 Application.OnTime Now() + TimeValue("00:00:01"), "'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'"          & "!'Modul1.ByRefCheck'"
End Sub
Private Sub UnderMainMacro(ByVal Nmbr As Long)
 MsgBox prompt:="Arg1 is   " & Nmbr
End Sub
Sub UnderUnderMainMacro(ByVal Nmbr As Long, ByVal NuverNmbr As Long)
 MsgBox prompt:="Arg1 is  " & Nmbr & ", Arg2 is  " & NuverNmbr
End Sub
Sub DoubleCheck(ByVal DblNmr1 As Double, ByRef DblNmr2 As Double) ' provided the signature line is declared appropriately, all number argument types dont have to be in  ""
 MsgBox prompt:="Arg1 is  " & DblNmr1 & ", Arg2 is  " & DblNmr2
 Let DblNmr2 = 999.99
End Sub


Sub ByRefCheck()
 Debug.Print vbCr & vbLf & vbCr & vbLf & vbCr & vbLf & "Rem 3     ByRef Check" & vbCr & vbLf & Pbic_Arg2
End Sub

**DocAElstein** · 09-16-2015, 06:47 PM

Function Code for getting Column Letter from Column Number
Shortened version used in Post #14
http://www.excelfox.com/forum/showth...=9837#post9837
Public Function CL(ByVal lclm As Long) As String

And Fuller version with explaining 'Comments

Code:

Public Function CL(ByVal lclm As Long) As String '         http://www.excelforum.com/development-testing-forum/1101544-thread-post-appendix-no-reply-needed-please-do-not-delete-thanks-4.html#post4213980
    Do: Let CL = Chr(65 + (((lclm - 1) Mod 26))) & CL: Let lclm = (lclm - (1)) \ 26: Loop While lclm > 0
End Function

Function FukOutChrWithDoWhile(ByVal lclm As Long) As String 'Using chr function and Do while loop      For example http://www.excelforum.com/excel-programming-vba-macros/796472-how-to-go-from-column-number-to-column-letter.html
Dim rest As Long 'Variable for what is "left over" after subtracting as many full 26's as possible
    Do
    '    Let rest = ((lclm - 1) Mod 26) 'Gives 0 to 25 for Column Number "Left over" 1 to 26. Better than ( lclm Mod 26 ) which gives 1 to 25 for clm 1 to 25 then 0 for 26
    '    Let FukOutChrWithDoWhile = Chr(65 + rest) & FukOutChrWithDoWhile 'Convert rest to Chr Number, initially with full number so the "units" (0-25), then number of 26's left over (if the number was so big to give any amount of 26's in it, then number of 26's in the 26's left over (if the number was so big to give any amount of 26 x 26's in it, Enit ?
    '    'OR
    Let FukOutChrWithDoWhile = Chr(65 + (((lclm - 1) Mod 26))) & FukOutChrWithDoWhile
    Let lclm = (lclm - (1)) \ 26 'This gives the number of 26's ( if any ), but just the excact part, in the next number down , - so applying the rest formula to this new number will again leave a difference "left over" rest.
    'lclm = (lclm - (rest + 1)) \ 26 ' As the number is effectively truncated here, any number from 1 to (rest +1)  will do in the formula
    Loop While lclm > 0 'Only loop further if number was big enough to still have 0-25's in it
End Function
Rem Ref    http://www.excelforum.com/development-testing-forum/1101544-thread-post-appendix-no-reply-needed-please-do-not-delete-thanks-4.html#post4213980
Rem Ref    http://www.excelforum.com/tips-and-tutorials/1108643-vba-column-letter-from-column-number-explained.html

**DocAElstein** · 09-16-2015, 09:00 PM

The code to step through while reading Posts from
http://www.excelfox.com/forum/showth...=9517#post9517
from Post #25 http://www.excelfox.com/forum/showth...tenation/page3
http://www.excelfox.com/forum/f2/spe...42/index3.html

Code:

Sub EvalutingQuotes() 'Posts from #25   http://www.excelfox.com/forum/f2/special-concatenation-2042/index3.html

Rem 1) Basics
Dim v As Variant
Let v = "3" '  Results in a Variant variable containing a string value "3"
Let v = 3 '    Results in a Variant variable containing a Long Number 3 ( actually an Integer ? )

Range("I1").Value = Evaluate("=A1") 'Explicit Version
Range("I1").Value = Evaluate("=" & Range("A1").Address & "") 'Explicit Version
Range("I1").Value = Evaluate("" & Range("A1").Address & "") 'Implicit Default

Range("I1").Value = Evaluate("       " & Range("A1").Address & "       ") '

Range("I1").Value = Evaluate(Range("A1").Address) 'Common but dangerous variation

Rem 2) Detailed code anylysis
Dim strEval As String 'String to be used in Evaluate

10  strEval = "=A1" & "&" & "A1": Debug.Print strEval 'gives =A1&A1
Range("I1").Value = Evaluate("" & strEval & "") 'Result Gives 11 in cell I1

20  'strEval = "=A1" & "&"" & ";" & ""&" & "A1": Debug.Print strEval 'gives syntax error
'Range("I1").Value = Evaluate("" & strEval & "") 'errors

30  strEval = "=A1" & "&"";""&" & "A1": Debug.Print strEval 'gives =A1&";"&A1
Range("I1").Value = Evaluate("" & strEval & "") 'Result Gives 1;1

40  strEval = "=A1" & "&"";""": Debug.Print strEval 'gives =A1&";"
Range("I1").Value = Evaluate("" & strEval & "") 'Gives 1;

50  strEval = "=A1" & "&"";" & """": Debug.Print strEval 'gives =A1&";"
Range("I1").Value = Evaluate("" & strEval & "") 'Gives 1;

60  strEval = "=A1" & "&"";""""" & """": Debug.Print strEval 'gives =A1&";" ""
Range("I1").Value = Evaluate("" & strEval & "") 'error
70  strEval = "=A1" & "&"";"";""" & """": Debug.Print strEval 'gives=A1&";";""
Range("I1").Value = Evaluate("" & strEval & "") 'error

80  strEval = "=A1" & "&"";""""" & """": Debug.Print strEval 'gives =A1&";"""
Range("I1").Value = Evaluate("" & strEval & "") 'Did not error  Gives 1;"  !!!!!!!!

90  strEval = "=A1" & "&"";""" & """" & """": Debug.Print strEval 'gives =A1&";"""
Range("I1").Value = Evaluate("" & strEval & "") 'Did not error  Gives 1;"  !!!!!!!!

Rem 3
100 strEval = "=A1" & "&"";""""""": Debug.Print strEval 'gives =A1&";"""
Range("I1").Value = Evaluate("" & strEval & "") 'Did not error  Gives 1;"  !!!!!!!!


End Sub

**DocAElstein** · 05-29-2016, 08:07 PM

Obtaining grid coordinates for an Area of contiguous cells in a Spreadsheet using [ ] and Evaluate(“ “) through the use of a Named Range for that Area

Aka ' It is a Range Name Test : Its n Range Name Test : 's 'n Rng Name Test : s n Rg Name Testie : snRg.Name = "snRgNme"

This code is in support of other Posts in various Threads. ( I will edit the Links as I reference this post )
For example:
http://www.excelforum.com/showthread...t=#post4400666

The code takes in a hard coded Range, A1:E10.
That Range is given a Name as held in the Names Register of a Worksheet.
Various code lines are developed which reference this Named Range and return the Grid Coordinates.

These coordinates are held within the following Long Type Variables
Cs is the start column
sClm is the column count
stpClm is the stop column
Rs is the start row
sRw is the rows count
stpRw is the stop row

Code:

'10   ' It is a Range Name Test : Its n Range Name Test : 's 'n Rng Name Test : s n Rg Name Testie : snRg.Name = "snRgNme"
Sub snRgNameTest()  ' Inspired by..   snb     .. " array [     ] "       '  http://www.excelfox.com/forum/showthread.php/2083-Delete-One-Row-From-A-2D-Variant-Array?p=9714#post9714
20    ' Worksheets Info
30    Dim ws As Worksheet '                                      ' Preparing a "Pointer" to an Initial "Blue Print" ( or a Form, or a Questionnaire not yet filled in, a template   etc.) in Memory of the Object ( Pigeon Hole with a bit of paper or code lines on that can be filled in to refer to a specific Object of this type ) . This also us to get easily at the Methods and Properties through the applying of a period ( .Dot) ( intellisense )
40    'Set ws = ThisWorkbook.Worksheets("NPueyoGyanArraySlicing") 'The worksheets collection object is used to Set ws to the Sheet we are playing with, so that we carefull allways referrence this so as not to go astray through Excel Guessing inplicitly not the one we want...              ' Values are filled at the memory locations and the directions there are specified in the variable "Blue Print "Pointer". In this case a Filled in Blue Print is passed.      http://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it-4.html#post4387191
50    Set ws = ActiveSheet ' Alternative to last line, make code apply to the current active sheet, - That being "looked at" when running this code        '
60    Dim vTemp As Variant ' To help development when you are not sure what type is retuned. "Suck and see what comnes out!"  Highlight it and Hit Shift+F9 to see it in the imediate Window
70    ' Named range referrencing                                                                                                                                      Invoke  Pike  Evaluate Rabbit Rabbit. How's the Bunny ? Bunnytations Banters
80    Dim snRg As Range: Set snRg = ws.Range("A1:E10")
90    Dim sName As String: Let sName = "snRgNme" '
100   Let snRg.Name = "snRgNme"  ' It is a Range Name me  - " 's 'n Range Name me "  ..  "snRgNme"  ;)  This name appears permanentlly in then sheet. It remains referrencing this range unless the name iis deleted or the range referrenced is overwritten by a similar code line which has a different range in it on RHS of =                                                                                                  http://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables
110   Let snRg.Name = sName      ' Identical to last line
120   Dim ReturnedsnRgName As String
130   Let ReturnedsnRgName = snRg.Name ' The returned name is full, like  "NPueyoGyanArraySlicing!$A$1:$E$10". This will not work in the Address Formulas
140   Dim NameOnly As String: Let NameOnly = Replace((snRg.Name), "!", "", (InStr(1, (snRg.Name), "!"))):  Debug.Print snRg.Name: Dim pos&: pos = InStr(1, (snRg.Name), "!"): NameOnly = Replace((snRg.Name), "!", "", pos) ' We had  ----  "NPueyoGyanArraySlicing!$A$1:$E$10"   so here I return a string that starts at the position of the ! and which replaces in that truncated shortened string -  "!$A$1:$E$10"   the "!" with nothing
150   Let NameOnly = Replace((ReturnedsnRgName), "!", "", (InStr(1, (ReturnedsnRgName), "!")))
160      If InStr(NameOnly, "!") > 0 Then MsgBox prompt:="NameOnly is " & vbCr & """" & NameOnly & """" & vbCr & "so will chop off up to and including the ""!""": Let NameOnly = Replace((NameOnly), "!", "", (InStr(1, (NameOnly), "!"))) ' Just to demo that you need to do this if you are not sure that a ! is there, or the code line would error if no ! was in there..
170  '
180   ' Count, Start, and Stop of columns in an Area of contiguous cells in a Spreadsheet
190   Dim sClm As Long 'Variable for ColumnsCount.             -This makes a Pigeon Hole sufficient in construction to house a piece of Paper with code text giving the relevant information for the particular Variable Type. VBA is sent to it when it passes it. In a Routine it may be given a particular “Value”, or (“Values” for Objects).  There instructions say then how to do that and handle(store) that(those). At Dim the created Paper is like a Blue Print that has some empty spaces not yet filled in. Long is very simple to handle, final memory "size" type is known (13.456, 00.001 have same "size" computer memory ),so an Address suggestion can be given for when the variable is filled in. (Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647). If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.-upon/after 32-bit, Integers (Short) need converted internally anyway, so a Long is actually faster)
200   Let sClm = Evaluate("columns(snRgNme)") ' = 5
210   'Let sClm = Evaluate("columns(RetunedsnRgName)") 'Run time Error as expected
220   Let sClm = [columns(snRgNme)]           ' = 5              'Is this Most Powerful Command in VBA?, or what ...    http://www.ozgrid.com/forum/showthread.php?t=52372       http://www.mrexcel.com/forum/excel-questions/899117-visual-basic-applications-range-a1-a5-vs-%5Ba1-a5%5D-benefits-dangers.html
230   'Let sClm = [columns(RetunedsnRgName)]           'Run time Error as expected
240   Let sClm = [columns(A1:E10)]             ' = 5
250                                                               Let vTemp = Evaluate("column(snRgNme)") ' Reveals an Array {1, 2, 3, 4, 5}  -  1 Dimension "pseudo Horizontal" Array
260   Dim Cs As Long 'Variable for Start Column
270   Let Cs = Evaluate("column(A1:E10)")(1)
280   Let Cs = Evaluate("column(snRgNme)")(1) ' = 1
290                                                               Let vTemp = [column(snRgNme)]: vTemp = vTemp(1) ' Anololie erklart:   http://www.excelforum.com/showthread.php?t=1141369&p=4398930&highlight=#post4398930    http://www.excelforum.com/showthread.php?t=1141369&p=4398966#post4398966
300   Let Cs = [column(A1:E10)]()(1)
310   Let Cs = [column(snRgNme)]()(1)
320   '
330   Dim stpClm% ' Variable for Stop column Number               '  ( % is shorthand for As Long ..http://www.excelforum.com/showthread.php?t=1116127&p=4256569#post4256569
340   Let stpClm = Cs + (sClm - 1)             ' = 5
350   ' [ ]
360   Let stpClm = [column(snRgNme)]()(1) + ([columns(snRgNme)] - 1)
370   Let stpClm = [column(snRgNme)]()(1) + ([columns(snRgNme)] - 1)
380   ' In between step [ ] and Evaluate(" ")
390   Let stpClm = [column(snRgNme)]()(UBound([column(snRgNme)]))
400   ' Now Full Evaluate(" ")
410   Let stpClm = Evaluate("column(snRgNme)")(1) + (Evaluate("columns(snRgNme)") - 1)
420   Let stpClm = Evaluate("column(snRgNme)")(UBound(Evaluate("column(snRgNme)")))
430  '
440   ' Start, Count and Stop of rows in an Area of contiguous cells in a Spreadsheet
450   Dim sRw As Long 'Rows Count
460   Let sRw = Evaluate("rows(snRgNme)")
470   Let sRw = [rows(snRgNme)]
480   Let sRw = [rows(A1:E10)]
490                                                               Let vTemp = Evaluate("row(snRgNme)") ' = {1; 2; 3; 4; 5; 6; 7; 8; 9; 10}
500   Dim Rs As Long 'Start Row
510   Let Rs = Evaluate("row(A1:E10)")(1, 1) 'Note a 2 Dimensional,  1 column, "vertical" Array is returned : ' vTemp = {1; 2; 3; 4; 5; 6; 7; 8; 9; 10}
520   Let Rs = Evaluate("row(snRgNme)")(1, 1)
530                                                               Let vTemp = [row(snRgNme)]: vTemp = vTemp(1, 1)
540   Let Rs = [row(A1:E10)]()(1, 1)
550   Let Rs = [row(snRgNme)]()(1, 1)
560  '
570   Dim stpRw% 'Stop Row
580   Let stpRw = Rs + (sRw - 1)
590   Let stpRw = [row(snRgNme)]()(1, 1) + ([rows(snRgNme)] - 1)
600   Let stpRw = [row(snRgNme)]()(1, 1) + ([rows(snRgNme)] - 1)
610  '
620   Let stpRw = [row(snRgNme)]()(UBound([row(snRgNme)], 1), 1) 'UBound([row(snRgNme)], 1) is Ubound first ( "row" ) dimension.  UBound([row(snRgNme)], 2) would be the second dimension ( "column" ) count
630  '
640   Let stpRw = Evaluate("row(snRgNme)")(1, 1) + (Evaluate("rows(snRgNme)") - 1)
650   Let stpRw = Evaluate("row(snRgNme)")(UBound(Evaluate("row(snRgNme)")), 1)
660  '
End Sub

**DocAElstein** · 06-07-2016, 10:31 PM

"Opened up" Rick code:

' To Test Function, Type some arbitrary values in range A1:E10, step through Test Code in F8 Debug Mode in VB Editor, and examine Worksheet, Immediate Window ( Ctrl+G when in VB Editor ), hover over variables in the VB Editor Window with mouse cursor, set watches on variables ( Highlight any occurrence of a variable in the VB Editor and Hit Shift+F9 ) , etc.. and then you should expected the required Output to be pasted out starting Top Left at cell M17

(_... Original Code:
' http://www.excelfox.com/forum/showth...=9658#post9658
....)

Code:

' To Test Function, Type some arbitrary values in range A1:E10, step through Test Code in F8 Debug Mode in VB Editor, and examine Worksheet, Immediate Window ( Ctrl+G when in VB Editor ), hover over variables in the VB Editor Window with mouse cursor, set watches on variables ( Highlight  any occurrence of a variable in the VB Editor and Hit Shift+F9 ) , etc.. and then you should expected the required Output to be pasted out starting Top Left at cell M17
'   http://www.excelfox.com/forum/showthread.php/2083-Delete-One-Row-From-A-2D-Variant-Array?p=9658#post9658
Sub Rick()
Dim sp() As Variant
Dim DataArr() As Variant: Let DataArr() = Range("A1:E10").Value
 Let sp() = Fu_Rick(DataArr(), 5)
 Range("M17").Resize(UBound(sp(), 1), UBound(sp(), 2)).ClearContents
 Let Range("M17").Resize(UBound(sp(), 1), UBound(sp(), 2)) = sp()
End Sub

Required Function_...
Function Fu_Rick(ByRef arrIn() As Variant, ByVal RowToDelete As Long) As Variant
_... in next Post

**DocAElstein** · 06-07-2016, 10:32 PM

Function Required for last Post:

Code:

Function Fu_Rick(ByRef arrIn() As Variant, ByVal RowToDelete As Long) As Variant
10  ' use "neat magic" code line    arrOut() = Application.Index(arrIn(), rwsT(), clms())
20  ' So we have directly the Input Array, arrIn(). For clms(), do some extra stuff to get a column letter ( usiing the Split Address Method ) then column indices diectly from Spreadsheet column() Function. Rows from joinig the Row indicies above and below the row to be deleted
30                                          Dim Cols As String: Cols = "A:" & Split(Columns(UBound(arrIn(), 2) - LBound(arrIn(), 2) + 1).Address(, 0), ":")(0)
40  '                                       Fu_Rick = Application.Index(arrIn(), Application.Transpose(Split(Join(Application.Transpose(Evaluate("Row(1:" & (RowToDelete - 1) & ")"))) & " " & Join(Application.Transpose(Evaluate("Row(" & (RowToDelete + 1) & ":" & UBound(arrIn()) & ")"))))), Evaluate("COLUMN(" & Cols & ")"))
50
60  '   clms() = { 1, 2, 3, 4, 5 }
61  'clms()   Rick     Evaluate("COLUMN(" & "A:" & Split(Columns(UBound(arrIn(), 2) - LBound(arrIn(), 2) + 1).Address(, 0), ":")(0) & ")")
70   '  Start point is last column in Output Array using..   Split Address technique     http://www.excelforum.com/development-testing-forum/1101544-thread-post-appendix-no-reply-needed-please-do-not-delete-thanks-4.html#post4213969
80   Dim larrClm As Long: Let larrClm = ((UBound(arrIn(), 2) - LBound(arrIn(), 2)) + 1) ' For our Output Array  ( base 1 ) staring at 1 - not yet pinned to a Top left Output Range cell the ( ( stop "column"  - start "column" ) + 1 ) gives "last" "column"
90   Dim AdrsRel As String: Let AdrsRel = Columns(larrClm).Address(ColumnAbsolute:=False) 'False absolute Address gives no $ prefix and format like "E:E" (true Relative Address) , so split by ":" and then either (0) or (1) returned arrAddressSplit() Element will do for the letter..
100  Dim arrAddressSplit() As String
110  Let arrAddressSplit() = VBA.Split(AdrsRel, ":", 2, vbTextCompare) 'Splits  into like ("E", "E") for no or -1 second argument..  Here 2 gives just the 2 you would get E, and E - ...   http://www.mrexcel.com/forum/general-excel-discussion-other-questions/929381-visual-basic-applications-split-function-third-argument-refers-maximum-outputs-%93when-splitting-stops-%94.html
120  Dim clmLtr As String
130  Let clmLtr = arrAddressSplit(0) 'Returns first element "along" in 1 Dimensional "Psuedo Horizontal" Array ( Elements for 1 Dimensional Array are by default 0,1, 2, 3 ....etc )
140  ' Now use spreadsheet column function , column(A:E"), to get a {1, 2, 3, 4, 5} Array
150  Dim clms() As Variant: Let clms() = Evaluate("column(A:" & clmLtr & ")")
 
160  'rwsT()       Rick       Application.Transpose(Split(Join(Application.Transpose(Evaluate("Row(1:" & (RowToDelete - 1) & ")"))) & " " & Join(Application.Transpose(Evaluate("Row(" & (RowToDelete + 1) & ":" & UBound(arrIn()) & ")")))))
170  'Final required row Indicies, with a missing indicie, as 2 strings ( Hard Copy )
180  Dim strRwsDBelow As String, strRwsDAbove As String, strrwsD As String
190  Let strRwsDBelow = "1 2 3 4": Let strRwsDAbove = "6 7 8 9 10"
200  Let strrwsD = "1 2 3 4" & " " & "6 7 8 9 10"
210  Let strrwsD = strRwsDBelow & " " & strRwsDAbove
220
230
240  'Get row indicies conveniently from Row Function - ( correct "orintation" to use in "neat magic" code line, but wrong "orientation" to use Join Function {1; 2; 3; 4}   and   {6; 7; 8; 9; 10}  )
250  Dim arr_2D1rowBelow() As Variant, arr_2D1rowAbove() As Variant
260  Let arr_2D1rowBelow() = Evaluate("Row(1:" & (RowToDelete - 1) & ")") ' 1 To 4, 1 To 1 {1; 2; 3; 4} Array
270  Let arr_2D1rowAbove() = Evaluate("Row(" & (RowToDelete + 1) & ":" & UBound(arrIn()) & ")") ' 1 To 5, 1 To 1 {6; 7; 8; 9; 10} Array
280  'Get sequential below and above  row strings....   transpose back again! so Join will work, dear oh dear.....
290  Let strRwsDBelow = Join(Evaluate("transpose(Row(1:" & (RowToDelete - 1) & "))"), " ") 'Join must have eindimensional Array, as given by transpose working on a 2D 1 column Array
300  Let strRwsDBelow = Join(Application.Transpose((Evaluate("Row(1:" & (RowToDelete - 1) & ")"))), " ") '   "1 2 3 4"
310  Let strRwsDBelow = Join(Application.Transpose((arr_2D1rowBelow())), " ") '   "1 2 3 4"
320  Let strRwsDAbove = Join(Application.Transpose((arr_2D1rowAbove())), " ") '   "6 7 8 9 10"
  
330 'Final required row Indicies, with a missing indicie, as a string
340  Let strrwsD = strRwsDBelow & " " & strRwsDAbove
350
360 'Split Final String by " " to get 1 1D "Pseudo Horizontal" Array
370 Dim rws() As String: Let rws() = VBA.Split(strrwsD, " ") ' 1 D Array
380 'final Transposed Array for "magic neat" code line
390 Dim rwsT() As Variant: Let rwsT() = Application.Transpose(rws()) ' 2 D 1 "column" Array
400
440 'Output Array
450 Dim arrOut() As Variant
460 Let arrOut() = Application.Index(arrIn(), rwsT(), clms())
470
480 Let Fu_Rick = arrOut()
490 'Or
 Fu_Rick = Application.Index(arrIn(), Application.Transpose(Split(Join(Application.Transpose(Evaluate("Row(1:" & (RowToDelete - 1) & ")"))) & " " & Join(Application.Transpose(Evaluate("Row(" & (RowToDelete + 1) & ":" & UBound(arrIn()) & ")"))))), Evaluate("COLUMN(" & "A:" & Split(Columns(UBound(arrIn(), 2) - LBound(arrIn(), 2) + 1).Address(, 0), ":")(0) & ")"))
End Function

**DocAElstein** · 06-07-2016, 11:52 PM

' To Test Function, Type some arbitrary values in range A1:E10, step through code in F8 Debug Mode in VB Editor, and examine Worksheet, Immediate Window ( Ctrl+G when in VB Editor ), hover over variables in the VB Editor Window with mouse cursor, set watches on variables ( Highlight any occurrence of a variable in the VB Editor and Hit Shift+F9 ) , etc.. and then you should expected the required Output to be pasted out starting Top Left at cell M17

Main Test Code ( Required Function given a couple of Posts down )

Code:

' Delete One Row From A 2D Excel Range Area
' To Test Function, Type some arbitrary values in range A1:E10, step through code in F8 Debug Mode in VB Editor, and examine Worksheet, Immediate Window ( Ctrl+G when in VB Editor ), hover over variables in the VB Editor Window with mouse cursor, set watches on variables ( Highlight  any occurrence of a variable in the VB Editor and Hit Shift+F9 ) , etc.. and then you should expected the required Output to be pasted out starting Top Left at cell M17

Sub Alan()
Dim sp() As Variant
    'Dim DataArr() As Variant: Let DataArr() = Range("A1:E10").Value
 Let sp() = FuR_Alan(Range("A1:E10"), 5)
 'Let sp() = FuRSHg(Range("A1:E10"), 5)
 'Let sp() = FuRSHgDotT(Range("A1:E10"), 5)
 'Let sp() = FuRSHgShtHd(Range("A1:E10"), 5)
 Range("M17").Resize(UBound(sp(), 1), UBound(sp(), 2)).ClearContents
 Let Range("M17").Resize(UBound(sp(), 1), UBound(sp(), 2)) = sp()
End Sub

_............

For no particular reason I am considering this as my Input "Area"

Using Excel 2007 32 bit

_Row\^Col	A	B	C	D	E
1	0	10	20	30	40
2	2	12	22	32	42
3	4	14	24	34	44
4	6	16	26	36	46
5	8	18	28	38	48
6	10	20	30	40	50
7	12	22	32	42	52
8	14	24	34	44	54
9	16	26	36	46	56
10	18	28	38	48	58
11

Sheet: NPueyoGyanArraySlicing

_.......

Expected Output shown in next Post

**DocAElstein** · 06-26-2016, 10:27 PM

My codes
Main Code
Sub DoStuffInFoldersInFolderRecursion()
And called Routine
Sub LoopThroughEachFolderAndItsFile(

Code:

Option Explicit
'
'http://excelpoweruser.blogspot.de/2012/04/looping-through-folders-and-files-in.html     http://www.excelforum.com/excel-programming-vba-macros/1126751-get-value-function-loop-through-all-files-in-folder-and-its-subfolders.html#post4316662
Sub DoStuffInFoldersInFolderRecursion() 'Main Procedure to call the Function  LoopThroughEachFolder(objFolder)
Rem 1A) Some Worksheets and General Variables Info
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets.Item(1) 'Worksheets("RudyMSRQueue") 'CHANGE TO SUIT YOUR WORKSHEET
Dim strDefPath As String: Let strDefPath = ThisWorkbook.Path ' Any Path to Folder to test this code! here we simply use the Path where the File with this code in is
Dim strDefFldr As String: Let strDefFldr = "EileensFldr" 'Just for an initial suggestion
Rem 2A) Get Folder Info ( Using Library Shell32 ( C:\WINDOWS\system32\SHELL32.dll ) Microsoft Shell Controls And Automation )
Dim ShellApp  As Shell32.Shell ' The next two lines are the equivalent "Early Binding pair"
Set ShellApp = New Shell32.Shell ''You will need to do select form VB Editor options .. Extras...then scroll down to  Microsoft Shell Controls And Automation  ...  and add a check
Dim objWB As Object, strWB As String 'The  .BrowseForFolder Method appears either return a string of the Folder name you choose, or an object which is that chosen Folder, depending on how you declare the variable to put the retuned "thing" in
Set objWB = ShellApp.BrowseForFolder(0, "Please choose a folder", 0, "" & strDefPath & "\" & strDefFldr & "") 'An Object of Folder type returned
Let strWB = CStr(objWB) ' ! Cstr seems not to be necerssary

Rem 3A )
'Dim FSO As Object: Set FSO = CreateObject("Scripting.FileSystemObject")'Late Binding
Dim FSO As Scripting.FileSystemObject 'Early Binding alternative  activate a reference to the Microsoft Scripting Runtime Library ( MSRL ) in the Tools References menu of VB Editor Options.
Set FSO = New Scripting.FileSystemObject 'Create an Instance of the Class Scripting FileSystemObject
Dim myFolder As Folder 'An Object from myFolder, can be an declared as Object also
Set myFolder = FSO.GetFolder(strWB) 'Set the selected Folder to the Object Folder using this Method which takes as arbument the Full String Path

Rem 4A )
Dim rCnt As Long, CopyNumber As Long: Let rCnt = 1: Let CopyNumber = 1 '"Run progressin ( "down vertical" ) axis ( Row count for output ), "Down Folder chain to the right", The Count of the Copy of the called Procedue
Dim celTL As Range: Set celTL = ws.Range("A1") 'Top left of where Licting should go
celTL.Value = myFolder.Path: celTL.Offset(0, 1).Value = myFolder.Name: ws.Columns("A:C").AutoFit 'First output Row
Call LoopThroughEachFolderAndItsFile(myFolder, celTL, rCnt, CopyNumber) 'Up until now we just got the initial Folder. Now we go to all sub folders  then all subfolders   then all subfolders.......
Application.ScreenUpdating = True
MsgBox "All Excel Files processed", vbInformation
ws.Columns("A:H").AutoFit
End Sub
Rem 5A)
Sub LoopThroughEachFolderAndItsFile(ByVal fldFldr As Folder, ByRef celTL As Range, ByRef rCnt As Long, ByVal CopyNumberFroNxtLvl As Long)  'In below function we have a nested loop to iterate each files also
Dim myFldrs As Folder ''This is used continuously as the "steering" thing, that is to say each Sub Folder in Folder loops, in loops, in loops......etc
Dim CopyNumber As Long 'equivalent to clmLvl in Rudis Q code
If CopyNumber = 0 Then CopyNumber = CopyNumberFroNxtLvl 'If this variable in this Copy of the Routine has not been set then we have reached the next Copy for the First time, so set the variable so we have an indication ( number to the right or "down" Folder Chain
    '5Ab) Doing stuff for current Folder
    For Each myFldrs In fldFldr.SubFolders 'SubFolders collection used to get at all Sub Folders
    ''''''''Doing stuff for each Folder
    Let rCnt = rCnt + 1 + 1 ''At each folder we always move down a line, and a dd amm extra line  as a space between Folders ( The indication of the "column" or "down" to the right comes from the Copy Number of the Sub Procedure
    celTL.Cells(rCnt, 1).Value = myFldrs.Path: celTL.Cells(rCnt, CopyNumber).Offset(0, 2).Value = myFldrs.Name:   'Print out current Folder Path and Name in next free row.
    ''''''''End doing stuff for each Folder
    '5Ac) Doing stuff for current file.
    Dim oFile As File
            For Each oFile In myFldrs.Files 'Looking at all Files types initially '#####
            ''''''''Doing Stuff for Each File
    '            Dim Extension As String: Let Extension = Right(oFile.Name, (Len(oFile.Name) - (InStrRev(oFile.Name, ".")))) 'To get the bit just after the . dot.
    '                If Left(Extension, 3) = "xls" Then 'Check for your required File Type    #####
                Let rCnt = rCnt + 1
                celTL.Cells(rCnt, CopyNumber).Offset(0, 2).Value = oFile.Name ' Do your stuff here
    '                Dim wkb As Workbook
                     On Error GoTo ErrHdlr 'In case problem opening file for example
    '                Set wkb = Workbooks.Open(oFile)
    '                wkb.Close SaveChanges:=True
    '                Else 'Do not do stuff for a Bad Extension
    '                End If
            ''''''''End Doing Sttuff for Each File
NxtoFile:   Next oFile ' Spring Point after error handler so as to go on to next File after the File action that errored
    Call LoopThroughEachFolderAndItsFile(myFldrs, celTL, rCnt, CopyNumber + 1) 'This is an example of recursion. It is actually very simple once you understand it. But it is just incredibly difficult to put in words. It is basically a Procedure that keeps calling itself as much as necessary as it goes "along",  "down", or "to the right" of the Path "roots". Every time it goes off calling itself VBA runs a copy of that Procedure. It "Stacks" all info carefully for each "Copy" Run and continues to do this "drilling" down as far as it must, in this case finding the Next Folder, and then the next Folder in that, then the next Folder in that, then the next Folder in that...I think you get the point! Each time VBA makes a copy of the Routine and you go into that. The calling Routine then "freezes at its current state and all variable keep there values. The "Frozen" Routine then re starts when the copy finishes
    Next
Exit Sub 'Normal End for no Errors
Rem 6 ) Error handler section just put here for convenience
ErrHdlr: 'Hopefully we know why we are here, and after informing can continue ( to next file )
MsgBox prompt:="Error " & Err.Description & " with File " & oFile & ""
On Error GoTo -1 'This needs to be done to reset the VBA exceptional error state of being. Otherwise VBA "thinks" Errors are being handeled and will not respond again to the Error handler.
On Error GoTo 0 ' Swiches off the current error handler. I do not really need to do this. But it is good practice so the error handler is only in place at the point where i next am expecting an error
GoTo NxtoFile
End Sub

'
'
''   http://www.excelforum.com/excel-programming-vba-macros/1126751-get-value-function-loop-through-all-files-in-folder-and-its-subfolders.html

**DocAElstein** · 06-07-2016, 11:53 PM

In support of these Forum Threads

https://www.excelforum.com/developme...ml#post4440512
https://excel.tips.net/T008233_Findi...e_Desktop.html
http://www.excelfox.com/forum/showth...ll=1#post10420
http://www.excelfox.com/forum/showth...ll=1#post10421

Original Post was the eigth Post after this one_.....
http://www.eileenslounge.com/viewtop...185272#p175068
_...... but it is no longer there currently

_.. Original Post: ( Post Split over several Posts in this Thread ( It was one Post originally )
-::::::................
)

Hi,
I found the code here to loop through all folders and sub Folders very interesting and revealing.
I am only a novice but have answered more Threads then I can remember doing this and almost always the method I and others use is the same one. The code here is different..

So I am mainly just adding another solution as well as discussing and comparing with the solution given here by Rudi.

I am mainly looking at applying the codes to get to the point of being able to "do stuff" with each File. For the OP "doing stuff" was to Find and Replace Values in all excel files of a main Folder, including all Files in Sub Folders. But the actual Doing stuff is rarely the difficult bit. The main work is to "get at " all Files.

What really caught my eye with this Thread is that it does this without using the, as many people find a big mystery, the process of recursion.

I thought it would be very useful to write a code where the "doing stuff " was to print out to a Spreadsheet a Full Listing of all The Folders, Sub Folders., and all files within.

So for any required " doing stuff " code you could first run the code, which asks you to select your main Folder. The Print out lets you see and check that you are "getting" at each File you want. The code can then be modified by simply replacing at those lines which print out the information, the code lines necessary to do stuff to each file and / or Folder.

_..........................................
I will present two codes, a version of Rudi's and mine which is based on the classical Recursion Way.

Here then some quick notes on the codes.
Rem 1) Just some Worksheet info. Currently the Code accesses the first tab from the left ( Usually your "Sheet1" ). Identical for both codes.
Rem 2) Identical for Both Codes. Calls up a dialogue box in which you may enter the Start Folder in which all Folders and Sub Folders of interest are in. ( There are at least 3 ways in VBA I know to get that, I just chose one of them:
http://www.mrexcel.com/forum/general...plication.html
( The one using an Object that has a lot to do with Window things ).
http://www.mrexcel.com/forum/general...plication.html
I did the above just to practice something new to me.
The Application.FileDialog(msoFileDialogFolderPicker)
Originally dine by Rudis Way is probably better. That is just a VBA Property that pulls up a dialogue box, in this case the one that lets you pick a Folder.
_................................................. ............
Rem 3) Similar for both codes.
Sets up and allows us to use the Microsoft Scripting Runtime Library, which allows us to do lots with Files and related things.

Rem 4) Positional Info variable declaration.
Variables for Positons of where I print the Folder and File Info in the Worksheet.
For that the Range Object of the Top Left of where a "Explorer" Listing should go has the Cells Property applied using the Co ordinates I determine to give the Position in the Worksheet in which to paste out the Folder or File name
For Rudis Code the Queue thing is also declared
For Rudis Code I have variables for the count of Folders in the next Folder level, and for the count of the current Folder level being looped through This is the actual stand at the time within the Queue.

Rem 5 ) Here the codes differ.
Explanations are given extensively in the code 'Comments, best viewed in the VB Code Window whilst stepping through the code in debug ( F8 ) ( Whilst if possible also looking at the Spreadsheet at the same time )
Briefely in Words.

My code. The Classic recursion Type.

The Routine at Rem 5A) is called initially from the
Sub DoStuffInFoldersInFolderRecursion()
Code. It passes to it this first time the Main Folder.
This called subroutine starts going into the next level "down" or "to the right" of Sub Folders in the given Original Main Folder.
It does stuff for Each Sub Folder and files, if any , therein. ( Using a For Next Loop typically )
After that it calls itself !!!! It takes into it the current Sub Folder.
At this point most people get confused. I think, as I am thick, and can understand the following explanation , then it may be easy for most people to understand

The thing that is often missing at this point is knowing what VBA actually does when this happens. Quite simply it makes a NEW Copy of the Routine, completely independent of the Original Routine calling it. And it starts running that. The original Routine is "frozen" by VBA. And VBA stores somewhere( typically referred to as in a "Stack Row" or "Stack" ) all the variable values used in the Calling Routine and "freezes" them at their current values as well.

In the New copy all variable are new and independent of those in the Original. Unfortunately you never think you see this new Copy, but you do. If you step through such a recursion code, when it "looks" like it springs back to the original when the Code calls itself, you are actually seeing at that point the Copy.

So you see, if you have a couple of Folders, and the first has a Sub Folder in it then the following happens:
You do stuff in the First Folder. Then that Routine "freezes" as it calls itself. The current Sub Folder is taken into the "Copy" Routine. The "Copy" Routine then Does the same for the all Sub Folders now in the current Sub Folder. Important is that the Code line which calls itself is within the main For Next. So if there are no more Sub Folders, the Copy Routine will end. This occurs in our example here after the one Sub Folder. Effectively the "Copy" Routine then "dies" The original Routine then Unfreezes. So the next of our two Folders is gone through.

The only difficulty I had in writing the particular Recursion code is that I wanted to show each Folder "level" down the Folder Chain at each column going "down" or "to the right" as typically seen in a classic Explorer Window. The problem is how do I know which "Copy" Routine I am in. Every successive Copy will relate to a run in a the next "down" or "to the right. I cannot simply add a progressively increasing count, as in the recursion Code I will be going "back and forth" depending on if and how many Sub folders there are. I need a way to know at which "level" of Sub Folders I am in when in any progression back and forth.
I do that as follows. I mention it here as it does demonstrate clearly again how recursion works.
Inside the Routine towards the start is a variable,
CopyNumber.
This will be a unique variable for each "Copy" Routine. Every time the Routine is called a number is taken in at the value inside a variable in the signature line
CopyNumberFroNxtLvl
For the very first call it is set to 1 in a variable whose value is taken in at its value, as a value, in the Signature line
Within the Routine this value is given to
CopyNumber.
Only
If CopyNumber = 0
That is to say if
CopyNumber
Had never been given a number
When the function calls itself to takes in by value at the value iif using the value of
CopyNumber + 1
( Call LoopThroughEachFolderAndItsFile(myFldrs, celTL, rCnt, CopyNumber + 1) )

This has the effect that when you go to the "next down" or "next level to the right" only the first time will
CopyNumber
Be assigned, and its value will take an incremented number giving an indication of you "level"

This value is frozen when you go "further down" in the next "Copy Routine" . But When you come back up, it "thaws out" and is used within so that my line which prints out information will be in the correct "column" which is an indication in my final Output of the "level" of my folder ( and possibly Files within )

(celTL.Cells(rCnt, 1).Value = myFldrs.Path: celTL.Cells(rCnt, CopyNumber).Offset(0, 2).Value = myFldrs.Name )

( celTL.Cells(rCnt, CopyNumber).Offset(0, 2).Value = oFile.Name )