Notes tests. ByVal ByRef Application.Run.OnTime Multiple Variable Arguments ByRef ByVal

[DocAElstein]

Re: Appendix Thread. ( Codes for other Threads, HTML Tables, etc. )<o:p></o:p>
<o:p> </o:p>
Hi<o:p></o:p>
. I would like to use this Thread as an Appendix for codes in other Threads so as to help reduce clutter in that Thread should the code be a bit long, or not directly relevant.<o:p></o:p>
. Also as HTML code is on in this Test Sub Forum I would like to reference HTML Tables should I wish to use them in answering threads<o:p></o:p>
<o:p> </o:p>
@ Moderators, Administrator:<o:p></o:p>
. I hope the above is OK to do and if so please do not delete this Thread. ( Or advise if I should post my "Appendix" somewhere else ( If possible where HTML code is on ) )<o:p></o:p>
.<o:p></o:p>
. Many Thanks<o:p></o:p>
Alan<o:p></o:p>



This Post http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)
http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)
2345

Edit
2404 Jan 2020 Post 11860

[DocAElstein]

Coding for these Threads
https://stackoverflow.com/questions/31439866/multiple-variable-arguments-to-application-ontime
http://www.excelfox.com/forum/showthread.php/2404-Notes-tests-Application-Run-OnTime-Multiple-Variable-Arguments-ByRef-ByVal?p=11870&viewfull=1#post11870
https://stackoverflow.com/questions/31439866/multiple-variable-arguments-to-application-ontime/59812342#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)



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]

Function Code for getting Column Letter from Column Number
Shortened version used in Post #14
http://www.excelfox.com/forum/showthread.php/2083-Delete-One-Row-From-A-2D-Variant-Array?p=9837#post9837
Public Function CL(ByVal lclm As Long) As String

And Fuller version with explaining 'Comments



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]

d,adhadkjAD

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
http://www.eileenslounge.com/viewtopic.php?p=324457#p324457 (http://www.eileenslounge.com/viewtopic.php?p=324457#p324457)
http://www.eileenslounge.com/viewtopic.php?p=324064#p324064 (http://www.eileenslounge.com/viewtopic.php?p=324064#p324064)
http://www.eileenslounge.com/viewtopic.php?p=323960#p323960 (http://www.eileenslounge.com/viewtopic.php?p=323960#p323960)
https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg (https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg)
https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg.ADd4m2zp_xDADd6Nnotj 1C (https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg.ADd4m2zp_xDADd6Nnotj 1C)
s://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgySdtXqcaA27wQLd1t4AaABAg (s://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgySdtXqcaA27wQLd1t4AaABAg)
http://www.eileenslounge.com/viewtopic.php?p=323959#p323959 (http://www.eileenslounge.com/viewtopic.php?p=323959#p323959)
http://www.eileenslounge.com/viewtopic.php?f=30&t=41784 (http://www.eileenslounge.com/viewtopic.php?f=30&t=41784)
http://www.eileenslounge.com/viewtopic.php?p=323966#p323966 (http://www.eileenslounge.com/viewtopic.php?p=323966#p323966)
http://www.eileenslounge.com/viewtopic.php?p=323959#p323959 (http://www.eileenslounge.com/viewtopic.php?p=323959#p323959)
http://www.eileenslounge.com/viewtopic.php?p=323960#p323960 (http://www.eileenslounge.com/viewtopic.php?p=323960#p323960)
http://www.eileenslounge.com/viewtopic.php?p=323894#p323894 (http://www.eileenslounge.com/viewtopic.php?p=323894#p323894)
http://www.eileenslounge.com/viewtopic.php?p=323843#p323843 (http://www.eileenslounge.com/viewtopic.php?p=323843#p323843)
http://www.eileenslounge.com/viewtopic.php?p=323547#p323547 (http://www.eileenslounge.com/viewtopic.php?p=323547#p323547)
http://www.eileenslounge.com/viewtopic.php?p=323516#p323516 (http://www.eileenslounge.com/viewtopic.php?p=323516#p323516)
http://www.eileenslounge.com/viewtopic.php?p=323517#p323517 (http://www.eileenslounge.com/viewtopic.php?p=323517#p323517)
http://www.eileenslounge.com/viewtopic.php?p=323449#p323449 (http://www.eileenslounge.com/viewtopic.php?p=323449#p323449)
http://www.eileenslounge.com/viewtopic.php?p=323226#p323226 (http://www.eileenslounge.com/viewtopic.php?p=323226#p323226)
http://www.eileenslounge.com/viewtopic.php?f=25&t=41702&p=323150#p323150 (http://www.eileenslounge.com/viewtopic.php?f=25&t=41702&p=323150#p323150)
http://www.eileenslounge.com/viewtopic.php?p=323085#p323085 (http://www.eileenslounge.com/viewtopic.php?p=323085#p323085)
http://www.eileenslounge.com/viewtopic.php?p=322955#p322955 (http://www.eileenslounge.com/viewtopic.php?p=322955#p322955)
http://www.eileenslounge.com/viewtopic.php?f=30&t=41659 (http://www.eileenslounge.com/viewtopic.php?f=30&t=41659)
http://www.eileenslounge.com/viewtopic.php?p=322462#p322462 (http://www.eileenslounge.com/viewtopic.php?p=322462#p322462)
http://www.eileenslounge.com/viewtopic.php?p=322356#p322356 (http://www.eileenslounge.com/viewtopic.php?p=322356#p322356)
http://www.eileenslounge.com/viewtopic.php?p=321984#p321984 (http://www.eileenslounge.com/viewtopic.php?p=321984#p321984)
https://eileenslounge.com/viewtopic.php?f=30&t=41610 (https://eileenslounge.com/viewtopic.php?f=30&t=41610)
https://eileenslounge.com/viewtopic.php?p=322176#p322176 (https://eileenslounge.com/viewtopic.php?p=322176#p322176)
https://eileenslounge.com/viewtopic.php?p=322238#p322238 (https://eileenslounge.com/viewtopic.php?p=322238#p322238)
https://eileenslounge.com/viewtopic.php?p=322270#p322270 (https://eileenslounge.com/viewtopic.php?p=322270#p322270)
https://eileenslounge.com/viewtopic.php?p=322300#p322300 (https://eileenslounge.com/viewtopic.php?p=322300#p322300)
http://www.eileenslounge.com/viewtopic.php?p=322150#p322150 (http://www.eileenslounge.com/viewtopic.php?p=322150#p322150)
http://www.eileenslounge.com/viewtopic.php?p=322111#p322111 (http://www.eileenslounge.com/viewtopic.php?p=322111#p322111)
http://www.eileenslounge.com/viewtopic.php?p=322086#p322086 (http://www.eileenslounge.com/viewtopic.php?p=322086#p322086)
https://stackoverflow.com/questions/33868233/shell-namespace-not-accepting-string-variable-but-accepting-string-itself/77888851#77888851 (https://stackoverflow.com/questions/33868233/shell-namespace-not-accepting-string-variable-but-accepting-string-itself/77888851#77888851)
http://www.eileenslounge.com/viewtopic.php?p=322084#p322084 (http://www.eileenslounge.com/viewtopic.php?p=322084#p322084)
http://www.eileenslounge.com/viewtopic.php?p=321822#p321822 (http://www.eileenslounge.com/viewtopic.php?p=321822#p321822)
http://www.eileenslounge.com/viewtopic.php?p=322424#p322424 (http://www.eileenslounge.com/viewtopic.php?p=322424#p322424)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[DocAElstein]

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.php?t=1141369&p=4400666&highlight=#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



'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]

"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/showthread.php/2083-Delete-One-Row-From-A-2D-Variant-Array?p=9658#post9658
....)



' 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]

Function Required for last Post:



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.Trans pose(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.Trans pose(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.Trans pose(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]

"Opened up" snb Code

(_.. Original code here
http://www.excelfox.com/forum/showthread.php/2083-Delete-One-Row-From-A-2D-Variant-Array?p=9714#post9714
_........)

' 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






' Delete One Row From a ... group of contiguous cells in a Spreadsheet

' 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
' http://www.excelfox.com/forum/showthread.php/2083-Delete-One-Row-From-A-2D-Variant-Array?p=9714#post9714
Sub snb_()
Dim sp() As Variant
Let sp() = Fu_snb(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

Required Function_...
Function Fu_snb(ByVal sn As Range, ByVal y As Long) As Variant
_...in next Post

[DocAElstein]

Required Function for last Post


Function Fu_snb(ByVal sn As Range, ByVal y As Long) As Variant
10 ' use "neat magic" code line arrOut() = Application.Index(arrIn(), rwsT(), clms()) http://www.excelforum.com/excel-new-users-basics/1099995-application-index-with-look-up-rows-and-columns-arguments-as-vba-arrays.html http://www.mrexcel.com/forum/excel-questions/908760-visual-basic-applications-copy-2-dimensional-array-into-1-dimensional-single-column-2.html#post4375354
20 ' So we have sn as a range sn, ( can be uses syntaxly for arrIn() in "neat magic" line. ). Consequtive columns indicies as simple transpose of consequtive row Indicies from Spreadsheet row Funnction. Row indicies as the consequtive row indicies with the row to be deleted taken out
30 ' so snb does arrOut() = Application.Index(sn, rwsT(), clms())
40
50
60 ' clms() = { 1, 2, 3, 4, 5 }
70 'clms()
80 Dim clms() As Variant: Let clms() = Evaluate("column(A1:E10)")
90 Let clms() = Evaluate("column(" & sn.Address & ")")
100 Dim sName As String: Let sName = "snb_002"
110 Let sn.Name = sName
120 Let clms() = Evaluate("column(" & sName & ")")
129 Let clms() = Evaluate("column(snb_002)")
130 '== DANGER: === Pitful: Above we gave the Range Object a Name, but now see what "Name" or "Name" 's comes back "!" !
132 Dim retRefstrName As String, retObjName As Object
133 Let retRefstrName = sn.Name: Set retObjName = sn.Name: Debug.Print sn.Name 'something of the form "NPueyoGyanArraySlicing!$A$1:$E$10" is reveald in Immediate ( Ctrl+G when in VB Editor ) Window
134 'Let clms() = Evaluate("column(=NPueyoGyanArraySlicing!$A$1:$E$10)") 'Let clms() = Evaluate("column(" & retRefstrName & ")")' Rintime Error 13: Incompatiblee types
135 Let clms() = Evaluate("column(NPueyoGyanArraySlicing!$A$1:$E$10)") 'Works
137 Dim NameOnly As String: Let NameOnly = Replace((sn.Name), "!", "", (InStr(1, (sn.Name), "!"))): 'Debug.Print sn.Name: Dim pos&: pos = InStr(1, (sn.Name), "!"): NameOnly = Replace((sn.Name), "!", "", pos) ' We had ---- "NPueyoGyanArraySlicing!$A$1:$E$10" This is a String referrece returned when the Name Object is used directly or set to a String Variable. 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
138 Let clms() = Evaluate("column(" & NameOnly & ")"): Let clms() = Evaluate("column(" & Replace((sn.Name), "!", "", (InStr(1, (sn.Name), "!"))) & ")")
139
140 Dim strName As String: Let strName = sn.Name.Name: Debug.Print strName: Let strName = retObjName.Name: Debug.Print strName ' returns our original "CoN"
142 Let clms() = Evaluate("column(" & strName & ")")
150 Dim rngF1G2 As Range: Set rngF1G2 = Range("F1:G2"): Let Range("F1:G2").Value = "From Line 150"
151 Let Range("=NPueyoGyanArraySlicing!F1:G2").Value = "From Line 151"
152 Let rngF1G2.Name = "snFG": Let Range("snFG").Value = "From Line 152"
149 '===============
160 'rwsT() snb rws() = VBA.Split(Trim(Replace(" " & Join(Evaluate("transpose(row(A1:E10))")) & " ", " " & y & " ", " ")))
170 'Final required row Indicies, with a missing indicie, as a string ( Hard Copy )
180 Dim strrwsD As String
190 Let strrwsD = "1 2 3 4 6 7 8 9 10"
200 Let strrwsD = Replace("1 2 3 4 5 6 7 8 9 10", " 5 ", " ", 1)
210 Dim strRws As String: Let strRws = "1 2 3 4 5 6 7 8 9 10"
220 Let strrwsD = Replace(strRws, " 5 ", " ", 1)
230
240 'Get full sequential row conveniently from Row Function - ( correct "orientation" to use in "neat magic" code line, but wrong "orientation" to use Join Function {1; 2; 3; 4; 5; 6; 7; 8; 9; 10} )
250 Dim arr_2D1row() As Variant
260 Let arr_2D1row() = Evaluate("row(A1:E10)") ' 1 To 10, 1 To 1
270
280 'Get full sequential row string.
290 Let strRws = Join(Evaluate("transpose(row(A1:E10))"), " ") 'Join must have eindimensional Array, as given by transpose working on a 2D 1 column Array
300 Let strRws = Join(Application.Transpose((Evaluate("row(A1:E10)"))), " ")
310 Let strRws = Join(Application.Transpose((arr_2D1row())), " ") ' Join ( Transpose ( { 1; 2; 3; 4; 5; 6; 7; 8; 9; 10} ) ) = Join ( { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10} )
320
330 'Final required row Indicies, with a missing indicie, as a string
340 Let strrwsD = Replace(strRws, " 5 ", " ", 1)
350 Let strrwsD = Replace(strRws, " " & y & " ", " ", 1)

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 arrOut() = Application.Index(sn, rwsT(), clms())
470
480 Let Fu_snb = arrOut()
490 'Or
Let Fu_snb = Application.Index(sn, Application.Transpose(VBA.Split(Replace(Join(Appli cation.Transpose((Evaluate("row(A1:E10)"))), " "), " " & y & " ", " ", 1), " ")), Evaluate("column(A1:E10)"))
'Finally the "extra" named range bit:
'Let sn.Name = "snb_002"
Let Fu_snb = Application.Index(sn, Application.Transpose(VBA.Split(Replace(Join(Appli cation.Transpose((Evaluate("row(snb_002)"))), " "), " " & y & " ", " ", 1), " ")), Evaluate("column(snb_002)"))
' "Shorthand" evaluate
Let Fu_snb = Application.Index(sn, Application.Transpose(VBA.Split(Replace(Join(Appli cation.Transpose(([row(snb_002)])), " "), " " & y & " ", " ", 1), " ")), [column(snb_002)])
'Let Fu_snb = Application.Index(sn, Application.Transpose(VBA.Split(Trim(Replace(" " & Join(Evaluate("transpose(row(snb_002))")) & " ", " " & y & " ", " ")))), Evaluate("column(snb_002)"))
'or
'Let Fu_snb = Application.Index(sn, Application.Transpose(Split(Trim(Replace(" " & Join([transpose(row(snb_002))]) & " ", " " & y & " ", " ")))), [column(snb_002)])
End Function

[DocAElstein]

' 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 )



' 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
F1
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]

In Support of this Forum Question:
https://stackoverflow.com/questions/31439866/multiple-variable-arguments-to-application-ontime






Multiple Variable Arguments to Application.OnTime

I have fought with the tricky syntax for arguments to Application.OnTime ( or Application.Run, which is similar ) every time I have needed it. I have often gone here https://stackoverflow.com/questions/31439866/multiple-variable-arguments-to-application-ontime , as well as arrived a few times at the other links referenced below in the second post. As often they almost, but not quite, got me there.

I spent some time making myself some worked examples to reference in the future, and also convinced myself finally that I understand what is going on.

So I am sharing my solutions , and finally I think I can have a stab at answering thel question regarding concisely explaining / justifying the syntax..

I am deliberately giving very full explicit code lines for two reasons

_ 1. Its easy to simplify it to the more usual shortened version if you only need that, but going the other way , from the more common simplified form to the full explicit form, should you need that, is quite hard.

_ 2.Showing the full explicit code line syntax helps with my attempt at explain the syntax, and so is needed in answering the question fully.


The full explicit syntax would be needed , for example , to ensure the corrects file were opened, when we want to trigger a macro in a closed workbook. ( In such a case, the closed workbook would be opened. The VBA Application.OnTime code line will do this opening, provided it has the full explicit form )

I am using 2 example files, the first would be opened , the second can be closed or open , but the second should be in the same folder. ( The reason why it needs to be in the same folder is just for simplified demonstration, - I have organised that demonstration macros will look for the closed workbook in the same folder. In the practice, the closed workbook can be anywhere if you replace exactly this bit , ( including the first " ) , with the full path and file name of the closed workbook

" & ThisWorkbook.Path & "" & "UverFile.xls

In other words, you would replace that last bit with something like …_

C\Elston\Desktop\MyFolder\UverFile.xls

_ .. giving a complete code line of this sort of form:

Application.OnTime Now(), "'" & ThisWorkbook.Path & "\" & "UverFile.xls" & "'" & "!'Modul1.MacroInUverFile ""465""'": Debug.Print "!'Modul1.MacroInUverFile ""465""'"

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)

See here: http://www.excelfox.com/forum/showthread.php/2404-Notes-tests-Application-Run-OnTime-Multiple-Variable-Arguments-ByRef-ByVal?p=11861&viewfull=1#post11861
http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=12070#post12070



Worksheets Class module of first worksheet "Tabelle1" in MainFile.xls


Option Explicit
Sub InLisWbFirstWsCodeModule(ByRef Nmbr As Long)
MsgBox prompt:="Arg1 is " & Nmbr
Let Nmbr = 999
End Sub
Sub InLisWbFirstWsCodeModuleMultipleArguments(ByVal Nmbr As Long, ByVal NuverNmbr As Long)
MsgBox prompt:="Arg1 is " & Nmbr & ", Arg2 is " & NuverNmbr
End Sub



Closed workbook - UverFile.xls : https://app.box.com/s/u7r2jw79m8ou70otn7xcxced2qkot4w4

Module "Modul1" in UverFile.xls

Option Explicit
Private Sub MacroInUverFile(ByVal Nmbr As Long)
MsgBox prompt:="Arg1 is " & Nmbr
End Sub
Sub MacroUnderMacroInUverFile(ByVal Nmbr As Long, ByVal NuverNmbr As Long)
MsgBox prompt:="Arg1 is " & Nmbr & ", Arg2 is " & NuverNmbr
End Sub

Worksheets Class module of first worksheet "Tabelle1" in UverFile.xls


Option Explicit
Sub InUverFileFirstWsCodeModule(ByVal Nmbr As Long)
MsgBox prompt:="Arg1 is " & Nmbr
End Sub
Sub InUverFileFirstWsCodeModuleMultipleArguments(ByVal Nmbr As Long, ByVal NuverNmbr As Long)
MsgBox prompt:="Arg1 is " & Nmbr & ", Arg2 is " & NuverNmbr
End Sub





I have tried to give a good spread of working examples, which I found useful to then use as a template to modify to exactly my needs.


Here is the explanation to how things work , which makes the syntax more understandable:

First the nested '
This is generally how VBA handles making any spaces be taken as literal spaces, ( rather than , for example, mistaking them as separating arguments). You will see that in the codes, as I have posted I have done some exaggerated spaces in all code lines which helps to split up
_ the LHS , which in a simplified / shortened use would be typically be left out
and
_ the RHS , most of which is always needed . (Most likely are likely typically to see the macro name and the arguments . The extra module code name allows you to use macros in any modules , ( regardless of if they are ] Private or Pubic )

Just to make that clear, I have some exaggerated spaces in the code windows above either side of one of the `&`s , so pseudo I have

"---------LHS-------------" & "---------RHS------------------"
or like, pseudo

"String bit containing full path and file name what you mostly don't use" & "String bit containing the macro name and the arguments like you more typically see"
Those exaggerated extra spaces will vanish if you copy and paste that code into the VB editor code window. If I add spaces within the path string on the LHS, such as changing a file name from UverFile.xls to Uver File.xls , then as perhaps expected, the spaces will not change. This is because the enclosing ' ' is doing its job of ensuring that all is taken as literally as it is given.

On the RHS we need also that the information is taken exactly as we give it. This needs to be stored into a buffer from when it is then retrieved and pseudo physically put in. This is why I can add some rogue spaces, as I have done in the code section named ' mess about with argument positions. This modification is also not changed when you post into the VB Code window. This helps us to understand the nested " "

the nested " " in the variable arguments bit.

This is much less difficult then a lot of literature suggests. The only time you need those enclosing quote pair is if you are giving string values in the argument. That is generally the case in VBA code lines, the enclosing quote indicating that a string is being given. ( Since you are already inside a string, then the double quotes need to be doubled, as is standard VBA syntax).
If you are using variables, rather than hard coding, you never need this following often seen complicated syntax, ( provided you have your variables at the top of a module, outside any subroutine ). What I am saying is, that the following complicated argument syntax is, in most cases, more complicated than needed

""" & Arg1 & """ , """ & Arg2 & """


In most cases, that complicated form above can be reduced to this sort of form below

Arg1 , Arg2

To use that simplified form, the variables must be outside the macro with the scheduling Application.OnTime code line, and it must be at the top of the code module, or else, the scheduled macro which is to be set off by VBA later , won't know where to get the variables from

So do not really "need" that complicated syntax, provided you use "module level" variables. But if you use that complicated syntax, it will have the effect of placing the value from the variable in the final argument string that VBA puts into the code line it write to run the scheduled macro later. This would have the effect of that if you use that syntax, and your variables are local, then you might be fooled into thinking that you , ( that is to say VBA in the scheduled macro later ), are using the variables. In fact you are hard coding with values into the string that will finally be used by VBA later in the scheduled macro. I suppose you might say that is using variables within the calling macro, at least from the practical point of use. But understanding what is actually going on, helps , I think, to see where the sometime daunting syntax comes from.

In my demo macros, I refer to that way of using the calling macro variables as "Pseudo" variables use.

Further more, the point that Nick P was making in his answer, is that 4 of those quotes around each variable in that very complicated argument syntax, are there to give the typical required finally seen double enclosing " " pair around a string value. If one of those variables in the example, for example Arg2 , is a number, then even for the case of using the "trick" to make it appear that you are using variable within the scheduling macro, you can do away with some of those quotes, in particular the ones giving finally seen by VBA the the enclosing " " pair, reducing it to

""" & Arg1 & """ , " & Arg2 & "



Examining the right hand side syntax for macro name and arguments.

In all the coding I have a Debug.Print after each Application.OnTime code line. What this is showing is the actual RHS part of the string that VBA uses later when running the scheduled macro. So that is showing the part containing the macro name and the arguments. This helps to show the main point I am trying to get across.

For example, the string in what I refer to as the "Pseudo" variables use , looks like this:

!'Modul1.DoubleCheck "465.42" , "25.4" '

Or, as noted, if a variable, for example, the second is a number , then you can also use this

!'Modul1.DoubleCheck "465.42" , 25.4 '



For what I call the 'Real variable use , the string "seen" must actually use the variable names

!'Modul1.DoubleCheck Modul1.Pbic_Arg1 , Pbic_Arg2 '


Just to clarify that Last code line above. The sub routine being scheduled is Sub DoubleCheck( ) which I have located in my code module with the code name Modul1

Also in that same code module are placed at the top of the module , declarations for the variable, Pbic_Arg1 and Pbic_Arg2 . Pbic_Arg1 is Private , and Pbic_Arg2 is Publc


If you try my coding out running from the VB Editor in step ( F8 ) mode , whilst you have the Immediate Window open , then I think that will help make everything clear


Summary
See next post

[DocAElstein]

Summary

At the end of the day, the key to getting the syntax correct , and to understanding it , is as follows: You must arrange it such that what VBA "has", ( which you can check via a Debug.Print of the string you are giving ) needs to have a similar form to how you might manually write in arguments in a code line to call a sub routine taking in arguments. You can add a few extra spaces between multiple arguments and the separating comer , just as you might do carelessly when typing in manually a series of arguments. Presumably, VBA later, when it uses exactly your given string, it does something similar to what happens when you physically write or paste such things in, the result of which is that those extra spaces get removed.

The point of the enclosing ' ' is to indicate to VBA to take literally exactly as you have written it. In my explicit code lines we need that for both the LHS and the RHS. More typically the LHS is omitted.

Any use of a complicated combination of many double or triple " pairs is more of a trick to give you a way to effectively use variables that are within the scheduling macro , in the scheduling Application.OnTime code line. If your variables are in a code module outside of any sub routine, then the variable syntax is much simplified. In this case you do not actually need any quotes within the main string, not even if the a variable type is string. ( The complete second argument of the Application.OnTime always needs to be enclosed in a quote pair )


Alan


Ref
https://groups.google.com/forum/?hl=es#!msg/microsoft.public.excel.programming/S10tMoosYho/4rf3VBejtU0J
https://www.mrexcel.com/board/threads/calling-a-procedure-with-parameters.81724/#post398494
http://markrowlinson.co.uk/articles.php?id=10
http://www.tushar-mehta.com/publish_train/xl_vba_cases/1022_ByRef_Argument_with_the_Application_Run_metho d.shtml

P.S.
@ Holger Leichsenring - Hi . I think the apostrophes must enclose the macro name AND the arguments. Any number types can be passed without quotes. The macro you want to call can reside in any module, in any workbook, (open or closed) , and need not be Public. ( My geuss is that `Application.OnTime` uses the same wiring as `Application.Run` , which has the advantage, over simple Calling a sub , that it will run both Public and Private subs ( https://stackoverflow.com/questions/55266228/difference-between-calling-a-sub-and-application-run/59809370#59809370 ) )

Gruß, Alan

[DocAElstein]

This is the sort of output seen in the Immediate window, which is showing the right hand side of the Application.OnTime second argument string





Rem 1
This workbook module, single arrgument
!'Modul1.UnderMainMacro 465'
!'Modul1.UnderMainMacro "465"'

UverFile module, single argument
!'Modul1.MacroInUverFile 465'
!'Modul1.MacroInUverFile "465"'

Thisworkbook module, multiple arguments
!'Modul1.UnderUnderMainMacro 465, 25'
!'Modul1.UnderUnderMainMacro 465, "25"'

UverFile module, multiple argument
!'Modul1.MacroUnderMacroInUverFile 465, 25'
!'Modul1.MacroUndermacroInUverFile 465, "25"'

mess about with argument positions
!'Modul1.UnderUnderMainMacro 465 , "25" '

This workbook first worksheet code module, single arrgument
!'Tabelle1.InLisWbFirstWcCodeModule 465'
!'Tabelle1.InLisWbFirstWcCodeModule "465"'

UverFile first worksheet code module, single arrgument
!'Tabelle1.InUverFileFirstWsCodeModule 465'
!'Tabelle1.InUverFileFirstWsCodeModule "465"'

This workbook first worksheet code module, multiple arguments
!'Tabelle1.InLisWbFirstWcCodeModuleMultipleArgumen ts 465 , "25" '
!'Tabelle1.InLisWbFirstWcCodeModuleMultipleArgumen ts "465" , 25 '

UverFile first worksheet code module, Multiple arrgument
!'Tabelle1.InUverFileFirstWsCodeModuleMultipleArgu ments 465 , "25" '
!'Tabelle1.InUverFileFirstWsCodeModuleMultipleArgu ments "465" , "25" '

Doubles do not have to be in quotes either ' This workbook module, double argument arrgument
!'Modul1.DoubleCheck 465.5 , "25.4" '

Rem 2 Variables
'2a) "Pseudo" variables use
!'Modul1.DoubleCheck "465.42" , "25.4" '
!'Modul1.DoubleCheck "465.42" , 25.4 '

'2b) Real varable use
!'Modul1.DoubleCheck Modul1.Pbic_Arg1 , Pbic_Arg2 '
!'Modul1.DoubleCheck Modul1.Pbic_Arg1, Pbic_Arg2'
!'Modul1.DoubleCheck module2.Pbic_Arg1, Pbic_Arg2'



Rem 3 ByRef Check
25.4



Rem 3 ByRef Check
25.4



Rem 3 ByRef Check
999.99

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
http://www.eileenslounge.com/viewtopic.php?p=324457#p324457 (http://www.eileenslounge.com/viewtopic.php?p=324457#p324457)
http://www.eileenslounge.com/viewtopic.php?p=324064#p324064 (http://www.eileenslounge.com/viewtopic.php?p=324064#p324064)
http://www.eileenslounge.com/viewtopic.php?p=323960#p323960 (http://www.eileenslounge.com/viewtopic.php?p=323960#p323960)
https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg (https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg)
https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg.ADd4m2zp_xDADd6Nnotj 1C (https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg.ADd4m2zp_xDADd6Nnotj 1C)
s://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgySdtXqcaA27wQLd1t4AaABAg (s://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgySdtXqcaA27wQLd1t4AaABAg)
http://www.eileenslounge.com/viewtopic.php?p=323959#p323959 (http://www.eileenslounge.com/viewtopic.php?p=323959#p323959)
http://www.eileenslounge.com/viewtopic.php?f=30&t=41784 (http://www.eileenslounge.com/viewtopic.php?f=30&t=41784)
http://www.eileenslounge.com/viewtopic.php?p=323966#p323966 (http://www.eileenslounge.com/viewtopic.php?p=323966#p323966)
http://www.eileenslounge.com/viewtopic.php?p=323959#p323959 (http://www.eileenslounge.com/viewtopic.php?p=323959#p323959)
http://www.eileenslounge.com/viewtopic.php?p=323960#p323960 (http://www.eileenslounge.com/viewtopic.php?p=323960#p323960)
http://www.eileenslounge.com/viewtopic.php?p=323894#p323894 (http://www.eileenslounge.com/viewtopic.php?p=323894#p323894)
http://www.eileenslounge.com/viewtopic.php?p=323843#p323843 (http://www.eileenslounge.com/viewtopic.php?p=323843#p323843)
http://www.eileenslounge.com/viewtopic.php?p=323547#p323547 (http://www.eileenslounge.com/viewtopic.php?p=323547#p323547)
http://www.eileenslounge.com/viewtopic.php?p=323516#p323516 (http://www.eileenslounge.com/viewtopic.php?p=323516#p323516)
http://www.eileenslounge.com/viewtopic.php?p=323517#p323517 (http://www.eileenslounge.com/viewtopic.php?p=323517#p323517)
http://www.eileenslounge.com/viewtopic.php?p=323449#p323449 (http://www.eileenslounge.com/viewtopic.php?p=323449#p323449)
http://www.eileenslounge.com/viewtopic.php?p=323226#p323226 (http://www.eileenslounge.com/viewtopic.php?p=323226#p323226)
http://www.eileenslounge.com/viewtopic.php?f=25&t=41702&p=323150#p323150 (http://www.eileenslounge.com/viewtopic.php?f=25&t=41702&p=323150#p323150)
http://www.eileenslounge.com/viewtopic.php?p=323085#p323085 (http://www.eileenslounge.com/viewtopic.php?p=323085#p323085)
http://www.eileenslounge.com/viewtopic.php?p=322955#p322955 (http://www.eileenslounge.com/viewtopic.php?p=322955#p322955)
http://www.eileenslounge.com/viewtopic.php?f=30&t=41659 (http://www.eileenslounge.com/viewtopic.php?f=30&t=41659)
http://www.eileenslounge.com/viewtopic.php?p=322462#p322462 (http://www.eileenslounge.com/viewtopic.php?p=322462#p322462)
http://www.eileenslounge.com/viewtopic.php?p=322356#p322356 (http://www.eileenslounge.com/viewtopic.php?p=322356#p322356)
http://www.eileenslounge.com/viewtopic.php?p=321984#p321984 (http://www.eileenslounge.com/viewtopic.php?p=321984#p321984)
https://eileenslounge.com/viewtopic.php?f=30&t=41610 (https://eileenslounge.com/viewtopic.php?f=30&t=41610)
https://eileenslounge.com/viewtopic.php?p=322176#p322176 (https://eileenslounge.com/viewtopic.php?p=322176#p322176)
https://eileenslounge.com/viewtopic.php?p=322238#p322238 (https://eileenslounge.com/viewtopic.php?p=322238#p322238)
https://eileenslounge.com/viewtopic.php?p=322270#p322270 (https://eileenslounge.com/viewtopic.php?p=322270#p322270)
https://eileenslounge.com/viewtopic.php?p=322300#p322300 (https://eileenslounge.com/viewtopic.php?p=322300#p322300)
http://www.eileenslounge.com/viewtopic.php?p=322150#p322150 (http://www.eileenslounge.com/viewtopic.php?p=322150#p322150)
http://www.eileenslounge.com/viewtopic.php?p=322111#p322111 (http://www.eileenslounge.com/viewtopic.php?p=322111#p322111)
http://www.eileenslounge.com/viewtopic.php?p=322086#p322086 (http://www.eileenslounge.com/viewtopic.php?p=322086#p322086)
https://stackoverflow.com/questions/33868233/shell-namespace-not-accepting-string-variable-but-accepting-string-itself/77888851#77888851 (https://stackoverflow.com/questions/33868233/shell-namespace-not-accepting-string-variable-but-accepting-string-itself/77888851#77888851)
http://www.eileenslounge.com/viewtopic.php?p=322084#p322084 (http://www.eileenslounge.com/viewtopic.php?p=322084#p322084)
http://www.eileenslounge.com/viewtopic.php?p=321822#p321822 (http://www.eileenslounge.com/viewtopic.php?p=321822#p321822)
http://www.eileenslounge.com/viewtopic.php?p=322424#p322424 (http://www.eileenslounge.com/viewtopic.php?p=322424#p322424)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[DocAElstein]

Second Code with further lines to overcome extra () required for start row and star column codes



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 2: Its n Range Name Test 2: 's 'n Rng Name Test 2: s n Rg Name Testie 2: snRg.Name = "snRgNme"
This code is in support of other Posts in various Threads. ( I will edit the Links as I reference this post )




The code takes in a hard coded Range, A1:E10.
That Range is given a Name as held in the Names Register of a Workbook ( Workbooks Scope ).
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 column count
sClm is the start column
stpClm is the stop column
Rs is the rows count start row
sRw is the start row
stpRw is the stop row


' Code 2
'10 ' It is a Range Name Test 2: Its n Range Name Test 2: 's 'n Rng Name Test 2: s n Rg Name Testie 2: snRg.Name = "snRgNme"
Sub snRgNameTest2() ' 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 ' Workbooks ( Default ) Scope 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
130 '== DANGER: === Pitful: Above we gave the Range Object a Name, but now see what "Name" or "Name" 's comes back "!" !
131 Dim clms() As Variant 'Array to take returned Variant type Field of sequential column numbers
132 Dim retRefstrName As String, retObjName As Object
133 Let retRefstrName = snRg.Name: Set retObjName = snRg.Name: Debug.Print snRg.Name 'something of the form "NPueyoGyanArraySlicing!$A$1:$E$10" is reveald in Immediate ( Ctrl+G when in VB Editor ) Window
134 'Let clms() = Evaluate("column(=NPueyoGyanArraySlicing!$A$1:$E$10)") 'Let clms() = Evaluate("column(" & retRefstrName & ")")' Rintime Error 13: Incompatiblee types
135 Let clms() = Evaluate("column(NPueyoGyanArraySlicing!$A$1:$E$10)") 'Works
137 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" This is a String referrece returned when the Name Object is used directly or set to a String Variable. 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
138 Let clms() = Evaluate("column(" & NameOnly & ")"): Let clms() = Evaluate("column(" & Replace((snRg.Name), "!", "", (InStr(1, (snRg.Name), "!"))) & ")")
139
140 Dim strName As String: Let strName = snRg.Name.Name: Debug.Print strName: Let strName = retObjName.Name: Debug.Print strName ' returns our original "CoN"
142 Let clms() = Evaluate("column(" & strName & ")")
150 Dim rngF1G2 As Range: Set rngF1G2 = Range("F1:G2"): Let Range("F1:G2").Value = "From Line 150"
151 Let Range("=NPueyoGyanArraySlicing!F1:G2").Value = "From Line 151"
152 Let rngF1G2.Name = "snFG": Let Range("snFG").Value = "From Line 152"
153
154
159 '===============
160 Let clms() = Evaluate("column(snRgNme)"): Let clms() = [column(snRgNme)] ' Full and "shorthand" Simple 1 D "pseudo horizontal" Array of column Indicies.
170 '
180 ' Count, Start, and Stop of columns in an Area of contiguous cells in a Spreadsheet
190 Dim Cs 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 Cs = Evaluate("columns(snRgNme)") ' = 5
210 'Let Cs = Evaluate("columns(RetunedsnRgName)") 'Run time Error as expected
220 Let Cs = [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 Cs = [columns(RetunedsnRgName)] 'Run time Error as expected
240 Let Cs = [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 sClm As Long 'Variable for Start Column
270 Let sClm = Evaluate("column(A1:E10)")(1)
280 Let sClm = Evaluate("column(snRgNme)")(1) ' = 1
290 Let sClm = [column(A1:E10)]()(1)
300 Let sClm = [column(snRgNme)]()(1)
301
302 Let sClm = Evaluate("=MIN(column(snRgNme))"): Let sClm = [=MIN(column(snRgNme))] 'Alternative using Spreadsheet Functions to avoid having to VBA ()( ) after the Evaluate
329 '
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 = sClm + (Cs - 1) ' = 5
350 ' [ ]
360 Let stpClm = [column(A1:E10)]()(1) + ([columns(A1:E10)] - 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)")))
421
430 Let stpClm = Evaluate("=MIN(column(snRgNme))") + (Evaluate("columns(snRgNme)") - 1) ''Alternatives using Spreadsheet Functions to avoid having to VBA ()( ) after the Evaluate
431 Let stpClm = [=MIN(column(snRgNme))] + ([columns(snRgNme)] - 1)
432 Let stpClm = [=MIN(column(snRgNme)) + (columns(snRgNme) - 1)]
439 '
440 ' Start, Count and Stop of rows in an Area of contiguous cells in a Spreadsheet
450 Dim Rs As Long 'Rows Count
460 Let Rs = Evaluate("rows(snRgNme)")
470 Let Rs = [rows(snRgNme)]
480 Let Rs = [rows(A1:E10)]
490 Let vTemp = Evaluate("row(snRgNme)") ' = {1; 2; 3; 4; 5; 6; 7; 8; 9; 10}
500 Dim sRw As Long 'Start Row
510 Let sRw = 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 sRw = Evaluate("row(snRgNme)")(1, 1)
530 Let sRw = [row(A1:E10)]()(1, 1)
540 Let sRw = [row(snRgNme)]()(1, 1)
541
550 Let sRw = Evaluate("=MIN(Row(snRgNme))"): Let sRw = [=MIN(Row(snRgNme))] '''Alternatives using Spreadsheet Functions to avoid having to VBA ()( ) after the Evaluate
560
570 Dim stpRw% 'Stop Row
580 Let stpRw = sRw + (Rs - 1)
590 Let stpRw = [row(A1:E10)]()(1, 1) + ([rows(A1:E10)] - 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 '
670 Let stpRw = Evaluate("=MIN(Row(snRgNme))") + (Evaluate("rows(snRgNme)") - 1) ''''Alternatives using Spreadsheet Functions to avoid having to VBA ()( ) after the Evaluate
680 Let stpRw = [=MIN(Row(snRgNme))] + [rows(snRgNme)] - 1
690 Let stpRw = [=MIN(Row(snRgNme))] + [rows(snRgNme)] - 1
700 Let stpRw = [=MIN(Row(snRgNme)) + rows(snRgNme) - 1]
End Sub

[DocAElstein]

Alternative Codes using [ ] shorthand
Delete One Row From A 2D Excel Range Area

' To Test Function, Type some arbitrary values in range A1:E10, step through Main 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



' 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
' "Short hand", ShtHd, version using []
' requires snb and kalak "neat trick" so you can to all intents and purpose do very close to doing vba Un Hard coded in [ ] - http://www.excelfox.com/forum/showthread.php/2083-Delete-One-Row-From-A-2D-Variant-Array?p=9714#post9714 http://www.mrexcel.com/forum/excel-questions/899117-visual-basic-applications-range-a1-a5-vs-%5Ba1-a5%5D-benefits-dangers.html#post4331217
' and Evaluate [ ] Properties , AloPerties, Methods - Alan Dynamic Coding Wonks http://www.excelforum.com/excel-programming-vba-macros/1141369-evaluate-and-differences-evaluated-array-return-needs-extra-bracket-for.html#post4400666
Function FuR_AlanShtHd(ByVal rngIn As Range, ByVal FoutRw As Long) As Variant
1 Let rngIn.Name = "snRgNme"
5 Dim vTemp As Variant 'A varyable to fill with something and "suck and see" what you get
10 ' use "neat magic" code line arrOut() = Application.Index(arrIn(), rwsT(), clms()) ' http://www.excelforum.com/excel-new-users-basics/1099995-application-index-with-look-up-rows-and-columns-arguments-as-vba-arrays.html http://www.mrexcel.com/forum/excel-questions/908760-visual-basic-applications-copy-2-dimensional-array-into-1-dimensional-single-column-2.html#post4375354
20 ' BUT in Cells form arrOut() = Application.Index(Cells, rwsT(), clms()) ' http://www.excelforum.com/excel-programming-vba-macros/1105617-trouble-writing-huge-array-into-worksheet-range-2.html
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 = rngIn.Parent ' 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 ws.Range("K30").ClearContents: ws.Range("K30").Value = "Here I am, in this Worksheet!"
60 'clms()
70 Dim sClm As Long, Cs As Long 'Variable for Count of, Start Column. - 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)
80 Let sClm = [=MIN(column(snRgNme))] '###Not needed now. Alternative using Spreadsheet Functions to avoid having to VBA ()( ) after the Evaluate
90 Dim clms() As Variant ' Evaluate Function used below returns a Field of Variant Element Types so the Array Elemments must be Declared appropriately. Must be adynamic Array to accept and be effectivelly sized by the Field size assigned to it.
95 Let clms() = [column(snRgNme)] ' snb Range Name equivalent so ### 'Let clms() = Evaluate("column(" & CL(sClm) & ":" & CL(sClm + (Cs - 1)) & ")")
100
160 'rwsT()
170 Dim sRw As Long, Rs As Long, stpRw As Long '
180 Let sRw = [=MIN(Row(snRgNme))] '''Alternatives using Spreadsheet Functions to avoid having to VBA ()( ) after the Evaluate ' Let sRw = rngIn.Areas.Item(1).Row
190 Let Rs = [rows(snRgNme)] 'Let Rs = rngIn.Areas.Item(1).Rows.Count
191 Let stpRw = [=MIN(Row(snRgNme)) + rows(snRgNme) - 1] '''Alternatives using Spreadsheet Functions to avoid having to VBA ()( ) after the Evaluate
200
240 'Get Full row indicies convenientally ( As 1 D "pseudo horizontal" Array ) from Spreadsheet Column() Function
250 Dim rws() As Variant: Let rws() = Evaluate("column(" & CL(sRw) & ":" & CL(sRw + (Rs - 1)) & ")")
251 Let vTemp = [CL(1)]: vTemp = [CL(MIN(Row(snRgNme)))] 'Both Return "A"
252 vTemp = [CL(MIN(Row(snRgNme)) + rows(snRgNme) - 1)] 'Returns "J"
254 Let rws() = [column(A:J)] ' Works
257 'Let rws() = [column(CL(1):J)] ' Fails - Bug in Excel ! ? !
258 'Let rws() = [column(CL(MIN(Row(snRgNme))):CL(MIN(Row(snRgNme)) + rows(snRgNme) - 1))] ' Fails - Bug in Excel ! ? !
260 Let rws() = Evaluate("column(" & [CL(MIN(Row(snRgNme)))] & ":" & [CL(MIN(Row(snRgNme)) + rows(snRgNme) - 1)] & ")")
270
280 'Get full sequential row indicies in a string.
290 Dim strRws As String: Let strRws = VBA.Strings$.Join(rws(), "|") ' 'The VBA strings collection such as Join in there basic form must not returnn a string, they can also return for example Null, a special type of variant. That lies within it's "powers. - It will coerce even an Empty, or Null to a variant type and return that. That takes extra ( unecerssary work here ). If the result of a function is used as a string or assigned it to a string variable, use the $ form of the function. This results in faster executing code, because a conversion from a variant to a string is unnecessary. http://www.excelforum.com/excel-new-users-basics/1058406-range-dimensioning-range-and-value-referencing-and-referring-to-arrays-5.html#post4084783 I believe that without $ a Strings collection Function coerces the first parameter into Variant, with $ does not - that's why $ is preferable over no $ , it's theoretically more efficient. http://www.xoc.net/standards/rvbacc.asp#DollarSignFunctions
300
330 'Get String with missing row
340 Dim strrwsD As String: Let strrwsD = Replace(strRws, "|" & FoutRw & "", "", 1, -1)
350
360 'Get Array ( 1 D Pseudo Horizontal ) of required row indicies
370 Dim rwsS() As String ' The VBA Strings Collection Function, Split, used below returns a Field of String Element Types so the Array Elemments must be Declared appropriately. It must be adynamic Array to accept and be effectivelly sized by the Field size assigned to it.
375 Let rwsS() = VBA.Strings$.Split(strrwsD, "|", -1)
380 'final Transposed Array for "magic neat" code line
390 Dim rwsT() As String: ReDim rwsT(0 To (UBound(rwsS())), 1 To 1) ' Both the type and size of Array is known so can be decared initially appropriatelly. Re Dim must be used as Dim only takes values, not variables
400 Dim Cnt As Long
410 For Cnt = 0 To UBound(rwsS())
420 Let rwsT(Cnt, 1) = rwsS(Cnt)
430 Next Cnt

440 'Output Array
450 Dim arrOut() As Variant
460 Let arrOut() = Application.Index(ws.Cells, rwsT(), clms()) '"Magic neat" Code line in Cells first argument Form
470
480 Let FuR_AlanShtHd = arrOut()
490
500 ' . Transpose
510 Dim rwsDotT() As Variant ' Transpose Function used below returns a Field of Variant Element Types so the Array Elemments must be Declared appropriately. Must be adynamic Array to accept and be effectivelly sized by the Field size assigned to it.
520 Let rwsDotT() = Application.Transpose(rwsS())
530 Let arrOut() = Application.Index(ws.Cells, rwsDotT(), clms())
540
550 Let FuR_AlanShtHd = arrOut()
'
End Function

[DocAElstein]

More Named Range Scope Wonks. Problems when Worksheet Scoped WorkSheet is different from Worksheet refered to in RefersTo:= Range Object argument


Here is a another partial solution to the This Thread
http://www.excelforum.com/excel-programming-vba-macros/1141369-evaluate-and-differences-evaluated-array-return-needs-extra-bracket-for.html

It was also used to answer a few questions I had here:
http://www.thespreadsheetguru.com/blog/the-vba-guide-to-named-ranges[I][COLOR="#000080"] ( Comment 22 )
Here is what I wrote there:
Reply Posted at
http://www.thespreadsheetguru.com/blog/the-vba-guide-to-named-ranges
6 th June 2016:

Hi
Hi Just feeding back from my "experiments" over the weekend_...
http://www.excelforum.com/showthread.php?t=1141369&page=2&p=4404276&highlight=#post4404276
_..So now o answer my questions:
_1) I have not yet seen anything to suggest the answer to that is not yes.

[DocAElstein]

Referrences in suppost of this post:
http://www.excelfox.com/forum/showthread.php/2130-Sort-an-array-based-on-another-array-VBA?p=9985#post9985

and solution to this post
http://www.excelforum.com/excel-programming-vba-macros/1160648-how-to-create-a-pop-up-notification-for-two-different-conditions-at-the-same-time.html#post4507157



' http://www.snb-vba.eu/VBA_Arraylist_en.html
' http://www.snb-vba.eu/VBA_Arraylist_en.html#L_11.3


' https://usefulgyaan.wordpress.com/2013/06/12/vba-trick-of-the-week-slicing-an-array-without-loop-application-index/comment-page-1/#comment-587

' https://usefulgyaan.wordpress.com/2013/06/12/vba-trick-of-the-week-slicing-an-array-without-loop-application-index/comment-page-1/#comment-515






' https://usefulgyaan.wordpress.com/2013/06/12/vba-trick-of-the-week-slicing-an-array-without-loop-application-index/comment-page-1/#comment-587
Sub M_snbSortof() ' http://www.snb-vba.eu/VBA_Arraylist_en.html#L_11.3
Dim rngVoll As Range: Set rngVoll = Tabelle3.Range("A1:E10")
Dim snAll() As Variant, Sported() As Variant
Let snAll() = rngVoll.Value
Dim j As Long, jj As Long
With CreateObject("System.Collections.Arraylist")
For j = 1 To UBound(snAll(), 1)
.Add snAll(j, 3)
Next
.Sort
Let Sported() = .ToArray
.Clear
For j = 0 To UBound(Sported())
For jj = 1 To UBound(snAll(), 1)
If snAll(jj, 3) = Sported(j) Then
' Use Range to overcome Array size Limits of Worksheets Functions
'Dim Clm As Range: Set Clm = Application.Index(rngVoll, jj, 0)
' .Add Clm.Value
' .Add (Application.Index(rngVoll, jj, 0).Value)

' Use Cells to overcome Array size Limits of Worksheets Functions
Dim LB As Long, UB As Long '…User Given start and Stop Column as a Number
Let LB = LBound(snAll(), 2): Let UB = UBound(snAll(), 2)
Dim strLtrLB As String, strLtrUB As String '…Column Letter corresponding to Column Number
'There are many ways to get a Column Letter from a Column Number – excelforum.com/tips-and-tutorials/1108643-vba-column-letter-from-column-number-explained.html
Let strLtrLB = Split(Cells(1, LB).Address, "$")(1) 'An Address Method
Let strLtrUB = Replace (Replace(Cells(1, UB).Address, "1", ""), "$", "") 'A Replace Method
'Obtain Column Indicies using Spreadsheet Function Column via VBA Evaluate Method
Dim clms() As Variant
Let clms() = Evaluate("column(" & strLtrLB & ":" & strLtrUB & ")") 'Returns 1 D “pseudo” Horizontal Array of sequential numbers from column number of LB to UB
'Or
clms() = Evaluate("column(" & Split(Cells(1, LB).Address, "$")(1) & ":" & Replace (Replace(Cells(1, UB).Address, "1", ""), "$", "") & ")")
.Add (Application.Index(Tabelle3.Cells, jj, clms()))

'Let snAll(jj, 3) = ""
Exit For
End If
Next jj
Next j
For j = 0 To .Count - 1
Tabelle3.Cells(j + 1 + 10, 1).Resize(, UBound(snAll, 2)) = .Item(j)
Next j
End With
End Sub
'
Sub M_snb()
Dim sn, sp, j As Long, jj As Long
sn = Tabelle3.Range("A1:E10")
With CreateObject("System.Collections.Arraylist")
For j = 1 To UBound(sn)
.Add sn(j, 3)
Next
.Sort
sp = .ToArray
.Clear
For j = 0 To UBound(sp)
For jj = 1 To UBound(sn)
If sn(jj, 3) = sp(j) Then
.Add Application.Index(sn, jj)
sn(jj, 3) = ""
Exit For
End If
Next
Next
For j = 0 To .Count - 1
Tabelle3.Cells((j + 1) + 10, 1).Resize(, UBound(sn, 2)) = .Item(j)
Next
End With
End Sub































'Rem Ref
' http://www.excelforum.com/excel-programming-vba-macros/1139207-how-to-move-a-userform-and-module-from-one-book-to-another-2.html
' http://www.excelforum.com/excel-programming-vba-macros/1138300-vba-userform-value-check-if-user-form-buttons-checked-not-working-check-button-on-open.html
' http://www.excelforum.com/excel-programming-vba-macros/1139742-workbooks_open-crashes-when-file-opened-with-code-manually-open-ok-userform-issue.html
' http://www.excelfox.com/forum/showthread.php/2130-Sort-an-array-based-on-another-array-VBA?p=9985#post9985
' http://www.snb-vba.eu/VBA_Arraylist_en.html
' http://www.snb-vba.eu/VBA_Arraylist_en.html#L_11.3
' http://www.excelforum.com/showthread.php?t=1154829&page=4#post4502593
' http://www.excelforum.com/excel-programming-vba-macros/1160648-how-to-create-a-pop-up-notification-for-two-different-conditions-at-the-same-time.html#post4507157
' http://www.excelfox.com/forum/showthread.php/2130-Sort-an-array-based-on-another-array-VBA?p=9985#post9985






http://www.excelforum.com/showthread.php?t=1154829&page=4#post4502593

[DocAElstein]

Hi Nelson,
I think both
_ the ways to do this,
_and the possibly output forms
are infinite

_ You must try to be a bit more precise in exactly what you want.

_ I have done another fairly simple code:

It is here:
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10047#post10047

It runs though all the Worksheets to calculate normal overtime and holiday overtime. The total added for all Worksheets is displayed in a message box:
NelsonMessageBox.jpg http://imgur.com/XSvQpQi
1863




I expect the code is not yet want you finally want. It tells you the total sum for Normal Overtime and Holiday overtime.
( I still do not understand what calculations should be done for total days :confused: )

Please start a new Thread here: _.. http://www.excelfox.com/forum/forumdisplay.php/2-Excel-Help

_.. Please try again to explain exactly what you want.
_.. I will then look at it again for you on Monday

:)
Alan

Code:

' An Initial code for Nelson for Post in this Forum http://www.excelfox.com/forum/forumdisplay.php/2-Excel-Help
Sub SomeingSumTotals() ' https://www.dropbox.com/s/u76eo5trrtppgoi/SAMPLE2.xlsx?dl=0
Rem 1) Worksheets info.
Dim WsStear As Worksheet ' Dim: For Object variabls: Address location to a "pointer". That has all the actual memory locations (addresses) of the various property values , and it holds all the instructions what / how to change them , should that be wanted later. That helped explain what occurs when passing an Object to a Call ed Fucntion or Sub Routine By Val ue. In such an occurance, VBA actually passes a copy of the pointer. So that has the effect of when you change things like properties on the local variable , then the changes are reflected in changes in the original object. (The copy pointer instructs how to change those values, at the actual address held in that pointer). That would normally be the sort of thing you would expect from passing by Ref erence. But as that copy pointer "dies" after the called routine ends, then any changes to the Addresses of the Object Properties in the local variable will not be reflected in the original pointer. So you cannot actually change the pointer.)
' Set: Fill or partially Fill: Setting to a Class will involve the use of an extra New at this code line. I will then have an Object referred to as an instance of a Class. At this point I include information on my Pointer Pigeon hole for a distinct distinguishable usage of an Object of the Class. For the case of something such as a Workbook this instancing has already been done, and in addition some values are filled in specific memory locations which are also held as part of the information in the Pigeon Hole Pointer. We will have a different Pointer for each instance. In most excel versions we already have a few instances of Worksheets. Such instances Objects can be further used., - For this a Dim to the class will be necessary, but the New must be omitted at Set. I can assign as many variables that I wish to the same existing instance
Rem 2) varables for some totals ;)
Dim NOHrsV2 As Double, HOHrsV2 As Double, TDays As Long
Let NOHrsV2 = 0: Let HOHrsV2 = 0: Let TDays = 0
Rem 3) Loop through worksheets and give some Totals
Dim Cnt As Long ' Loop Bound variable count for going through all worksheets
For Cnt = 1 To ThisWorkbook.Worksheets.Count
Set WsStear = ThisWorkbook.Worksheets.Item(Cnt) ' Set: Fill or partially Fill: Setting to a Class will involve the use of an extra New at this code line. I will then have an Object referred to as an instance of a Class. At this point I include information on my Pointer Pigeon hole for a distinct distinguishable usage of an Object of the Class. For the case of something such as a Workbook this instancing has already been done, and in addition some values are filled in specific memory locations which are also held as part of the information in the Pigeon Hole Pointer. We will have a different Pointer for each instance. In most excel versions we already have a few instances of Worksheets. Such instances Objects can be further used., - For this a Dim to the class will be necessary, but the New must be omitted at Set. I can assign as many variables that I wish to the same existing instance
Dim lr As Long ' Long is very simple to handle, - final memory "size" type is known (123.456 and 000.001 have same "size" computer memory ) , and so a Address suggestion can be given for the next line 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 anyways, so a Long is actually faster. )
Let lr = WsStear.Range("E" & Rows.Count & "").End(xlUp).Row ' The Range Object ( cell ) that is the last cell in the column of interest (CHOOSE a column typically that will always have a last Entry in any Data) ,( Row Number given by .Count Property applied to ( any Worksheet would do, so leaving unqualified is OK here, ) Spreadsheet Range Rows Property) has the Property .End ( argument "Looking back up" ) appled to it. This Returns a new Range ( cell ) object which is that of the first Range ( cell ) with something in it "looking back up" in the XL spreadsheet from that last Cell. Then the .Row Property is applied to return a long number equal to the Row number of that cell: Rows.Count is the very last row number in your Worksheet. It is different for earlier versions of Excel. The End(xlUp) is the same as pressing a Ctrl+UpArrow key combination. The final ".Row" returns the row where the cursor stops after moving up.
Dim FstDtaCel As Range: Set FstDtaCel = WsStear.Range("A2") ' Worksheets Range(" ") Property used to return Range object of first cell in second row
Dim arrInNorm() As Variant, arrInOver() As Variant ' In the next lines the .Value2 Property is applied a Range object which presents the the Value2 value or values in a single variable of appropriate type or a field of member Elements of varaint types.We are expecting the latter, so declare ( Dim ) a dynamic Array variable appropriately. It must be dynamic as its size will be defined at that assignment
Let arrInNorm() = FstDtaCel.Offset(0, 8).Resize(lr - 1, 1).Value2 ' One thing you pick up when learning VBA programming is that referring to cells from one to another via an offset is both fundamental and efficient. That makes sense as Excel is all about using the offsets mentioned above. So like if you use them you can “cut out the middle man”. ( The middle man here might be considered as, for example, in VBA, using extra variables for different Range objects: A fundamental thing to do with any cell ( or strictly speaking the Range object associated to a cell ) is the Range Item Property of any range Object, through which you can “get at” any other Range object. http://www.excelforum.com/showthread.php?t=1154829&page=13&p=4563838&highlight=#post4563838 ( It is often quicker than using a separate variable for each Range object – probably as all the variable does is hold the offset , so you might as well use the offset in the first place.. ) )
Let arrInOver() = FstDtaCel.Offset(0, 9).Resize(lr - 1, 1).Value2 ' Similarly Another thing you pick up along the way is that the cells ( or strictly speaking the Range objects associated with it ) can be organised into groups of cells which then are also called Range objects and are organised in their constituent parts exactly the same as for the single cell Range object. Once again this is all an indication of organising so that we get at information by sliding along a specific amount ( offset value). The Offset and Resize properties therefore return a new range object. I use the .Value 2 here as i seemed to get it for .Value anyway, not sure why yet, - so i thought be on the safe side , get it always and work somehow with that for now and convert as necerssary.
Dim ShtCnt As Long ' Loop Bound Variable Count for hours columns looping
For ShtCnt = 1 To UBound(arrInNorm(), 1) Step 1
If arrInNorm(ShtCnt, 1) <> 0 And arrInOver(ShtCnt, 1) <> 0 Then Let NOHrsV2 = NOHrsV2 + arrInOver(ShtCnt, 1)
If arrInNorm(ShtCnt, 1) = 0 And arrInOver(ShtCnt, 1) <> 0 Then Let HOHrsV2 = HOHrsV2 + arrInOver(ShtCnt, 1)
Next ShtCnt
Next Cnt
Rem 4) Output some totals ;)
MsgBox prompt:="Normal Overtime is " & NOHrsV2 * 24 & vbCrLf & "Holiday Overtime is" & HOHrsV2 * 24 ' NelsonMessageBox.jpg http://imgur.com/XSvQpQi
'4b) Tell you Totals ' Sub Speach() ' Richard Buttrey http://www.excelforum.com/showthread.php?t=1164765&p=4535112#post4535112
Dim Saps As Object
Set Saps = CreateObject("SAPI.SpVoice")
Saps.Speak "Hello Nelson. These are Sum totals for Normal Overtime and Holiday Overtime for the two Worksheets you gave.. Normal Overtime is " & NOHrsV2 * 24 & ".. Holiday Overtime is " & HOHrsV2 * 24 & ".. I expect this is not yet quite what you want."
' ' End Sub
End Sub

[DocAElstein]

Code for Nelson in this Thread
http://www.excelfox.com/forum/showthread.php/2144-Code-Required-to-calculate-number-of-days-worked-normal-overtime-and-holiday-overtime
http://www.excelfox.com/forum/showthread.php/2144-Code-Required-to-calculate-number-of-days-worked-normal-overtime-and-holiday-overtime?p=10060#post10060



Option Explicit
Sub IJAdjustTotalAllWorksheet() ' http://www.excelfox.com/forum/showthread.php/2144-Code-Required-to-calculate-number-of-days-worked-normal-overtime-and-holiday-overtime?p=10060#post10060
Rem 1) Workbooks Info.
Dim Wb As Workbook ' Dim: For Object variabls: Address location to a "pointer". That has all the actual memory locations (addresses) of the various property values , and it holds all the instructions what / how to change them , should that be wanted later. That helped explain what occurs when passing an Object to a Call ed Fucntion or Sub Routine By Val ue. In such an occurance, VBA actually passes a copy of the pointer. So that has the effect of when you change things like properties on the local variable , then the changes are reflected in changes in the original object. (The copy pointer instructs how to change those values, at the actual address held in that pointer). That would normally be the sort of thing you would expect from passing by Ref erence. But as that copy pointer "dies" after the called routine ends, then any changes to the Addresses of the Object Properties in the local variable will not be reflected in the original pointer. So you cannot actually change the pointer.)
Set Wb = ActiveWorkbook ' Set: Fill or partially Fill: Setting to a Class will involve the use of an extra New at this code line. I will then have an Object referred to as an instance of a Class. At this point I include information on my Pointer Pigeon hole for a distinct distinguishable usage of an Object of the Class. For the case of something such as a Workbook this instancing has already been done, and in addition some values are filled in specific memory locations which are also held as part of the information in the Pigeon Hole Pointer. We will have a different Pointer for each instance. In most excel versions we already have a few instances of Worksheets. Such instances Objects can be further used., - For this a Dim to the class will be necessary, but the New must be omitted at Set. I can assign as many variables that I wish to the same existing instance
Dim wsStear As Worksheet ' Used for each Worksheet counting Tabs from left from 1 To Total
Rem 2) varables for some totals ;)
Const TDays As Long = 30 'Total days just taken as 30 ' Long is very simple to handle, - final memory "size" type is known (123.456 and 000.001 have same "size" computer memory ) , and so a Address suggestion can be given for the next line 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 anyways, so a Long is actually faster. )
Dim NOHrsV2 As Double, HOHrsV2 As Double ' I am proposing to use the underlying number an adjust as necerssary to get the reqired format
Dim Dte As Date, DteNo As Long ' I am hoping Dte will sort out getting a date in a format that I can use the Weekday function to see what week day it is and get that as a nuumber to check for..
Rem 3) Loop through worksheets and give some Totals
Dim Cnt As Long ' Loop Bound variable count for going through all worksheets
'3a) main Loop start============================================= ========
For Cnt = 1 To Wb.Worksheets.Count ' The Worksheets collection Object Property returns the number of worksheet items in the Workbook
Let NOHrsV2 = 0: Let HOHrsV2 = 0 ' The varaibles are emtied before run for each worksheet
Set wsStear = Wb.Worksheets.Item(Cnt) ' At each loop the variable is set to the current Worksheet counting from the Cnt'ths tab from left
Dim lr As Long ' Used for last row number in column E
Let lr = wsStear.Range("E" & Rows.Count & "").End(xlUp).Row ' The Range Object ( cell ) that is the last cell in the column of interest (CHOOSE a column typically that will always have a last Entry in any Data) ,( Row Number given by .Count Property applied to ( any Worksheet would do, so leaving unqualified is OK here, ) Spreadsheet Range Rows Property) has the Property .End ( argument "Looking back up" ) appled to it. This Returns a new Range ( cell ) object which is that of the first Range ( cell ) with something in it "looking back up" in the XL spreadsheet from that last Cell. Then the .Row Property is applied to return a long number equal to the Row number of that cell: Rows.Count is the very last row number in your Worksheet. It is different for earlier versions of Excel. The End(xlUp) is the same as pressing a Ctrl+UpArrow key combination. The final ".Row" returns the row where the cursor stops after moving up.
Dim FstDtaCel As Range: Set FstDtaCel = wsStear.Range("A1") ' Worksheets Range(" ") Property used to return Range object of first cell in second row
'3b) Data arrays from worksheet. We need columns E H I J .... Date ( Column E ) and Total hrs ( Column H ) are required to use in calculations
Dim arrDte() As Variant, arrTotHrs() As Variant ' In the next lines the .Value2 or .value Property is applied a Range object which presents the Value or Value2 value or values in a single variable of appropriate type or a field of member Elements of varaint types.We are expecting the latter, so declare ( Dim ) a dynamic Array variable appropriately. It must be dynamic as its size will be defined at that assignment
Let arrDte() = FstDtaCel.Offset(0, 4).Resize(lr, 1).Value ' E ' One thing you pick up when learning VBA programming is that referring to cells from one to another via an offset is both fundamental and efficient. That makes sense as Excel is all about using the offsets mentioned above. So like if you use them you can “cut out the middle man”. ( The middle man here might be considered as, for example, in VBA, using extra variables for different Range objects: A fundamental thing to do with any cell ( or strictly speaking the Range object associated to a cell ) is the Range Item Property of any range Object, through which you can “get at” any other Range object. http://www.excelforum.com/showthread.php?t=1154829&page=13&p=4563838&highlight=#post4563838 ( It is often quicker than using a separate variable for each Range object – probably as all the variable does is hold the offset , so you might as well use the offset in the first place.. )
Let arrTotHrs() = FstDtaCel.Offset(0, 7).Resize(lr, 1).Value ' H ' Similarly Another thing you pick up along the way is that the cells ( or strictly speaking the Range objects associated with it ) can be organised into groups of cells which then are also called Range objects and are organised in their constituent parts exactly the same as for the single cell Range object. Once again this is all an indication of organising so that we get at information by sliding along a specific amount ( offset value). The Offset and Resize properties therefore return a new range object. I use the .Value 2 here as i seemed to get it for .Value anyway, not sure why yet, - so i thought be on the safe side , get it always and work somehow with that for now and convert as necerssary. Also 1 breadth Arrays due to Alan Intercept theory are held in such a ways as to be very effient in usage of values within:

Dim arrInNorm() As Variant, arrInOver() As Variant
Let arrInNorm() = FstDtaCel.Offset(0, 8).Resize(lr, 1).Value2 ' I ' Normal Hrs ( Column I ) are needed as they must be set to zero for Holy ?? Holidays ?? Friday ??
Let arrInOver() = FstDtaCel.Offset(0, 9).Resize(lr, 1).Value2 ' J ' Overtime ( Column J ) is needed as it will be changed and then used in calculations
'3c) Inner loop for rows
Dim ShtCnt As Long ' Loop Bound Variable Count for hours columns looping
For ShtCnt = 1 To UBound(arrDte(), 1) Step 1 '------------------- For "rows" in data arrays
'3d) We need to check for a Holy?? Holiday?? Friday?? Adjust columns I and J so that column I has no hours for holiday day and total hours goes to over time hours with criteria 9 or less than 9 hrs all total hours added overtime, 10 or above 10 hrs one hour deducted from total hours and added to column J
Let Dte = arrDte(ShtCnt, 1): Let DteNo = Weekday(Dte, [vbSunday]) ' I do not really nead this extra variable, but for dates I prefer always to do this to help in looking into the variable in Debugging
If DteNo = 6 Then ' 6 I think is Friday, Nelson's Holy HoliDay ?
If (arrTotHrs(ShtCnt, 1) * 24) <= 9 Then '(i) If Total Hrs are less than or equal to 9 ,Then all Total Hrs are added to Overtime Hrs
Let arrInOver(ShtCnt, 1) = arrTotHrs(ShtCnt, 1) ' Given To ' Added to arrInOver(ShtCnt, 1) + arrTotHrs(ShtCnt, 1)
ElseIf (arrTotHrs(ShtCnt, 1) * 24) > 9 Then ' (ii) If Total Hrs are less greater than 9 , Then ( Total Hrs - 1 ) are added to Overtime Hrs
Let arrInOver(ShtCnt, 1) = arrTotHrs(ShtCnt, 1) - 1 / 24 ' Given To ' arrInOver(ShtCnt, 1) + arrTotHrs(ShtCnt, 1) - 1 / 24 'Added to 1 hr less overtime for more than 9 hrs worked
End If
Let arrInNorm(ShtCnt, 1) = Empty ' (iii) As array is variant type can empty Remove normal Hrs Array for(Column I) is then set tom zerow for this "row"o
Else ' No Holy Holiday
End If
'3e)
If arrInNorm(ShtCnt, 1) <> 0 And arrInOver(ShtCnt, 1) <> 0 Then Let NOHrsV2 = NOHrsV2 + arrInOver(ShtCnt, 1) ' Normal Overtime is simply calculated from summing hours in column J only If there are Overtime hours in column J And there are Normal hours are in column I.
If arrInNorm(ShtCnt, 1) = Empty And arrInOver(ShtCnt, 1) <> 0 Then Let HOHrsV2 = HOHrsV2 + arrInOver(ShtCnt, 1) ' Holiday Overtime is simply calculated from summing hours in column J only If there are Overtime hours in column J And there are no Normal hours are in column I.
Next ShtCnt '--------------------------End Inner loop for rows-----
'3f) Paste out final Totals and days to current Worksheet
Let wsStear.Range("G34").Value = NOHrsV2 * 24 'Normal Overtime is held in Array as fraction of a day
Let wsStear.Range("J34").Value = HOHrsV2 * 24 'Holiday Overtime is held in Array as fraction of a day
Let wsStear.Range("C34").Value = TDays ' The constant value of Total days is simply added to cell C34
'3g) Normal Hrs ( Column I ) and Overtime Hrs ( Column J ) are changed
Let FstDtaCel.Offset(0, 9).Resize(lr, 1).Value2 = arrInOver() ' J ' The required spreadsheet cells range has its Range Object .Value2 values filled an allowed direct assignment to an array of values
Let FstDtaCel.Offset(0, 8).Resize(lr, 1).Value2 = arrInNorm() ' I
Next Cnt '==End main Loop==============================================
End Sub


' Rem Ref '_- http://www.excelfox.com/forum/showthread.php/2145-Excel-VBA-Interception-and-Implicit-Intersection-and-VLookUp?p=10062#post10062
'_- http://www.excelfox.com/forum/showthread.php/2138-Understanding-VBA-Range-Object-Properties-and-referring-to-ranges-and-spreadsheet-cells?p=10012#post10012

[DocAElstein]

Second Code for nelson
Post 9
http://www.excelfox.com/forum/showthread.php/2144-Code-Required-to-calculate-number-of-days-worked-normal-overtime-and-holiday-overtime?p=10070#post10070






Sub IJAdjustKAddTotalAllWorksheet() ' http://www.excelfox.com/forum/showthread.php/2144-Code-Required-to-calculate-number-of-days-worked-normal-overtime-and-holiday-overtime?p=10060#post10060
Rem 1) Workbooks Info.
Dim Wb As Workbook ' Dim: For Object variabls: Address location to a "pointer". That has all the actual memory locations (addresses) of the various property values , and it holds all the instructions what / how to change them , should that be wanted later. That helped explain what occurs when passing an Object to a Call ed Fucntion or Sub Routine By Val ue. In such an occurance, VBA actually passes a copy of the pointer. So that has the effect of when you change things like properties on the local variable , then the changes are reflected in changes in the original object. (The copy pointer instructs how to change those values, at the actual address held in that pointer). That would normally be the sort of thing you would expect from passing by Ref erence. But as that copy pointer "dies" after the called routine ends, then any changes to the Addresses of the Object Properties in the local variable will not be reflected in the original pointer. So you cannot actually change the pointer.)
Set Wb = ActiveWorkbook ' Set: Fill or partially Fill: Setting to a Class will involve the use of an extra New at this code line. I will then have an Object referred to as an instance of a Class. At this point I include information on my Pointer Pigeon hole for a distinct distinguishable usage of an Object of the Class. For the case of something such as a Workbook this instancing has already been done, and in addition some values are filled in specific memory locations which are also held as part of the information in the Pigeon Hole Pointer. We will have a different Pointer for each instance. In most excel versions we already have a few instances of Worksheets. Such instances Objects can be further used., - For this a Dim to the class will be necessary, but the New must be omitted at Set. I can assign as many variables that I wish to the same existing instance
Dim wsStear As Worksheet ' Used for each Worksheet counting Tabs from left from 1 To Total
Rem 2) varables for some totals ;)
Const TDays As Long = 30 'Total days just taken as 30 ' Long is very simple to handle, - final memory "size" type is known (123.456 and 000.001 have same "size" computer memory ) , and so a Address suggestion can be given for the next line 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 anyways, so a Long is actually faster. )
Dim Dte As Date, DteNo As Long ' I am hoping Dte will sort out getting a date in a format that I can use the Weekday function to see what week day it is and get that as a nuumber to check for..
Rem 3) Loop through worksheets and give some Totals
Dim Cnt As Long ' Loop Bound variable count for going through all worksheets
'3a) main Loop start============================================= ========
For Cnt = 1 To Wb.Worksheets.Count ' The Worksheets collection Object Property returns the number of worksheet items in the Workbook
Set wsStear = Wb.Worksheets.Item(Cnt) ' At each loop the variable is set to the current Worksheet counting from the Cnt'ths tab from left
Dim lr As Long ' Used for last row number in column E
Let lr = wsStear.Range("E" & Rows.Count & "").End(xlUp).Row ' The Range Object ( cell ) that is the last cell in the column of interest (CHOOSE a column typically that will always have a last Entry in any Data) ,( Row Number given by .Count Property applied to ( any Worksheet would do, so leaving unqualified is OK here, ) Spreadsheet Range Rows Property) has the Property .End ( argument "Looking back up" ) appled to it. This Returns a new Range ( cell ) object which is that of the first Range ( cell ) with something in it "looking back up" in the XL spreadsheet from that last Cell. Then the .Row Property is applied to return a long number equal to the Row number of that cell: Rows.Count is the very last row number in your Worksheet. It is different for earlier versions of Excel. The End(xlUp) is the same as pressing a Ctrl+UpArrow key combination. The final ".Row" returns the row where the cursor stops after moving up.
Dim FstDtaCel As Range: Set FstDtaCel = wsStear.Range("A1") ' Worksheets Range(" ") Property used to return Range object of first cell in second row
'3b) Data arrays from worksheet. We need columns E H I J .... Date ( Column E ) and Total hrs ( Column H ) are required to use in calculations
Dim arrInNorm() As Variant, arrInOver() As Variant ' In the next lines the .Value2 or .value Property is applied a Range object which presents the Value or Value2 value or values in a single variable of appropriate type or a field of member Elements of varaint types.We are expecting the latter, so declare ( Dim ) a dynamic Array variable appropriately. It must be dynamic as its size will be defined at that assignment
Let arrInNorm() = FstDtaCel.Offset(0, 8).Resize(lr, 1).Value2 ' I ' Normal Hrs ( Column I ) are needed as they must be set to zero for Holy ?? Holidays ?? Friday ??
Let arrInOver() = FstDtaCel.Offset(0, 9).Resize(lr, 1).Value2 ' J ' Overtime ( Column J ) is needed as it will be changed and then used in calculations
Dim arrTotHrs() As Variant ' ,' ## ' arrDteClr() As Variant
Let arrTotHrs() = FstDtaCel.Offset(0, 7).Resize(lr, 1).Value ' H ' ' One thing you pick up when learning VBA programming is that referring to cells from one to another via an offset is both fundamental and efficient. That makes sense as Excel is all about using the offsets mentioned above. So like if you use them you can “cut out the middle man”. ( The middle man here might be considered as, for example, in VBA, using extra variables for different Range objects: A fundamental thing to do with any cell ( or strictly speaking the Range object associated to a cell ) is the Range Item Property of any range Object, through which you can “get at” any other Range object. http://www.excelforum.com/showthread.php?t=1154829&page=13&p=4563838&highlight=#post4563838 ( It is often quicker than using a separate variable for each Range object – probably as all the variable does is hold the offset , so you might as well use the offset in the first place.. )
' Similarly Another thing you pick up along the way is that the cells ( or strictly speaking the Range objects associated with it ) can be organised into groups of cells which then are also called Range objects and are organised in their constituent parts exactly the same as for the single cell Range object. Once again this is all an indication of organising so that we get at information by sliding along a specific amount ( offset value). The Offset and Resize properties therefore return a new range object. I use the .Value 2 here as i seemed to get it for .Value anyway, not sure why yet, - so i thought be on the safe side , get it always and work somehow with that for now and convert as necerssary. Also 1 breadth Arrays due to Alan Intercept theory are held in such a ways as to be very effient in usage of values within
Dim arrK() As String 'I know the size, but must make it dynamic as Dim declaration only takes numbers, and so I use ReDim method below wehich can also take variables or formulas
ReDim arrK(1 To UBound(arrInNorm(), 1), 1 To 1) ' Any array first dimension ("row") will do

'This will not work. ' ## ' Let arrDteClr() = FstDtaCel.Offset(0, 4).Resize(lr, 1).Interior.Color ' because .Interior property for a Range object shows only one value for the entire range which seems to be zero unless all the cells have a colour
Dim arrDteClr() As Double, rngDts As Range
Set rngDts = FstDtaCel.Offset(0, 4).Resize(lr, 1)
Dim Rws As Long: ReDim arrDteClr(1 To lr, 1 To 1) ' so must loop in each Interior color value
For Rws = 1 To UBound(arrDteClr(), 1) Step 1 'InnerLoop for dates background colors
Let arrDteClr(Rws, 1) = rngDts.Item(Rws, "A").Interior.Color
Next Rws
'3c) Inner loop for rows
Dim ShtCnt As Long ' Loop Bound Variable Count for hours columns looping
For ShtCnt = 1 To UBound(arrDteClr(), 1) Step 1 '------------------- For "rows" in data arrays
'3d) We need to check Interior color Adjust columns I and J so that column I has no hours for holiday day and total hours goes to over time hours with criteria 9 or less than 9 hrs all total hours added overtime, 10 or above 10 hrs one hour deducted from total hours and added to column J ..... and add a H or N in helper column K
If arrDteClr(ShtCnt, 1) = 65535 Then
If (arrTotHrs(ShtCnt, 1) * 24) <= 9 Then '(i) If Total Hrs are less than or equal to 9 ,Then all Total Hrs are added to Overtime Hrs
Let arrInOver(ShtCnt, 1) = arrTotHrs(ShtCnt, 1) ' Given To ' Added to arrInOver(ShtCnt, 1) + arrTotHrs(ShtCnt, 1)
ElseIf (arrTotHrs(ShtCnt, 1) * 24) > 9 Then ' (ii) If Total Hrs are less greater than 9 , Then ( Total Hrs - 1 ) are added to Overtime Hrs
Let arrInOver(ShtCnt, 1) = arrTotHrs(ShtCnt, 1) - 1 / 24 ' Given To ' arrInOver(ShtCnt, 1) + arrTotHrs(ShtCnt, 1) - 1 / 24 'Added to 1 hr less overtime for more than 9 hrs worked
End If
Let arrInNorm(ShtCnt, 1) = Empty ' (iii) As array is variant type can empty Remove normal Hrs Array for(Column I) is then set tom zerow for this "row"o
Let arrK(ShtCnt, 1) = "H" ' Give string, "" value of H for Holiday in Admin's help column K
Else ' No Holy Holiday
Let arrK(ShtCnt, 1) = "N" ' give string N for normal
End If
'3e) ' from last code, is not now used to calculate totals
Next ShtCnt '--------------------------End Inner loop for rows-----
'3f) Paste out final Totals and days to current Worksheet
Let wsStear.Range("G35").Value = "=SUMIF(K1:K" & lr & ",""N"",J1:J" & lr & ")*24"
Let wsStear.Range("J35").Value = "=SUMIF(K1:K" & lr & ",""H"",J1:J" & lr & ")*24"
Let wsStear.Range("C34").Value = TDays ' The constant value of Total days is simply added to cell C34
'3g) Normal Hrs ( Column I ) and Overtime Hrs ( Column J ) are changed ' And can paste out help column if you like
Let FstDtaCel.Offset(0, 9).Resize(lr, 1).Value2 = arrInOver() ' J ' The required spreadsheet cells range has its Range Object .Value2 values filled an allowed direct assignment to an array of values
Let FstDtaCel.Offset(0, 8).Resize(lr, 1).Value2 = arrInNorm() ' I
Let FstDtaCel.Offset(0, 10).Resize(lr, 1).Value2 = arrK() ' K
Next Cnt '==End main Loop==============================================
End Sub

[DocAElstein]

Post for reference from a few other places, such as
https://eileenslounge.com/viewtopic.php?p=322751#p322751
https://www.excelfox.com/forum/showthread.php/2824/page2


ByRef ByVal and winapi
VBA variable basics
Variables, ByRef ByVal, "pointers" (Addresses)
The idea of this and the over next posts is to just very slightly extend the simplest typical explanation of ByRef and ByVal in VBA. This slightly extended explanation in the next post including an equally very slightly extended explanation of some VBA variables can, IMO, go a long way to help understand other issues later on when learning VBA: It is worth the effort in almost all cases, since the over simplified explanations can make further learning unnecessarily complicated.
The slightly fuller explanations can be particularly helpful in API work, and indeed these postings arise from my attempts to get a better understanding to the issues in this post: https://eileenslounge.com/viewtopic.php?f=30&t=41659
This first post revises the typical beginner explanation

ByRef ByVal
Review of typical explanation VBA ByRef ByVal

This is a typical simple explanation, and I will assume you know this. If you don’t then best learn it before you go any further
The TLDR:
ByRef and ByVal are instructions to VBA. They instruct whether to take the value (ByVal ), of a given variable ( "the original text" of/in the variable StrBuffa in this example ) or whether to refer to the variable, - crudely in this latter case, in Layman terms ByRef instructs to take the variable itself, rather than the value in it..

Simple sample coding

Sub MainRoutine_ByValByRefSimpleVBAFunctionsExample() ' https://www.excelfox.com/forum/showthread.php/2404/page3#post11880
Dim StrBuffa As String
Let StrBuffa = "the original text" '

Call TakeValue(StrBuffa) ' Take the value in StrBuffa into Sub TakeValue(ByVal Tecst As String)
Debug.Print StrBuffa ' : MsgBox StrBuffa
Debug.Print

Call TakeVariable(StrBuffa) ' A bit like Taking the variable, StrBuffa, into Sub TakeVariable(ByRef Tecst As String)
Debug.Print StrBuffa ' : MsgBox StrBuffa
End Sub
Sub TakeValue(ByVal Tecst As String)
Let Tecst = "a new value" ' This only does anything within this routine, so it has no effect, for example, on anything in the main routune
End Sub
Sub TakeVariable(ByRef Tecst As String)
Let Tecst = "a new value" ' This has the effect of changing the value of StrBuffa in ther main routine
End Sub

If you run the first, main routine above, Sub MainRoutine_ByValByRefSimpleVBAFunctions() , then this should be the output:

the original text

a new value

The first output ,
the original text
, received after the first Call, Call TakeValue(StrBuffa) , tells you that you (still) have the original text in a string variable, StrBuffa, which is at the start of the main routing , Sub MainRoutine_ByValByRefSimpleVBAFunctions()
, and the second output ,
a new value
, received after the second Call , Call TakeVariable(StrBuffa) , tells you that you have a new value in that original string variable. In other words in this latter case you changed the value in the original main coding variable, StrBuffa,

Simple layman Explanation
A simple laymen explanation is that in the first Call, Call TakeValue(StrBuffa), I put a value in a local variable, Tecst, in the second routine, Sub TakeValue[( … ). That’s it. There is no way inside that second subroutine to change the value in the original variable StrBuffa , of the main routine, Sub MainRoutine_ByValByRefSimpleVBAFunctions()

In the second Call , Call TakeVariable(StrBuffa) , I am arranging that I am referring to the variable StrBuffa, (from the main routine ), when in the third and final routine , Sub TakeVariable( …. ). So I can, and do, change its value, which is then what the second output tells me. - Inside Sub TakeVariable( …. ), any reference to Tecst is effectively referring to the original variable StrBuffa , from the main routine.
In other words, within the third and final routine , Sub TakeVariable, the variable Tecst can be thought of as not a local variable, but effectively the variable StrBuffa within the main routine, Sub MainRoutine_ByValByRefSimpleVBAFunctions()
Another simple layman way to think of that is that in the third and final routine, Sub TakeVariable( .. ), I take the variable from the main routine, StrBuffa, itself , into the third and final routine , not just the value inside it. In the second routine, Sub TakeValue( ..) , I just take the value.
I realise that all the above is not technically so correct. - Indeed the purpose of this and the next post is to give a slightly more technically correct explanation, but one that is still understandable by a beginner

ByRef and ByVal are instructions to VBA. They instruct whether to take the value (ByVal ), of a given variable ( StrBuffa in this example ) or whether to refer to the variable, - crudely in this latter case, in Layman terms ByRef instructs to take the variable itself.

(In these sort of coding arrangements, we may refer to the main routine as the Calling routine, and the others as the Called routines )


A useful consequence of the above: Use ByRef instead of function return value
This is also worth reviewing , while we are here.
Some of us will know that this latter ByRef way of taking a variable, is a way to effectively get values returned from a routine, or function, since variables like StrBuffa can be thought of as a Buffer ( Buffer = box, variable, place, shelf, pigeon hole etc), to take and hold values assigned within the Called routine.
In other words, it can be thought of as a trick to make a Called routine or Function work as if it was a function that can return more than one value**.

( ** A Function in VBA and other languages is generally regarded as something that can return a single value, we talk of "The function return value", which if x is the "The function return value", then it would typically be seen in coding as x = MyFunction( ….. ) )


Examples in the next post

[DocAElstein]

Use ByRef instead of function return value

Example coding:
In Rem 1 we make a call to two simple conventional Function coding examples . One returns the sum of two given numbers, and the other the difference of the same two numbers.
In Rem 2, we make a call to a function that once again takes in the same two nunbers, but makes no return itself. It additionally takes in ByRef two variables . Effectively then we fill those variables in the function, a coinsequence of effectivley filling the variables passed to the function.

Sub MainRoutine_MakeRoutineWorkLikeAFunctionReturningM ultipleValues()
Dim NSum As Long, NDiff As Long ' https://www.excelfox.com/forum/showthread.php/2404-Notes-tests-ByVal-ByRef-Application-Run-OnTime-Multiple-Variable-Arguments-ByRef-ByVal?p=11881&viewfull=1#post11881

Rem 1 Classic Function use in VBA
Let NSum = SumNumbers(2, 1)
Debug.Print NSum
Let NDiff = NumbersDifference(2, 1)
Debug.Print NDiff
Debug.Print

Let NSum = 0: NDiff = 0 ' Empty variables

Rem 2 Unconventional way in VBA to return more than one value from a function or Sub routine
Call NumbersSumAndDifference(2, 1, NSum, NDiff)
Debug.Print NSum; NDiff

End Sub

Function SumNumbers(ByVal N1 As Long, ByVal N2 As Long) As Long
Let SumNumbers = N1 + N2
End Function
Function NumbersDifference(ByVal N1 As Long, ByVal N2 As Long) As Long
Let NumbersDifference = N1 - N2
End Function

Function NumbersSumAndDifference(ByVal N1 As Long, ByVal N2 As Long, ByRef TheSum As Long, ByRef TheDiff As Long)
Let TheSum = N1 + N2: TheDiff = N1 - N2 ' Effectively the variable TheSum is the variable NSum from the main calling routine, and similarly the variable TheDiff is effectively the variable NDiff from the main calling routine
End Function
, output should be

3
1

3 1


Notes:

_(i) ) In these sort of coding arrangements, we may refer to the main routine as the Calling routine, and the others as the Called routines or Called functions

_(ii)a) In this example, Function MainRoutine_MakeRoutineWorkLikeAFunctionReturningM ultipleValues ( ..….. , as it is written, can be a Function , (as it indeed is), but it can also be a Sub, by simply replacing Function with Sub

_(ii)b) For the function version only, you could also give another return in the conventional way by
_ adding a ..… ) As ..…. at the end of the function signature line in the conventional way
, and
_assigning the function name to something within the function, in the conventional way : Let MainRoutine_MakeRoutineWorkLikeAFunctionReturningM ultipleValues = …… )
Just to clarify this last point, the single called function in the following example is used both in the conventional and unconventional way to return in total 3 values: The same two values as the last coding, and an extra string value,
The two results are: The sum, 3 , and the difference, 1
That extra string value is returned in the conventional way.

Sub MainCallingRoutine()
Dim NSum As Long, NDiff As Long

Debug.Print NumbersSumAndDifference(2, 1, NSum, NDiff)
Debug.Print
Debug.Print NSum; NDiff

End Sub

Function NumbersSumAndDifference(ByVal N1 As Long, ByVal N2 As Long, ByRef TheSum As Long, ByRef TheDiff As Long) As String
Let TheSum = N1 + N2: TheDiff = N1 - N2 ' Effectively the variable TheSum is the variable NSum from the main calling routine, and similarly the variable TheDiff is effectively the variable NDiff from the main calling routine
Let NumbersSumAndDifference = "The two results are: The sum, " & TheSum & " , and the difference, " & TheDiff
End Function
The results are

The two results are: The sum, 3 , and the difference, 1

3 1

3 1 is obtained as in the previous example, and the string of text
, The two results are: The sum, 3 , and the difference, 1
, is returned from the called function in the more conventional way



Some performance characteristics of Using ByRef instead of function return value, (with strings)
As discussed, returning a value, such as a string, as the function return value, is the conventional practice. However, using the unconventional ByRef way to return a string in a ByRef parameter is faster.

It's often considered bad programming practice to return values in parameters. Normally procedures should not cause side-effects by modifying their ByRef parameters. But if performance is very important the ByRef trick is worth considering. If using ByRef for that reason I would perhaps write a ' comment to say so.

There is one case where ByRef is slower than ByVal. This happens when passing ByRef to an out-of-process server. The variable has to be marshalled twice, once going into the method and once returning. The implication is to use ByVal for your public server interfaces.*


(* https://www.aivosto.com/articles/stringopt2.html


In the next post we start again from basics , but assuming you understand this and the last post

[DocAElstein]

Slightly extended explanations
ByVal ByRef, and variables
Part 1 Simple variable Long type

VBA variable examples: Simple and slightly complex variable examples
A program stores the value of variables somewhere in the main memory. Where and how this happens is usually completely irrelevant to us as VBA does all the heavy work for us. Never the less, a slightly more in depth knowledge is generally worth having, IMO, and in particular, if later getting involved with API stuff
In particular I am interested in extending the explanations to the so called "Pointer " things, which in simple terms is concerned with how many computer systems and languages handle addresses/ location indicators of memory locations involved in storing and handling/ retrieving variable values/contents
VBA variable examples: Simple variable example: Long
If you declare a variable of type Long, for example, the system assigns the variable a start address in memory, and from there four bytes, (32 binary Bits) are set aside to hold the number value . This allows a very large whole number range to be represented / stored, and for modern computers, 4 Bytes is a very small insignificant amount, so it is of no concern if the variable holds a large or small number, in other words it is of little concern if you need all the 32 Bits or not
Because VBA does all the heavy work for us, we can easily get mislead into thinking that the long type variable somehow has or holds the value. It never does. When we use the variable, VBA goes off and gets the value, and when presented to us it may appear as if it is there, for example when we hover over it in the VB editor (https://i.postimg.cc/02dpKFbh/Hover-over-Long-variable-in-VB-Editor.jpg), but it isn't. For the Long variable, it is probably more accurate to say it holds the start address…… the start address in memory, from where four bytes, (32 binary Bits) are set aside to hold the number value: This happens even if the variable has not yet been assigned a value; the declaration is sufficient. Just after the declaration, nothing is written into these four bytes and all the bits in them are still set to "0". The situation can be thought of as that it is just noted somewhere: "The value of the variable is stored from memory address 142008724". This "note" idea or concept is called a "pointer". Or saying the same thing slightly differently: a variable used to hold such an address is called a pointer. So the Long variable could be considered a pointer.
Pretty well most variables used in VBA can be considered such a pointer. In simple layman terms it makes sense : It is pointing to somewhere in a way of thinking: - VBA can be thought in running a coding to go there to get the address, to go to – a bit like asking someone where to go and they point you in the right direction, by giving you the address.
Now at this point it is easy to get confused, but with a bit of careful thinking things the confusion should go away: The variable itself, the thing containing the important address also has an address. As mentioned, VBA does all the hard work for us normally making everything seem simpler than it is: "Give it a variable", and it will find, use, and/ or show you the value as appropriate, leading you to think the variable "has" the value. But we have some tools that can be thought of as disabling the automatic VBA processes, and just return us something more raw, Pointer functions
Pointer Functions
There are a few, but we will only consider a couple for now.
VarPtr(variable name) As LongPtr
We can use this in a couple of ways. In the simplest form as shown it will give you the address of the variable you give it. Pseudo like
= get address of(This variable) : It does not tell you what is "in" the variable. It does not give you any value
For the case of a Long it will give you the address of that variable, not the address in memory, from where four bytes, (32 binary Bits) are set aside to hold the number value!
Now, these pointer functions are old functions, they are not only undocumented, but they are even hidden in the object catalogue. They are not VBA functions, so are, probably, external functions of a sort. Here is an important but not well known fact. External function calls are set by default to work in the ByRef way, but you can override this at the call. Depending on how such an old function is implemented/wired into, VBA, that override ability and other original things associated with such an old function may or may not still work. But experience shows the older things are in Microsoft the moiré likely they are to work more usefully. These functions are very old and indeed this override function seems to work. It works in the same way as we might generally expect for the ByVal as so far discussed.
In the coding below, in Rem 1, we use, twice, the VarPtr( ) to get
_a) the address of a long variable, (remember that will be the address of the pointer: The variable holding the address of where in memory the actual final wanted number value will be)
_b) using the ByVal option we can get the "value" at that memory address, but now it gets a bit subtle and complex, or rather it will when we consider a String type. For now, we see that we get the value of zero, since we have not "filled the variable"
In Rem 2 the same is done again after the variable is "filled" with a value

' https://www.excelfox.com/forum/showthread.php/2404-Notes-tests-Application-Run-OnTime-Multiple-Variable-Arguments-ByRef-ByVal?p=11881&viewfull=1#post11881
Sub PoyntersVarialesAddresses() ' https://classicvb.net/tips/varptr/ https://www.vba-tutorial.de/referenz/zeiger.htm
Rem 0 A simple vaiable, Long Type
Dim Lng As Long
Rem 1
Debug.Print "Rem 1"
'1a) ' Address of the Pointer; the actual typical wanted value
Debug.Print "Pointer Address; actual typical wanted value ";
Debug.Print VarPtr(Lng); Lng
'1b) ' The value at that address; the actual typical wanted value"
Debug.Print "Value at address; actual typical wanted value ";
Debug.Print VarPtr(ByVal Lng); Lng
Debug.Print
Let Lng = 2
Rem 2
Debug.Print "Rem 2"
Debug.Print "Pointer Address; the actual typical wanted value ";
Debug.Print VarPtr(Lng); Lng
Debug.Print "Value at address;the actual typical wanted value ";
Debug.Print VarPtr(ByVal Lng); Lng
End Sub

The results are as expected
Rem 1
Pointer Address; actual typical wanted value 2749500 0
Value at address; actual typical wanted value 0 0

Rem 2
Pointer Address; the actual typical wanted value 2749500 2
Value at address;the actual typical wanted value 2 2






For more detailed pointer discussions, see here https://www.excelfox.com/forum/showthread.php/2824/?p=17881&viewfull=1#post17881

[DocAElstein]

Slightly extended explanations
ByVal ByRef, and variables
Part 2 less Simple variable String type
Characters are more complicated for a computer to store, ( https://www.excelfox.com/forum/showthread.php/2824-Tests-Copying-Pasting-API-Cliipboard-issues-and-Rough-notes-on-Advanced-API-stuff?p=17877&viewfull=1#post17877 ) and a lot of text can be extremely long, so desirably we would like this large amount to be available. Currently I think we are talking of sizes around a Giga Bytes or two. But we may not always need this. Unlike in the case of a Long variable type, it would be impractical to routinely set aside a very large amount of memory after/ with a Declaration. More commonly, the address gets assigned once a value is given, ( when the variable gets "filled" ). It is slightly more complicated in the case of VBA as the VBA variable will then contain the address of another pointer which is a specific pointer type, one originating from Visual Basic, sometime referred to as BSTR, (Binary String or Basic String, or a pointer of some sort . Immediately you have opened a can of worms as there is not generical agreement on what a BSTR is). This BSTR (pointer) "goes to" (has the address ("value in it") of), not the start of the memory location as was the case with the Long, but rather to the start of the section containing the representatio0n of the characters of the string. Unlike in the case of the Long, things other than the final value we are interested in , mainly the a length of the string, is also at the memory location.
(Attempting to lift the skirt of VBA and discuss things like VB Strings, and terms such as BSTR can be very dangerous.
I actually put this post, and the rest of this page, on hold and I made some attempt to clarify the situation in a forum Thread, ( https://eileenslounge.com/viewtopic.php?f=30&t=41784 ). A month later I am still not fully clued up on the situation, but am possibly almost as far as anyone has ever got, and can perhaps proceed, with caution, with this post. Complete clarity may require going 20-30 years retro with Microsoft and installing some earlier versions of the Visual Basic that is generally regarded as what most or VBA is based on. I can then cross check some experiments I have done in VBA).


At this point I have left the rest of this page 3, (https://www.excelfox.com/forum/showthread.php/2404 /page3) for some rough notes as I move on finally onto the next page 4 (https://www.excelfox.com/forum/showthread.php/2404 /page4)


In particular, at this stage it is worth a look at some notes on Pointers, which I have put in a Thread considering API things, since pointers, in particular with string variables, are an important issue in API calls in VB(A)

Long
https://www.excelfox.com/forum/showthread.php/2824-Tests-Copying-Pasting-API-Cliipboard-issues-and-Rough-notes-on-Advanced-API-stuff?p=17881&viewfull=1#post17881
https://www.excelfox.com/forum/showthread.php/2824-Tests-Copying-Pasting-API-Cliipboard-issues-and-Rough-notes-on-Advanced-API-stuff/page2#post17881

String
https://www.excelfox.com/forum/showthread.php/2824-Tests-Copying-Pasting-API-Cliipboard-issues-and-Rough-notes-on-Advanced-API-stuff?p=24948&viewfull=1#post24948
https://www.excelfox.com/forum/showthread.php/2824-Tests-Copying-Pasting-API-Cliipboard-issues-and-Rough-notes-on-Advanced-API-stuff/page2#post24948

[DocAElstein]

https://i.postimg.cc/BnDm793c/Area-likely-to-change-on-string-value-assignment.jpg (https://postimages.org/)

https://i.postimg.cc/8Pxybk2z/Let-str-Ob-A.jpg (https://postimg.cc/VdBBfwsx)

[DocAElstein]

lvödsvdljv

[DocAElstein]

Taking a time out here to ask a question or two or 11 over at Eileen's Lounge
https://eileenslounge.com/viewtopic.php?p=323893#p323893
https://eileenslounge.com/viewtopic.php?p=323894#p323894

Here is the two post in one…….




Hi
I have been going around in circles on this one for a few days, and I am still not sure if I have quite got it right.
Can someone help put me out of my misery by confirming some things or otherwise….

First, I think I do understand approximately how a VBA Long type variable and a VBA String type variable are handled in memory, and I think I understand the important differences in those two, ( and why) , in the way they are handled and stored. My confusion is related to exactly which memory addresses are being referred to in the following coding examples

Long example

Sub ConfusedWithVBALongTypeMemoryStuff()
1 Dim Lng As Long
2 Debug.Print VarPtr(Lng) ' 2355700 I don't know where this number is held in memory. I don't care
3 Debug.Print VarPtr(ByVal Lng) ' 0
4 Debug.Print Lng ' 0

Let Lng = 2

5 Debug.Print VarPtr(Lng) ' 2355700
6 Debug.Print VarPtr(ByVal Lng) ' 2

8 Debug.Print Lng ' 2
End Sub

In the first line of the coding above, Dim Lng As Long , Lng becomes a number which is made with 4 bytes, (32 bits). It is called a Pointer. The actual number refers to the first memory address ( the first memory address at the left hand side) of 4 Bytes (32 bits) set aside in memory to hold the final value which I later assign to the variable, (with Let Lng = 2 ). In other words, the act of doing Dim Lng As Long reserves for me 4 sequential memory addresses/ byte locations, in a sequential row as it were. And the first one, the first byte, at the left as it were, has the memory location got for me with the second code line, Debug.Print VarPtr(Lng) (by me , I got the value shown in the ' comment - 2355700** )
Q1: Have I got that right?

The third line, Debug.Print VarPtr(ByVal Lng) , is perhaps giving me the same as the 4th line, which is the value of the variable. For the case of a Long Type it does have a value even before I assign one. It has the value 0. If I assigned it 0 with Let Lng = 0 nothing would change anywhere
Q2: Have I got that right?

In line 5 , the memory location has not changed, as it never does for the VBA Long type, even if I assign a much different number. This is because It doesn’t need to change, because 32 bits are enough to hold in binary any number in the range of the defined number range for a VBA Long type
Q3: Have I got that right?

Line 6 aqnd line 8: My results suggest that Debug.Print VarPtr(ByVal Lng) and Debug.Print Lng are giving me the same thing – the value it sees at the address, 2355700**.
Q4: Any comments at all on that?
( **The address I got was 2355700 , you will get a similar but different number. I am OK with that and understand why that is )

This next bit is what has got me a bit confused. The 32 Bit number, the number returned from the second code line, Debug.Print VarPtr(Lng) , (by me 2355700** ) , is presumably held somewhere in memory as well? Where I don't know (I am not complaining, I just am trying to confirms that I got that right). I know the value held there, (by me 2355700** ) , but I don’t know where that number is being held
Q5: Have I got that right? (I expect VarPtr( ) knows that memory location as it goes there and gets the number in it for me. ( Maybe there is a "stack" of active variables and their values somewhere we ain't privy to?)

Q6: In this situation what is actually the "Pointer". Is it
_ the number the second code line, Debug.Print VarPtr(Lng) , gets me,
_ is it the variable Lng ,
_ is it the Bytes used for it somewhere I don't know?
Or is the word "pointer" some vague concept you would use to refer to one or more of those things or all of them depending on the context in which you use it


I have some similar questions on the VBA String type. It will perhaps help tidiness and later reference if I do another post for that…..



Ref: VarPtr , StrPtr stuff https://classicvb.net/tips/varptr/ , https://www.vba-tutorial.de/referenz/zeiger.htm

https://www.excelfox.com/forum/showthread.php/2404/page3#post11886








Unicorn or Unicode Encoding – A beast by any name

…….. continued, sort of, from last post


…. Confused with VBA and VB String Pointer Stuff

First, I do understand
_ (i) that String memory handling generally in computing, is a bit more dynamic/ complex than something like for a simple number, since even with modern computers, we don’t want to go around reserving a Giga Byte or two of memory every time we Declare a string variable.
and
_(ii) thanks to a lot of research and help from here, I am pretty well clued up now on all the various Ascii Ansi, Unicorns and other character encoding beasts roaming about ..

Once again my main confusion just now is/ was to do with what memory addresses / pointers are referring to what / where etc…

So, bearing that in mind…..
and with reference to the coding below….

After a month on and off researching VB String stuff, and VBA – VB API stuff, I was not too sure anymore that I even understood the very first line, Dim Str As String, … but as is often the case, preparing carefully the question can often help get the answer… The second code line Debug.Print VarPtr(Str) actually returned me the very same number as the second code line in the coding from the last post, Debug.Print VarPtr(Lng)!! I do realise that will not always be the case, but as I ran the coding shortly after running the previous coding on the same computer, then there is a chance it will be the same, as it was, … because….. how about this: What Dim Str As String in the coding below is doing, is very similar to what the Dim Lng As Long did/does in the coduing from the last post, - it sets aside once again 4 Bytes (32 bits) for me. But the difference being now is that it is not reserving me a place to store any final number or character values, rather it is reserving me a place … for …. a 32Bit Pointer , specifically a pointer to a VB String, (what I might refer to as a COM BSTR), and if I am feeling very adventurous I might say it’s a LPWSTR pointer, (or variation of) , which means that when it eventually gets filled in, it will be the address not of the start memory point I finally use for my string, but rather 4 Bytes along where the actual Unicode UTF-16 LE encoding Bytes start.
Q7. Have I got that right?

So I could say, what I have is a VBA Pointer to a VB Pointer, or perhaps a VBA Pointer to what likely will be a VB Pointer – depending on how you feel the word pointer should be used
Q8. Have I got that right?

The result 0 of code line 3 and code lines 4 are, I think, telling me the bit I think I know, -they are telling me that, unlike in the case of the Long I have not yet reserved any memory for my final character string.
Q9a) Have I got that right?

Q9b) This is a tricky one that might get me hate mail. I am sure many people would tell me that at this point my Str variable is a vbNullString. I don’t see that. I think I have nothing to do with a VB string at this point? All I have is an empty VBA 32 bit pointer, possibly even indistinguishable from a Long variable of value 0
Any comments on that?

Q9c) I am not too clear what the difference is in these 2(3) code lines.
VarPtr(ByVal Str)
StrPtr(ByVal Str) ; StrPtr(Str)
It is discussed at the first of the references below, but I cannot quite understand what they are on about. Apparently the StrPtr( ) was introduced to make sure you go to the actual Unicode UTF-16 LE encoding Bytes start bit. (the 5th byte along ). But VarPtr(ByVal Str) seems to be doing this. Is this maybe VBA being a bit cleverer with the VarPtr( ) than VB is / was?

Q10 This is similar to Q5. The 32 bit address value/ number of the variable Str (by me 2355700** ) , is itself somewhere in memory. I don’t know where, that is to say I do not know the memory location holding that number . I don't particularly care. I am just wanting that confirmed.
Is that correct?

I think the rest of the coding makes sense to me. The VBA Pointer address generally does not change, (code line 5), the other 2(3) code lines 6 and 7 all do the same thing ( differences in what / how is Q9c). Although the 3 values are always the same value, that value will likely change every time you run the coding. I am OK with understanding what is going on there

( As for code line 8, that, and related VB API stuff, is the subject of another Thread I want to get back to, but I got a bit stuck on the issues I raised in this Thread, so later on that one. - Just passing interest, a taste of interesting things to come as it were, …. what we have in code line 8 is VBA knowing what's going on. VBA does a lot of heavy stuff for us. APIs in VBA on the other hand, can get a bit confused, … the poor old things can even get confused with VB strings more than half the time…. But we will soon have all that sorted as well, :) , :wink: :grin: :cool: )

String example coding

Sub ConfusedWithVBAandVBStringPointerStuff() ' https://www.excelfox.com/forum/showthread.php/2404/page3#post11886
1 Dim Str As String
2 Debug.Print VarPtr(Str) ' 2355700 I don't know where this number is held in memory. I don't care
3 Debug.Print VarPtr(ByVal Str) ' 0
4 Debug.Print StrPtr(ByVal Str); StrPtr(Str) ' 0 0

Let Str = "ABCD"
5 Debug.Print VarPtr(Str) ' 2355700 - makes sense - no reason for this address to change. It is the first Byte of the 4 bytes that holds the VB pointer/ address, whatever value that is
6 Debug.Print VarPtr(ByVal Str) ' 4028444
7 Debug.Print StrPtr(ByVal Str); StrPtr(Str) ' 4028444 4028444

8 Debug.Print Str
End Sub
Q 11. Is the following beautiful sketch an accurate technical depiction of the situation just before the above coding ends?
In that sketch I have enclosed 3 memory chunks, and I have, sort of , connected them with arrow/pointers:
, starting form the bottom there are two memory chunks of 4 Bytes.
The bottom I know what is in it but not where it is.
The middle one I know where it is and what is in it
The top enclosed memory chunk in this example is 14 bytes, ( it would be 18 Bytes if I had ABCDEF, and so on ).


https://i.postimg.cc/L6zbgmFC/A-VBA-Unicorn-a-beast-by-any-name.jpg
https://i.postimg.cc/zVGj9ZBK/A-VBA-Unicorn-a-beast-by-any-name.jpg (https://postimg.cc/zVGj9ZBK)

Thanks for any help, comments , confirmations etc
Alan

Ref: VarPtr , StrPtr stuff
https://classicvb.net/tips/varptr/
https://www.vba-tutorial.de/referenz/zeiger.htm

[DocAElstein]

sdmbsmbf

[DocAElstein]

Buffer Post COFFee table ideas


This post reserved probably for Pallet stacking Ideas and Trendy COFFee tables made with norm dimensioned/size wooden Pallets


Arising from this

By definition a VBA Long is 32bits, So ... yes. But that's not why the memory location has not changed. I am not going to get into the Common Object File Format (COFF) and symbol tables here, but that provides you some nice new keywords to Google ... (but be warned, you are moving very much away from VBA here). This, by the way, is essentially the "... 'stack' of active variables and their values somewhere we ain't privy to?"

>_ the number the second code line, Debug.Print VarPtr(Lng) , gets me,
>_ is it the variable Lng

Lng is the symbol for the pointer,
VarPtr(Lng) gets you the value of the pointer, which is the memory address the pointer is pointing to
Lng itself is stored in the COFF symbol table

Note that one of the whole points of high-level languages is to (try to) hide this stuff away from you.




Snowy pisc, from this time of the year. - Impracticle conditions for Stacks of Coffee tables Ideas

6150
https://i.postimg.cc/90WBwmMH/Beutiful-but-difficult-to-do-Palllet-Stacking-experiments.jpg
https://i.postimg.cc/90WBwmMH/Beutiful-but-difficult-to-do-Palllet-Stacking-experiments.jpg (https://postimg.cc/wyGNSdLw)


Earlier Pics

615161526149

https://i.postimg.cc/3JzKKdN6/Pooh-Bahnhof-im-Bau.jpg (https://postimg.cc/F7gtpFjg)

https://i.postimg.cc/Zq8Ys4TQ/Pooh-Bahn-Hof-im-Bau.jpg (https://postimg.cc/Y4SwhcLN)


Team work
https://i.postimg.cc/m2mmMHCX/Stacking-Ideas.jpg (https://postimg.cc/2VBdN3Hh)




https://i.postimg.cc/7hySGvsp/Skematic-VBA-Long.jpg (https://postimages.org/)






https://i.postimg.cc/VLmjz8bt/Skematic-VBA-String.jpg (https://postimages.org/)

[DocAElstein]

sölcjslkjcslkjc



https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg.9hI1CQJMLLo9hWn2pGBe SS (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg.9hI1CQJMLLo9hWn2pGBe SS)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzkRujoMw9PblmXDQ14AaABAg.9hJRnEjxQrd9hJoCjomN I2 (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzkRujoMw9PblmXDQ14AaABAg.9hJRnEjxQrd9hJoCjomN I2)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzPZbG7OvUkh35nXDd4AaABAg.9hJOZEEZa6p9hJqLC7El-w (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzPZbG7OvUkh35nXDd4AaABAg.9hJOZEEZa6p9hJqLC7El-w)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwUcEpm8u6ZW3uOHXx4AaABAg.9hIlxxGY7t49hJsB2PWx C4 (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwUcEpm8u6ZW3uOHXx4AaABAg.9hIlxxGY7t49hJsB2PWx C4)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyvDj6NWT1Gxyy2JyR4AaABAg.9hIKlNPeqDn9hJskm92n p6 (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyvDj6NWT1Gxyy2JyR4AaABAg.9hIKlNPeqDn9hJskm92n p6)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugwy7qx_kG9iUmMVO_F4AaABAg.9hI2IGUdmTW9hJuyaQaw qx (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugwy7qx_kG9iUmMVO_F4AaABAg.9hI2IGUdmTW9hJuyaQaw qx)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg.9hI1CQJMLLo9hJwTB9Jl ob (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg.9hI1CQJMLLo9hJwTB9Jl ob)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyyQWYVP1OnCqavb-x4AaABAg (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyyQWYVP1OnCqavb-x4AaABAg)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwJKKmExZ1FdZVDJf54AaABAg (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwJKKmExZ1FdZVDJf54AaABAg)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugz_p0kVGrLntPtYzCt4AaABAg (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugz_p0kVGrLntPtYzCt4AaABAg)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
http://www.eileenslounge.com/viewtopic.php?f=30&t=41784 (http://www.eileenslounge.com/viewtopic.php?f=30&t=41784)
http://www.eileenslounge.com/viewtopic.php?p=323966#p323966 (http://www.eileenslounge.com/viewtopic.php?p=323966#p323966)
http://www.eileenslounge.com/viewtopic.php?p=323959#p323959 (http://www.eileenslounge.com/viewtopic.php?p=323959#p323959)
http://www.eileenslounge.com/viewtopic.php?p=323960#p323960 (http://www.eileenslounge.com/viewtopic.php?p=323960#p323960)
http://www.eileenslounge.com/viewtopic.php?p=323894#p323894 (http://www.eileenslounge.com/viewtopic.php?p=323894#p323894)
http://www.eileenslounge.com/viewtopic.php?p=323843#p323843 (http://www.eileenslounge.com/viewtopic.php?p=323843#p323843)
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa6BSa17 3Z (https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa6BSa17 3Z)
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa6-64Xpgl (https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa6-64Xpgl)
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa5ms39y jd (https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa5ms39y jd)
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa5ZXJwR CM (https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa5ZXJwR CM)
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa4Pr15N Ut (https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa4Pr15N Ut)
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa4I83Je lY (https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABa4I83Je lY)
https://www.youtube.com/watch?v=C43btudYyzA&lc=Ugyf349Ue6_4umFfNUB4AaABAg.8mjgPNoTt_HABa3tnAjh ZU (https://www.youtube.com/watch?v=C43btudYyzA&lc=Ugyf349Ue6_4umFfNUB4AaABAg.8mjgPNoTt_HABa3tnAjh ZU)
https://www.youtube.com/watch?v=C43btudYyzA&lc=Ugyf349Ue6_4umFfNUB4AaABAg.8mjgPNoTt_HABa3KswxL 3c (https://www.youtube.com/watch?v=C43btudYyzA&lc=Ugyf349Ue6_4umFfNUB4AaABAg.8mjgPNoTt_HABa3KswxL 3c)
https://www.youtube.com/watch?v=suUqEo3QWus&lc=UgyBXFxnVWT3pqtdqPx4AaABAg (https://www.youtube.com/watch?v=suUqEo3QWus&lc=UgyBXFxnVWT3pqtdqPx4AaABAg)
https://www.youtube.com/watch?v=suUqEo3QWus&lc=Ugi53h84LUm5bHgCoAEC.7-H0Z7-COoGABZFQ8vjEvY (https://www.youtube.com/watch?v=suUqEo3QWus&lc=Ugi53h84LUm5bHgCoAEC.7-H0Z7-COoGABZFQ8vjEvY)
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABZ8N9O-O8p (https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABZ8N9O-O8p)
http://www.eileenslounge.com/viewtopic.php?p=323547#p323547 (http://www.eileenslounge.com/viewtopic.php?p=323547#p323547)
http://www.eileenslounge.com/viewtopic.php?p=323516#p323516 (http://www.eileenslounge.com/viewtopic.php?p=323516#p323516)
http://www.eileenslounge.com/viewtopic.php?p=323517#p323517 (http://www.eileenslounge.com/viewtopic.php?p=323517#p323517)
http://www.eileenslounge.com/viewtopic.php?p=323449#p323449 (http://www.eileenslounge.com/viewtopic.php?p=323449#p323449)
http://www.eileenslounge.com/viewtopic.php?p=323226#p323226 (http://www.eileenslounge.com/viewtopic.php?p=323226#p323226)
http://www.eileenslounge.com/viewtopic.php?f=25&t=41702&p=323150#p323150 (http://www.eileenslounge.com/viewtopic.php?f=25&t=41702&p=323150#p323150)
http://www.eileenslounge.com/viewtopic.php?p=323085#p323085 (http://www.eileenslounge.com/viewtopic.php?p=323085#p323085)
http://www.eileenslounge.com/viewtopic.php?p=322955#p322955 (http://www.eileenslounge.com/viewtopic.php?p=322955#p322955)
http://www.eileenslounge.com/viewtopic.php?f=30&t=41659 (http://www.eileenslounge.com/viewtopic.php?f=30&t=41659)
https://www.youtube.com/watch?v=suUqEo3QWus&lc=Ugi53h84LUm5bHgCoAEC.7-H0Z7-COoGABZFQ8vjEvY (https://www.youtube.com/watch?v=suUqEo3QWus&lc=Ugi53h84LUm5bHgCoAEC.7-H0Z7-COoGABZFQ8vjEvY)
https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABZ8N9O-O8p (https://www.youtube.com/watch?v=3t8Mk4URi6g&lc=UgzoakhRXOsCaoRm_Nd4AaABAg.8xzeMdC8IOGABZ8N9O-O8p)
https://www.youtube.com/watch?v=C43btudYyzA&lc=UgxREWxgx2z2Lza_0st4AaABAg (https://www.youtube.com/watch?v=C43btudYyzA&lc=UgxREWxgx2z2Lza_0st4AaABAg)
https://www.youtube.com/watch?v=C43btudYyzA&lc=UgyikSWvlxbWS24NBeR4AaABAg (https://www.youtube.com/watch?v=C43btudYyzA&lc=UgyikSWvlxbWS24NBeR4AaABAg)
https://www.youtube.com/watch?v=C43btudYyzA&lc=UgwNiH4hhyrd2UjDK8d4AaABAg (https://www.youtube.com/watch?v=C43btudYyzA&lc=UgwNiH4hhyrd2UjDK8d4AaABAg)
https://www.youtube.com/watch?v=C43btudYyzA&lc=Ugyf349Ue6_4umFfNUB4AaABAg.8mjgPNoTt_HAAf952WoU ti (https://www.youtube.com/watch?v=C43btudYyzA&lc=Ugyf349Ue6_4umFfNUB4AaABAg.8mjgPNoTt_HAAf952WoU ti)
https://www.youtube.com/watch?v=hz4vb48wzMM&lc=Ugy2N3gvXBNrvWpojqR4AaABAg (https://www.youtube.com/watch?v=hz4vb48wzMM&lc=Ugy2N3gvXBNrvWpojqR4AaABAg)
http://www.eileenslounge.com/viewtopic.php?p=322462#p322462 (http://www.eileenslounge.com/viewtopic.php?p=322462#p322462)
http://www.eileenslounge.com/viewtopic.php?p=322356#p322356 (http://www.eileenslounge.com/viewtopic.php?p=322356#p322356)
http://www.eileenslounge.com/viewtopic.php?p=321984#p321984 (http://www.eileenslounge.com/viewtopic.php?p=321984#p321984)
https://eileenslounge.com/viewtopic.php?f=30&t=41610 (https://eileenslounge.com/viewtopic.php?f=30&t=41610)
https://eileenslounge.com/viewtopic.php?p=322176#p322176 (https://eileenslounge.com/viewtopic.php?p=322176#p322176)
https://eileenslounge.com/viewtopic.php?p=322238#p322238 (https://eileenslounge.com/viewtopic.php?p=322238#p322238)
https://eileenslounge.com/viewtopic.php?p=322270#p322270 (https://eileenslounge.com/viewtopic.php?p=322270#p322270)
https://eileenslounge.com/viewtopic.php?p=322300#p322300 (https://eileenslounge.com/viewtopic.php?p=322300#p322300)
http://www.eileenslounge.com/viewtopic.php?p=322150#p322150 (http://www.eileenslounge.com/viewtopic.php?p=322150#p322150)
http://www.eileenslounge.com/viewtopic.php?p=322111#p322111 (http://www.eileenslounge.com/viewtopic.php?p=322111#p322111)
http://www.eileenslounge.com/viewtopic.php?p=322086#p322086 (http://www.eileenslounge.com/viewtopic.php?p=322086#p322086)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[DocAElstein]

Slightly extended explanations
ByVal ByRef, and variables
Part 1 Simple variable Long type]
For more detailed background, See also here
https://www.excelfox.com/forum/showthread.php/2404/page3#post11882
https://www.excelfox.com/forum/showthread.php/2824/?p=17881&viewfull=1#post17881
We give here the simple explanation for ByRef / ByVal issues in VBA. but slightly different in that we are less concerned in considering resulting variable values, but rather the values of all pointer associated with the variables concerned in the example coding
Consider the 12 labelled code lines, those with the code line numbers) associated with the following coding. Those lines are done sequentially when we run the first main coding, Sub ByValByRefWithPointersLong()
(If anything is unclear about how the pointer values discussed are obtained, you should refer to the two references above )

Sub ByValByRefWithPointersLong()
1 Dim Lng As Long
2 Debug.Print VarPtr(Lng), VarPtr(ByVal Lng) ' ' 1831204 0
3 Call ByRefLong(Lng) ' Do a ByRef Call --#
7 Debug.Print VarPtr(Lng), VarPtr(ByVal Lng) ' 1831204 2

8 Let Lng = 0

9 Call ByValLong(Lng) ' Do a ByVal Call --**
13 Debug.Print VarPtr(Lng), VarPtr(ByVal Lng) ' 1831204 0
End Sub
Sub ByRefLong(ByRef Lnge As Long) ' #-- Do a ByRef Call
4 Debug.Print VarPtr(Lnge), VarPtr(ByVal Lnge) ' 1831204 0
5 Let Lnge = 2
6 Debug.Print VarPtr(Lnge), VarPtr(ByVal Lnge) ' 1831204 2
End Sub
Sub ByValLong(ByVal Lnge As Long) ' **-- Do a ByVal Call
10 Debug.Print VarPtr(Lnge), VarPtr(ByVal Lnge) ' 1831192 0
11 Let Lnge = 2
12 Debug.Print VarPtr(Lnge), VarPtr(ByVal Lnge) ' 1831192 2
End Sub
Code line 1: Lng is strictly speaking the symbol for the pointer held in a stack of active variable that we don’t have easy access to, going by the name of Common Object File Format ( COFF ) symbol table, and could perhaps be by a layman be regarded as some sort of stack or shelf arrangement populated by some complex rules allowing software to access as necessary. Our interest starts at what is "in" this, in other words the number held at this stack/shelf location . It is a number which is made with 4 bytes, (32 bits). It, or the mechanisms associated with it, is/are called a Pointer, often.
The actual number refers to the first memory address ( the first memory address at the left hand side) of 4 Bytes (32 bits) set aside in memory to hold the final value which I later assign to the variable. In other words, where the first 0/1 value Bit is in the binary representation of a number is. This address is obtained by the first half of code line 2
I use for this explanation the numbers I got (https://i.postimg.cc/rsQfsCZ4/My-Debug-Print-Results.jpg), you will of course get similar sized but different numbers. – Your computer will find an appropriate suitable place at the time you run the coding
This next sketch is a pictorial representation of that number in that mysterious shelf/stack (COFF symbol Table)
https://i.postimg.cc/RCXmK1wN/First-COFF-symbol-table-value.jpg
https://i.postimg.cc/0QwkZTfZ/First-COFF-symbol-table-value.jpg (https://postimg.cc/3W4zHvST)
We cannot easily know "where that sketch is".
The second value at line 2 tells us what number is held at that address. At this stage it will be 0 as the memory location and following 31 bits all have the value 0, representing the final decimal value of 0. At this stage those 32 bits have just been set aside for us to use.
This next sketch is similar to the last, as it is representing a similar sized memory location, ( 32 consecutive bits ). But this is a different "”place" / memory location , the one being "pointed to" by that number, 1831204 , in that shelf/stack (COFF symbol Table) . This place is being used currently to represent a value of zero, which it will be set to by default when it is set aside for us to use. We know "where this is". It starts at memory location 1831204, and ends 31 bits (4 bytes), further along at 1831204 + 3 = 1831207
https://i.postimg.cc/J0mxTCLL/Memeoy-address-from-1831204-holding-value-0.jpg
https://i.postimg.cc/J0mxTCLL/Memeoy-address-from-1831204-holding-value-0.jpg (https://postimg.cc/wyfDvPMf)

Code Line 4 in the first Called procedure returns the same values as code line 2. This is because the code lines are the same, and, importantly for our discussions, they are referring to the same variable, the same "place" depicted above. This is arranged by the use of ByRef in the Called Sub ByRefLong(ByRef Lnge As Long) We could think in simple layman terms, that in the first main procedure, Sub ByValByRefWithPointersLong() and the first Called procedure , the variables Lng and Lnge are in effect the same variable.
Code line 5 in the first Called procedure, then changes the value in the 32 bits there to a binary set of 0/1 digits representing the decimal value of 2. So not much has changed there: This is what it looks like now:
https://i.postimg.cc/6q00gbWG/Memeoy-address-from-1831204-holding-value-2.jpg
https://i.postimg.cc/6q00gbWG/Memeoy-address-from-1831204-holding-value-2.jpg (https://postimg.cc/zHyKRjRJ)

Just to make that all clear again, the story so far:
VarPtr(Lng) has returned us the number held in some place we do not have easy access to. That number , 1831204 , is the memory address of the first bit on the left of 32 bits reserved for us to hold the actual final value that we want to "have in the variable"
VarPtr(ByVal Lng) gives us the decimal value held in those 32 bits at any time. Originally it was set by default to zero, and at some point we changed it to 2

As we now move on, back in the main procedure. at code line 7, the two values returned remain at the values obtained / last set, within the first Called procedure: These are now the values as they stand in the main procedure: the address location of our original variable will never change as long as the coding runs, but we changed the value effectively at that address within the first Called procedure, - we were able to do that change to the value of 2 as effectively, in simple terms, Lng and Lnge were the same variable.

(Code line 8 takes the value of our variable,( that is to say the value represented by the 32 bits starting at memory location 1831204), back to 0 so that we can now repeat the same experiment using the second Called procedure, Sub ByValLong(ByVal Lnge As Long) )

Code line 10 tells us we "have a new variable” , with the name Lnge , which might sometimes be referred to as a local variable, - in this case it is local to the second Called procedure: The use of ByVal in Sub ByValLong(ByVal Lnge As Long) means that we take a value of, in this example 0, and , as in any Long value, we know it must be held somewhere. It cannot be held in 32 bits starting from memory location 1831204, since we are already using this for Lng in the main procedure. The computer has arranged another address for us, and as expected. It is not too far away as I was doing not much else on my computer that might need such a memory space. It has given me 1831192 and the value represented in the 32 bits starting there I currently have the decimal value of 0
Code line 11 sets the decimal value there to 2, as confirmed by code line 12

Finally we are back in our main procedure and do a last check with code line 13 of the situation to our original variable Lng. It has not been effected in any way by the second Called procedure, and remains in the state we left it in, when we went to the second
Called procedure. (Line 8 set it to 0 )



The next sketch shows the situation as it would be in at typical situation in a coding with a long variable declared, for example after
Dim Lng As Long
Let Lng = 2
https://i.postimg.cc/rpVP5Dm5/Final-Long-variable-case-with-value-2.jpg
https://i.postimg.cc/zDjP7FJt/Final-Long-variable-case-with-value-2.jpg (https://postimg.cc/BtjgQDPH)




For comparison, here is the situation just before our coding ends, where we have our variable, but have "cleared" it so to speak, with = 0 , that is to say got it down to its "minimum" computer storage.
https://i.postimg.cc/G2DbWSp1/Final-or-initial-after-Dim-of-Long-variable-case-with-value-0.jpg
https://i.postimg.cc/G2DbWSp1/Final-or-initial-after-Dim-of-Long-variable-case-with-value-0.jpg (https://postimg.cc/LYHdfDfy)

We note finally that for the case of a Long type, the computer storage used is identical, as the sketches perhaps suggest. In other words, from the computers point of view, an assignment of Let Lng = x, ( where x could be a small or a very large number ) , makes no significant difference to its memory state, and no difference at all to its memory allocation


_.___

One last short interesting experiment. It may not always work for you, but if you run the following short coding immediately after running the last coding, then it may work. We note that in the last experiments I mostly got a result of1831204 for the memory location of the start of the 32 bits that were made available to hold our Long variable number. The exception was when I ran Sub ByValLong(ByVal Lnge As Long) , and we got a different number, 1831192
We noted that this may have been because the number 1831204 was in use already. The following small coding only Calls that Sub ByValLong(ByVal Lnge As Long)
You may often find as I did , that it returns the same memory location as first obtained and mostly obtained in the last coding. This goes half way to supporting our ideas, as this makes sense as we do not make any attempt to get such a number/memory location reserved, until in the Sub ByValLong(ByVal Lnge As Long)

Sub OnlyByValCall()
Call ByValLong(0)
End Sub
' 1831204 0
' 1831204 2

[DocAElstein]

Extended explanations
ByVal ByRef, and variables
Part 2 less Simple variable String type
Characters are more complicated for a computer to store, ( https://www.excelfox.com/forum/showthread.php/2824-Tests-Copying-Pasting-API-Cliipboard-issues-and-Rough-notes-on-Advanced-API-stuff?p=17877&viewfull=1#post17877 ) and a lot of text can be extremely long, so desirably we would like this large amount to be available. Currently I think we are talking of sizes around a Giga Bytes or two. But we may not always need this. Unlike in the case of a Long variable type, it would be impractical to routinely set aside a very large amount of memory after/ with a Declaration. More commonly, the address gets assigned once a value is given, ( when the variable gets "filled" ). It is slightly more complicated in the case of VBA as the VBA variable will then contain the address of another pointer which is a specific pointer type, one originating from Visual Basic, sometime referred to as BSTR, (Binary String or Basic String, or a pointer of some sort . Immediately you have opened a can of worms as there is not generical agreement on what a BSTR is). This BSTR (pointer) "goes to" (has the address ("value in it") of), not the start of the memory location as was the case with the Long, but rather to the start of the section containing the representatio0n of the characters of the string. Unlike in the case of the Long, things other than the final value we are interested in ,
_ the a length of the string, at the "left" as it were
and
_ a zero is added at the end

The test coding below used in my explanations is very similar and gives similar results to the previous for the Long case, but there are some subtle things going on that are different: It is very wise therefore to try to understand the Long case from the last post before moving on to here

StrPtr
This is as mysterious as the VarPtr, and was introduced around d the VB4 - VB5 time apparently wired a bit differently to the VarPtr(ByVal ) so as to make sure it went to the correct place, which may somehow help make UNICODE API calls more efficient, since in that use there may be more difficulty in getting the VarPtr(ByVal ) to work as we wish. We expect it to work as the VarPtr(ByVal ) in our experiments


Sub ByValByRefWithPointersString() ' https://www.excelfox.com/forum/showthread.php/2404-Notes-tests-ByVal-ByRef-Application-Run-OnTime-Multiple-Variable-Arguments-ByRef-ByVal?p=11889&viewfull=1#post11889
1 Dim strOb As String
2 Debug.Print VarPtr(strOb), VarPtr(ByVal strOb), StrPtr(strOb), StrPtr(ByVal strOb) ' 1831204 0 0 0
3 Call ByRefString(strOb) ' Do a ByRef Call --#
7 Debug.Print VarPtr(strOb), VarPtr(ByVal strOb), StrPtr(strOb), StrPtr(ByVal strOb) ' 1831204 86412692 86412692 86412692

8 Let strOb = vbNullString

9 Call ByValString(strOb) ' Do a ByVal Call --**
13 Debug.Print VarPtr(strOb), VarPtr(ByVal strOb), StrPtr(strOb), StrPtr(ByVal strOb) ' 1831204 0 0 0
End Sub
Sub ByRefString(ByRef strObs As String) ' #-- Do a ByRef Call
4 Debug.Print VarPtr(strObs), VarPtr(ByVal strObs), StrPtr(strObs), StrPtr(ByVal strObs) ' 1831204 0 0 0 0 0
5 Let strObs = ""
6 Debug.Print VarPtr(strObs), VarPtr(ByVal strObs), StrPtr(strObs), StrPtr(ByVal strObs) ' 1831204 86412692 86412692 86412692
End Sub
Sub ByValString(ByVal strObs As String) ' **-- Do a ByVal Call
10 Debug.Print VarPtr(strObs), VarPtr(ByVal strObs), StrPtr(strObs), StrPtr(ByVal strObs) ' 1831036 0 0 0
11 Let strObs = ""
12 Debug.Print VarPtr(strObs), VarPtr(ByVal strObs), StrPtr(strObs), StrPtr(ByVal strObs) ' 1831036 86412092 86412092 86412092
End Sub
The first output lines of 2 and 4 give us very similar results to the Long case. The fact that the returned value in both lines are the same demonstrate again that both in the main routine and the Called we are referring to the same variable, which is what the ByRef in the Called Sub ByRefString(ByRef strObs As String) has arranged.
Furthermore, if you run the main coding Sub ByValByRefWithPointersString() on the same computer after running the previous Long case coding from the last post, then , as I did you may get exactly the same results. This is perhaps not too surprising. At this stage we are doing exactly as in the previous coding for the corresponding code lines: At some location not easily known to us, a 32 bit, 4Byte is created holding a number, that number being sometimes regarded as the pointer: it tells us the first "left" of another 4 Bytes of memory location, set aside for us, and they will be initialised to 0 initially, just as previously, as the lines 2 and 4 suggest. (But we are not finished with memory allocation here, as we were in the Ling case!!)
https://i.postimg.cc/2y7gccT9/vb-Null-String-state.jpg
https://i.postimg.cc/2y7gccT9/vb-Null-String-state.jpg (https://postimg.cc/wR3W3kCD)
(In passing, note, that this might be considered a "vbNullString situation" , but this is not directly because of the value 0 being held in the above chunk of memory. This 0 value is a consequence of, and an indication of, a vbNullString, but it is not directly depicting in any way a "nothing string" or "not string" or "null string". The specific direct representation is given by the fact that we see nothing else substantial in the picture yet, as we soon will)
Zero length string

In line 5 I deliberately chose the string "" , rather than something like "Alan" , as initially might reasonably have been expected. This is to emphasise that this “„ is not such a direct equivalent to the long of 0. If anything it comes closer to assigning a long value of a moderately large number, similar to the "pointer" values we have discussed in the COFF. In my example I might say that it does something like what Let Lng = 86412692 might have done in the previous coding. But now something else also happens: We get something new, extra, even in the very smallest simplest case of this string "" , which can be considered a string of zero length. A laymen instinct might be to consider 0 and "" as the equivalent things for the appropriate type. But that is more false than true, even if it is not simple black and white.
The following attempts to show this situation, when we "have" , "" , and even in this very smallest simplest case of this string, "" , it is difficult to accurately show the situation in a small simple diagram, because another chunk of memory place is made available, in more typical cases this is much larger than those considered so far. Even for "" , it is larger than those considered so far: For this simplest case it is 6 Bytes, (48 Bits)
Unlike in the case of the Long, the value, in my example here , it is 86412692, (and was 2 for the Long case), is not the final value of interest to me, which in this example is "" , but it is doing a very similar thing to the value of 1831204 in the COFF: It is pointing to this new extra chunk of memory. This "" situation is more commonly called the zero Length string situations
https://i.postimg.cc/8CSsbvrK/Zero-Length-Situation.jpg
https://i.postimg.cc/8CSsbvrK/Zero-Length-Situation.jpg (https://postimg.cc/gxgYzns8)


An important observation / concussion here is that the use of = "" does not return us to the state after the Dim, in the same or similar way that the = 0 did/ does in the long case

Lines 6 and 7 are giving the same results, indicating again that we are looking at the same variable in the main routine or the first Called routine, Sub ByRefString(ByRef strObs As String)

_.___________________

Before we take a break, consider what would happen if we simply replaced Let strOb = "" with Let strOb = "A"
Lets zoom in on what the area which would most likely be the only** area changed:
https://i.postimg.cc/BnDm793c/Area-likely-to-change-on-string-value-assignment.jpg
https://i.postimg.cc/BnDm793c/Area-likely-to-change-on-string-value-assignment.jpg (https://postimg.cc/HrY0jNVV)



This would most likely be the (only** ) change after replaced Let strOb = "" with Let strOb = "A"

https://i.postimg.cc/8Pxybk2z/Let-str-Ob-A.jpg
https://i.postimg.cc/8Pxybk2z/Let-str-Ob-A.jpg (https://postimg.cc/VdBBfwsx)

So two extra Bytes are added to represent the character A

_.__

** If you make a substantial change to the length of your character string you may see another change. Consider the following short coding.


Sub BigStringLength()
Dim strOb As String
Let strOb = ""
Debug.Print VarPtr(strOb), VarPtr(ByVal strOb)
Let strOb = "A"
Debug.Print VarPtr(strOb), VarPtr(ByVal strOb)
Let strOb = "Alan"
Debug.Print VarPtr(strOb), VarPtr(ByVal strOb)
Let strOb = String(99999999, "x")
Debug.Print VarPtr(strOb), VarPtr(ByVal strOb)
End Sub
The first number in all the output pairs should never change as that is the number held in the COFF symbol table.
In the first 3 character cases the second number will either never change or just change slightly
For the last output line, the second number will very likely change, and possibly a lot. This is because the extremely large string may require VBA / your computer to find some place perhaps less often used for such things: Given that strings can be up to a GByte or two, some flexibility is required in organising their storage. (As ever the actual numbers obtained will vary depending on when / where the coding is done)

_._______________________________________---





A lot was covered in this post, and an important concept, the zero length string was. We are about half way in the total coding and are at a convenient break point as the next thing dealt with will be more detail to the briefly mentioned vbNullString….

[DocAElstein]

….continued from last post



So in the coding we are now in the main coding just before code line 8.
In the corresponding line in the Long case, we did Let Lng = 0 here, the purpose of which was to get us back to the situation of the variable at the point after the Dim

Sub ByValByRefWithPointersString() ' https://www.excelfox.com/forum/showthread.php/2404-Notes-tests-ByVal-ByRef-Application-Run-OnTime-Multiple-Variable-Arguments-ByRef-ByVal?p=11889&viewfull=1#post11889
1 Dim strOb As String
2 Debug.Print VarPtr(strOb), VarPtr(ByVal strOb), StrPtr(strOb), StrPtr(ByVal strOb) ' 1831204 0 0 0
3 Call ByRefString(strOb) ' Do a ByRef Call --#
7 Debug.Print VarPtr(strOb), VarPtr(ByVal strOb), StrPtr(strOb), StrPtr(ByVal strOb) ' 1831204 86412692 86412692 86412692

8 Let strOb = vbNullString

9 Call ByValString(strOb) ' Do a ByVal Call --**
13 Debug.Print VarPtr(strOb), VarPtr(ByVal strOb), StrPtr(strOb), StrPtr(ByVal strOb) ' 1831204 0 0 0
End Sub
Sub ByRefString(ByRef strObs As String) ' #-- Do a ByRef Call
4 Debug.Print VarPtr(strObs), VarPtr(ByVal strObs), StrPtr(strObs), StrPtr(ByVal strObs) ' 1831204 0 0 0 0 0
5 Let strObs = ""
6 Debug.Print VarPtr(strObs), VarPtr(ByVal strObs), StrPtr(strObs), StrPtr(ByVal strObs) ' 1831204 86412692 86412692 86412692
End Sub
Sub ByValString(ByVal strObs As String) ' **-- Do a ByVal Call
10 Debug.Print VarPtr(strObs), VarPtr(ByVal strObs), StrPtr(strObs), StrPtr(ByVal strObs) ' 1831036 0 0 0
11 Let strObs = ""
12 Debug.Print VarPtr(strObs), VarPtr(ByVal strObs), StrPtr(strObs), StrPtr(ByVal strObs) ' 1831036 86412092 86412092 86412092
End Sub

The last thing we discussed in the last post was the code line Let strObs = "" , and one conclusions was that the use of = "" does not return us to the state after the Dim, in the same or similar way that the = 0 did/ does in the long case.

vbNullString
This introduced for API things around the VB4 - VB5 time, and in VB6 and VBA it is "hidden". Quite a bit of debate and different opinions seem to be about it, perhaps as often as few people, if any, really know for sure. For our current discussions it seems to put us back to the state after the Dim
To quickly demonstrate that pictorially, here some sketch pairs, on the left some we had before, and on the right the change caused if we applied vbNullString to the variable
https://i.postimg.cc/k2XMKD4q/Areas-likely-to-change-on-string-value-assignment.jpg (https://postimg.cc/k2XMKD4q) https://i.postimg.cc/ZCm5Ywz9/Areas-likely-to-change-on-string-value-assignment-after-vb-Null-String.jpg (https://postimg.cc/ZCm5Ywz9)



https://i.postimg.cc/Z9WTsgpQ/Let-str-Ob-A.jpg (https://postimg.cc/Z9WTsgpQ) https://i.postimg.cc/Js6rqmDJ/Let-str-Ob-A-after-vb-Null-String.jpg (https://postimg.cc/Js6rqmDJ)



6161 6162



6163 6164


The sketches on the right represent the exact situation after the Dim

_.____-

So, after code line 8, Let strOb = vbNullString , we are back to the initial start position so as to move on to the ByRef ByVal issues. Having covered the ByRef case we move on to the ByVal

The signature line , ByVal strObs As String , in the second Called routine, Sub ByValString(ByVal strObs As String) effectively gives us a new string type variable, strObs, as demonstrated by the next code line 10 which is giving us the number in the COFF, which for the first time we see has changed from that previously. In my ran example, I had until now always got 1831204 but now I have got 1831036. We expect this as our computer still has the address 1831204 in use, as it were, in the main coding, Sub ByValByRefWithPointersString()
We see perhaps a more noticeable difference in the two numbers, compared to the long case. This is perhaps understandable as there is more uncertainty in the final memory space that might be needed. (In the long case it was known that we would need no more than another 4Bytes, 32 Bits, so the next variable could be given a value not far from the last.)
We use again the example string "" ain the variable assignment in code line11, Let strObs = ""
Code line 12 the gives expected results of the previous COFF number and now also a new, the other "pointer" value, a unique one not yet used
Finally back in the main routine, we confirm in code line 13 that nothing done in this second Called routine, Sub ByValString(ByVal strObs As String), had any effect on the strOb variable of the main routine




So the conclusion is that both Long and String type variables behave similarly in terms of the ByRef ByVal issues in Called procedures. However we have seen that we need to have a good understanding to the memory location handling, and difference in the two types in order to interoperate the results correctly.

[DocAElstein]

lfjladskjf

[DocAElstein]

Code solution for this Thread
http://www.excelfox.com/forum/showthread.php/2229-complete-page-numbers
https://www.excelforum.com/excel-programming-vba-macros/1219601-fill-out-shortened-numbers.html





Option Explicit
Sub Moshe() ' http://www.excelfox.com/forum/showthread.php/2229-complete-page-numbers
Rem 1 Make array for holding inoput data and output data - ' Input data can be handled as simple text so Array work is satisfactory
Dim arrIn() As Variant ' We know the data type can be taken as string, but I want to get the data quickly in a spreadsheet "capture" type way, using the .Value Property applied to a range object which returns a field of values for more than 1 cell returns a field of values held in Variant types, - so the type must be variant or a type mismatch runtime error will occcur
Let arrIn() = Range("A1:A" & Range("A1").CurrentRegion.Rows.Count & "").Value
Dim arrOut() As String: ReDim arrOut(1 To UBound(arrIn())) ' I can use string type to suit my final data. I also know the array size, but I must make the array a dynamic ( unknown size ) type as the Dim declare statement will only take actual numbers, but I determine my size from the size of the input array by UBound(arrIn()) : the ReDim method will accept the UBound(arrIn()) , wheras the Dim declaration syntax will not accept this, as the Dim is done at complie and not runtime
Rem 2 Effectively looping for each data row
Dim Cnt As Long ' For going through each "row"
For Cnt = 1 To UBound(arrIn()) ' Going through each element in arrIn()
'2a) split the data in a cell into an array of data. The VBA strings collection split function will return a 1 dimentsional array of string types starting at indicie 0
Dim spltEnt() As String ' For the string row split into each number entry, in other words an array of the data in a cell
If InStr(1, arrIn(Cnt, 1), ", ", vbBinaryCompare) <> 0 Then ' case more than 1 entry in cell. starting at the first character , in the current Cnt array element , I look for ", " , stipulating an excact computer match search type This Function will return eitheer the position counting from the left that it finds the first ", " or it will return 0 if it does not find at least one occurance of the ", "
Let spltEnt() = VBA.Strings.Split(arrIn(Cnt, 1), ", ", -1, vbBinaryCompare) ' we now have a number or number pair
Else ' case a single entry I cannot split by a ", " as i don't have any, ...
ReDim spltEnt(0): Let spltEnt(0) = arrIn(Cnt, 1) ' ... so i just make a single element array and put the single element in it
End If
'2b) working through each data part in a cell
Dim strOut As String 'String in each "row" '_-"Pointer" to a "Blue Print" (or Form, Questionaire not yet filled in, a template etc.)"Pigeon Hole" in Memory, 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. A String is a a bit tricky. The Blue Print code line Paper in the Pigeon Hole will allow to note the string Length and an Initial start memory Location. This Location well have to change frequently as strings of different length are assigned. Instructiions will tell how to do this. Theoretically a specilal value vbNullString is set to aid in quich checks.. But..http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring-2.html#post44116
Dim CntX As Long ' '_-Long is very simple to handle, - final memory "size" type is known (123.456 and 000.001 have same "size" computer memory ) , and so a Address suggestion can be given for the next line 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 anyways, so a Long is actually faster. ) https://www.mrexcel.com/forum/excel-questions/803662-byte-backward-loop-4.html
For CntX = 0 To UBound(spltEnt()) ' for going through each entry in a row in other words for going through each piece of data in a cell
'2c)(i) case just data for a single page
If InStr(1, spltEnt(CntX), "-", vbBinaryCompare) = 0 Then ' case of no "-"
Let strOut = strOut & spltEnt(CntX) & ", " ' just the single number goes in the output string, strOut
Else ' we have a "-"
Dim NmbrPear() As String ' this will be am Array of 2 elements for each number pair
Let NmbrPear() = VBA.Strings.Split(spltEnt(CntX), "-", -1, vbBinaryCompare)
'2c)(ii) case no correction needed in the data
If Len(NmbrPear(0)) = Len(NmbrPear(1)) Then ' the numbers are the same
Let strOut = strOut & spltEnt(CntX) & ", " ' the same number pair goes in the output string
Else ' from here on, we need to do some adjustment before adding to the output string
'2c)(iii) cases data correction needed
Select Case Len(NmbrPear(0)) - Len(NmbrPear(1)) ' selecting the case of the difference in length of the two parts of the data "FirstNumberPart-SecondNumberPart"
Case 1 ' Like 123-24 or 12345-2345
Let NmbrPear(1) = VBA.Strings.Mid$(NmbrPear(0), 1, 1) & NmbrPear(1) ' like 1 & 24 or 1 & 2345 ' VBA strings collection Mid Function: This returns the part of ( NmbrPear(0) , the starts at character 1 , and has the length of 1 character )
Case 2 ' like 123-4
Let NmbrPear(1) = VBA.Strings.Mid$(NmbrPear(0), 1, 2) & NmbrPear(1) ' like 12 & 4
Case 3 ' like 1234-6
Let NmbrPear(1) = VBA.Strings.Mid$(NmbrPear(0), 1, 3) & NmbrPear(1) ' like 123 & 6
Case 3 ' like 12345-8
Let NmbrPear(1) = VBA.Strings.Mid$(NmbrPear(0), 1, 4) & NmbrPear(1) ' like 1234 & 8
End Select ' at this point we have corrected our second number part from the pair
Let strOut = strOut & VBA.Strings.Join(NmbrPear(), "-") & ", " ' The number pair is rejoined with the corrected second number part before adding the number parts pair to the output string
End If
End If
Next CntX
'2d) The string of corrected data can now be added to the array for output
Let strOut = VBA.Strings.Left$(strOut, Len(strOut) - 2) ' This removes the last unwanted ", " ' 'VBA Strings collection Left function returns a Variant- initially tries to coerces the first parameter into Variant, Left$ does not, that's why Left$ is preferable over Left, it's theoretically slightly more efficient, as it avoids the overhead/inefficieny associated with the Variant. It allows a Null to be returned if a Null is given. https://www.excelforum.com/excel-new...ml#post4084816 .. it is all to do with ya .."Null propagation".. maties ;) '_-.. http://allenbrowne.com/casu-12.html Null is a special "I do not know, / answer unknown" - handy to hav... propogetion wonks - math things like = 1+2+Null returns you null. Or string manipulation stuff like, left(Null returns you Null. Count things like Cnt (x,y,Null) will return 2 - there are two known things there..Hmm - bit iffy although you could argue that Null has not been entered yet.. may never
Let arrOut(Cnt) = strOut ' Finally the string is aded to the current "row" in the outout array
Let strOut = "" ' Empty variable holding a row string for use ijn next loop
Next Cnt
Rem 3 I have the final data array, and so umst now paste it out where I want it.
Dim arrClmOut() As String: ReDim arrClmOut(1 To UBound(arrOut), 1 To 1) ' This is for a 1 column 2 Dimensional array which I need for the orientation of my final output
'3(i) a simple loop to fill the transposed array
Dim rCnt As Long '
For rCnt = 1 To UBound(arrOut())
Let arrClmOut(rCnt, 1) = arrOut(rCnt)
Next rCnt
'3(ii) Output to worksheet
Let Range("B1").Resize(UBound(arrOut())).Value = arrClmOut() ' The cell Top left of where the output should go is resized to the required row size, and 1 column. The .Value Property of that range object may have the values in an Array assigned to it in a simpla one line assignment
End Sub
'
'
'
' http://www.excelfox.com/forum/showthread.php/2157-Re-Defining-multiple-variables-in-VBA?p=10192#post10192
' https://www.excelforum.com/word-programming-vba-macros/1175184-vba-word-repeat-character-in-string-a-number-of-times.html#post4591171

[DocAElstein]

Code for this Thread:
http://www.excelfox.com/forum/showthread.php/2232-Excel-VBA-comma-point-thousand-decimal-separator-number-problem?p=10503#post10503
http://www.excelfox.com/forum/forumdisplay.php/13-Excel-Tips-and-Tricks


Function CStrSepDbl


'10 ' http://www.eileenslounge.com/viewtopic.php?f=27&t=22850#p208624
Function CStrSepDbl(Optional ByVal strNumber As String) As Double ' Return a Double based on a String Input which is asssumed to "Look" like a Number. The code will work for Leading and Trailing zeros, but will not return them. )
20 Rem 0 At the Dim stage a '_-String is "Pointer" to a "Blue Print" (or Form, Questionaire not yet filled in, a template etc.)"Pigeon Hole" in Memory, 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. A String is a bit tricky. The Blue Print code line Paper in the Pigeon Hole will allow to note the string Length and an Initial start memory Location. This Location well have to change frequently as strings of different length are assigned. Instructiions will tell how to do this. Theoretically a specilal value vbNullString is set to aid in quich checks, But http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring-2.html#post44116
30 If StrPtr(strNumber) = 0 Then Let CStrSepDbl = "9999999999": Exit Function '_- StrPtr(MyVaraibleNotYetUsed)=0 .. http://www.excelfox.com/forum/showthread.php/1828-How-To-React-To-The-Cancel-Button-in-a-VB-(not-Application)-InputBox?p=10463#post10463 https://www.mrexcel.com/forum/excel-questions/35206-test-inputbox-cancel-2.html?highlight=strptr#post2845398 https://www.mrexcel.com/forum/excel-questions/917689-passing-array-class-byval-byref.html#post4412382
40 Rem 1 'Adding a leading zero if no number before a comma or point, change all seperators to comma ,
50 If VBA.Strings.Left$(strNumber, 1) = "," Or VBA.Strings.Left$(strNumber, 1) = "." Then Let strNumber = "0" & strNumber ' case for like .12 or ,7 etc 'VBA Strings collection Left function returns a Variant- initially tries to coerces the first parameter into Variant, Left$ does not, that's why Left$ is preferable over Left, it's theoretically slightly more efficient, as it avoids the overhead/inefficieny associated with the Variant. It allows a Null to be returned if a Null is given. https://www.excelforum.com/excel-new...ml#post4084816 .. it is all to do with ya .."Null propagation".. maties ;) '_-.. http://allenbrowne.com/casu-12.html Null is a special "I do not know, / answer unknown" - handy to hav... propogetion wonks - math things like = 1+2+Null returns you null. Or string manipulation stuff like, left(Null returns you Null. Count things like Cnt (x,y,Null) will return 2 - there are two known things there..Hmm -bit iffy although you could argue that Null has not been entered yet..may never
60 If VBA.Strings.Left$(strNumber, 2) = "-," Or VBA.Strings.Left$(strNumber, 2) = "-." Then Let strNumber = Application.WorksheetFunction.Replace(strNumber, 1, 1, "-0") ' case for like -.12 or -,274 etc
70 Let strNumber = Replace(strNumber, ".", ",", 1, -1, vbBinaryCompare) 'Replace at start any . to a , After this point there should be either no or any amount of ,
80 'Check If a Seperator is present, then MAIN CODE is done
90 If InStr(1, strNumber, ",") > 0 Then 'Check we have at least one seperator, case we have, then..
100 Rem 2 'MAIN CODE part ====
110 'Length of String: Position of last ( Decimal ) Seperator
120 Dim LenstrNumber As Long: Let LenstrNumber = Len(strNumber): Dim posDecSep As Long: Let posDecSep = VBA.Strings.InStrRev(strNumber, ",", LenstrNumber) ' from right the positom "along" from left ( (in strNumber) , for a (",") , starting at the ( Last character ) which BTW. is the default
130 'Whole Number Part
140 Dim strHlNumber As String: Let strHlNumber = VBA.Strings.Left$(strNumber, (posDecSep - 1))
150 Let strHlNumber = Replace(strHlNumber, ",", Empty, 1, -1) 'In (strHlNumber) , I look for a (",") , and replace it with "VBA Nothing there" , considering and returning the strNumber from the start of the string , and replace all occurances ( -1 ).
160 Dim HlNumber As Long: Let HlNumber = CLng(strHlNumber) 'Long Number is a Whole Number, no fractional Part
170 'Fraction Part of Number
180 Dim strFrction As String: Let strFrction = VBA.Strings.Mid$(strNumber, (posDecSep + 1), (LenstrNumber - posDecSep)) 'Part of string (strNumber ) , starting from just after Decimal separator , and extending to a length of = ( the length of the whole strNumber minus the position of the separator )
190 Dim LenstrFrction As Long: Let LenstrFrction = Len(strFrction) 'Digits after Seperator. This must be done at the String Stage, as length of Long, Double etc will allways be 8, I think?.
200 Dim Frction As Double: Let Frction = CDbl(strFrction) 'This will convert to a Whole Double Number. Double Number can have Fractional part
210 Let Frction = Frction * 1 / (10 ^ (LenstrFrction)) 'Use 1/___, rather than a x 0.1 or 0,1 so as not to add another , . uncertainty!!
220 'Re join, using Maths to hopefully get correct Final Value
230 Dim DblReturn As Double 'Double Number to be returned in required Format after maniplulation.
240 If Left(strHlNumber, 1) <> "-" Then 'Case positive number
250 Let DblReturn = CDbl(HlNumber) + Frction 'Hopefully a simple Mathematics + will give the correct Double Number back
260 Else 'Case -ve Number
270 Let strHlNumber = Replace(strHlNumber, "-", "", 1, 1, vbBinaryCompare) ' strHlNumber * (-1) ' "Remove" -ve sign
280 Let DblReturn = (-1) * (CDbl(strHlNumber) + Frction) 'having constructed the value of the final Number we multiply by -1 to put the Minus sign back
290 End If 'End checking polarity.
300 'Final Code Line(s) At this point we have what we want. We need to place this in the "Double Type variable" , CStrSepDbl , so that an assinment like = CStrSepDbl( ) will return this final value
310 Let CStrSepDbl = DblReturn 'Final Double value to be returned by Function
320 Else 'End MAIN CODE. === We came here if we have a Whole Number with no seperator, case no seperator
330 'Simple conversion of a string "Number" with no Decimal Seperator to Double Format
340 Let CStrSepDbl = CDbl(strNumber) 'String to be returned by Function is here just a simple convert to Double ' I guess this will convert a zero length string "" to 0 also
350 End If 'End checking for if a Seperator is present.
End Function
























'Long code lines: Referrences http://www.mrexcel.com/forum/about-board/830361-board-wish-list-2.html http://www.mrexcel.com/forum/test-here/928092-http://www.eileenslounge.com/viewtopic.php?f=27&t=22850
Function CStrSepDblshg(strNumber As String) As Double ' http://excelxor.com/2014/09/05/index-returning-an-array-of-values/ http://www.techonthenet.com/excel/formulas/split.php
5 If Left(strNumber, 1) = "," Or Left(strNumber, 1) = "." Then Let strNumber = "0" & strNumber
20 Let strNumber = Replace(strNumber, ".", ",", 1, -1)
40 If InStr(1, strNumber, ",") > 0 Then
170 If Left(Replace(Left(strNumber, (InStrRev(strNumber, ",", Len(strNumber)) - 1)), ",", Empty, 1, 1), 1) <> "-" Then
180 Let CStrSepDblshg = CDbl(CLng(Replace(Left(strNumber, (InStrRev(strNumber, ",", Len(strNumber)) - 1)), ",", Empty, 1, 1))) + CDbl(Mid(strNumber, (InStrRev(strNumber, ",", Len(strNumber)) + 1), (Len(strNumber) - InStrRev(strNumber, ",", Len(strNumber))))) * 1 / (10 ^ (Len(Mid(strNumber, (InStrRev(strNumber, ",", Len(strNumber)) + 1), (Len(strNumber) - InStrRev(strNumber, ",", Len(strNumber)))))))
190 Else
210 Let CStrSepDblshg = (-1) * (CDbl(Replace(Left(strNumber, (InStrRev(strNumber, ",", Len(strNumber)) - 1)), ",", Empty, 1, 1) * (-1)) + CDbl(Mid(strNumber, (InStrRev(strNumber, ",", Len(strNumber)) + 1), (Len(strNumber) - InStrRev(strNumber, ",", Len(strNumber))))) * 1 / (10 ^ (Len(Mid(strNumber, (InStrRev(strNumber, ",", Len(strNumber)) + 1), (Len(strNumber) - InStrRev(strNumber, ",", Len(strNumber))))))))
220 End If
250 Else
270 Let CStrSepDblshg = CDbl(strNumber)
280 End If
End Function


Demo Code to call Function

Sub TestieCStrSepDbl() ' using adeptly named TabulatorSyncranartor ' / Introducing LSet TabulatorSyncranartor Statement : http://www.excelfox.com/forum/showthread.php/2230-Built-in-VBA-methods-and-functions-to-alter-the-contents-of-existing-character-strings
Dim LooksLikeANumber(1 To 17) As String
Let LooksLikeANumber(1) = "001,456"
Let LooksLikeANumber(2) = "1.0007"
Let LooksLikeANumber(3) = "123,456.2"
Let LooksLikeANumber(4) = "0023.345,0"
Let LooksLikeANumber(5) = "-0023.345,0"
Let LooksLikeANumber(6) = "1.007"
Let LooksLikeANumber(7) = "1.3456"
Let LooksLikeANumber(8) = "1,2345"
Let LooksLikeANumber(9) = "01,0700000"
Let LooksLikeANumber(10) = "1.3456"
Let LooksLikeANumber(11) = "1,2345"
Let LooksLikeANumber(12) = ".2345"
Let LooksLikeANumber(13) = ",4567"
Let LooksLikeANumber(14) = "-,340"
Let LooksLikeANumber(15) = "00.04"
Let LooksLikeANumber(16) = "-0,56000000"
Let LooksLikeANumber(17) = "-,56000001"
Dim Stear As Variant, MyStringsOut As String
For Each Stear In LooksLikeANumber()
Dim Retn As Double
Let Retn = CStrSepDbl(Stear)
Dim TabulatorSyncranartor As String: Let TabulatorSyncranartor = " "
LSet TabulatorSyncranartor = Stear
Let MyStringsOut = MyStringsOut & TabulatorSyncranartor & Retn & vbCrLf
Debug.Print Stear; Tab(15); Retn
Next Stear
MsgBox MyStringsOut
End Sub





Code also Here:
https://pastebin.com/1kq6h9Bn

[DocAElstein]

Code for this Thread:
http://www.excelfox.com/forum/showthread.php/2232-Excel-VBA-comma-point-thousand-decimal-separator-number-problem?p=10503#post10503
http://www.excelfox.com/forum/forumdisplay.php/13-Excel-Tips-and-Tricks


Function CStrSepDbl


'10 ' http://www.eileenslounge.com/viewtopic.php?f=27&t=22850#p208624
Function CStrSepDbl(Optional ByVal strNumber As String) As Double ' Return a Double based on a String Input which is asssumed to "Look" like a Number. The code will work for Leading and Trailing zeros, but will not return them. )
20 Rem 0 At the Dim stage a '_-String is "Pointer" to a "Blue Print" (or Form, Questionaire not yet filled in, a template etc.)"Pigeon Hole" in Memory, 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. A String is a bit tricky. The Blue Print code line Paper in the Pigeon Hole will allow to note the string Length and an Initial start memory Location. This Location well have to change frequently as strings of different length are assigned. Instructiions will tell how to do this. Theoretically a specilal value vbNullString is set to aid in quich checks, But http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring-2.html#post44116
30 If StrPtr(strNumber) = 0 Then Let CStrSepDbl = "9999999999": Exit Function '_- StrPtr(MyVaraibleNotYetUsed)=0 .. http://www.excelfox.com/forum/showthread.php/1828-How-To-React-To-The-Cancel-Button-in-a-VB-(not-Application)-InputBox?p=10463#post10463 https://www.mrexcel.com/forum/excel-questions/35206-test-inputbox-cancel-2.html?highlight=strptr#post2845398 https://www.mrexcel.com/forum/excel-questions/917689-passing-array-class-byval-byref.html#post4412382
40 Rem 1 'Adding a leading zero if no number before a comma or point, change all seperators to comma ,
50 If VBA.Strings.Left$(strNumber, 1) = "," Or VBA.Strings.Left$(strNumber, 1) = "." Then Let strNumber = "0" & strNumber ' case for like .12 or ,7 etc 'VBA Strings collection Left function returns a Variant- initially tries to coerces the first parameter into Variant, Left$ does not, that's why Left$ is preferable over Left, it's theoretically slightly more efficient, as it avoids the overhead/inefficieny associated with the Variant. It allows a Null to be returned if a Null is given. https://www.excelforum.com/excel-new...ml#post4084816 .. it is all to do with ya .."Null propagation".. maties ;) '_-.. http://allenbrowne.com/casu-12.html Null is a special "I do not know, / answer unknown" - handy to hav... propogetion wonks - math things like = 1+2+Null returns you null. Or string manipulation stuff like, left(Null returns you Null. Count things like Cnt (x,y,Null) will return 2 - there are two known things there..Hmm -bit iffy although you could argue that Null has not been entered yet..may never
60 If VBA.Strings.Left$(strNumber, 2) = "-," Or VBA.Strings.Left$(strNumber, 2) = "-." Then Let strNumber = Application.WorksheetFunction.Replace(strNumber, 1, 1, "-0") ' case for like -.12 or -,274 etc
70 Let strNumber = Replace(strNumber, ".", ",", 1, -1, vbBinaryCompare) 'Replace at start any . to a , After this point there should be either no or any amount of ,
80 'Check If a Seperator is present, then MAIN CODE is done
90 If InStr(1, strNumber, ",") > 0 Then 'Check we have at least one seperator, case we have, then..
100 Rem 2 'MAIN CODE part ====
110 'Length of String: Position of last ( Decimal ) Seperator
120 Dim LenstrNumber As Long: Let LenstrNumber = Len(strNumber): Dim posDecSep As Long: Let posDecSep = VBA.Strings.InStrRev(strNumber, ",", LenstrNumber) ' from right the positom "along" from left ( (in strNumber) , for a (",") , starting at the ( Last character ) which BTW. is the default
130 'Whole Number Part
140 Dim strHlNumber As String: Let strHlNumber = VBA.Strings.Left$(strNumber, (posDecSep - 1))
150 Let strHlNumber = Replace(strHlNumber, ",", Empty, 1, -1) 'In (strHlNumber) , I look for a (",") , and replace it with "VBA Nothing there" , considering and returning the strNumber from the start of the string , and replace all occurances ( -1 ).
160 Dim HlNumber As Long: Let HlNumber = CLng(strHlNumber) 'Long Number is a Whole Number, no fractional Part
170 'Fraction Part of Number
180 Dim strFrction As String: Let strFrction = VBA.Strings.Mid$(strNumber, (posDecSep + 1), (LenstrNumber - posDecSep)) 'Part of string (strNumber ) , starting from just after Decimal separator , and extending to a length of = ( the length of the whole strNumber minus the position of the separator )
190 Dim LenstrFrction As Long: Let LenstrFrction = Len(strFrction) 'Digits after Seperator. This must be done at the String Stage, as length of Long, Double etc will allways be 8, I think?.
200 Dim Frction As Double: Let Frction = CDbl(strFrction) 'This will convert to a Whole Double Number. Double Number can have Fractional part
210 Let Frction = Frction * 1 / (10 ^ (LenstrFrction)) 'Use 1/___, rather than a x 0.1 or 0,1 so as not to add another , . uncertainty!!
220 'Re join, using Maths to hopefully get correct Final Value
230 Dim DblReturn As Double 'Double Number to be returned in required Format after maniplulation.
240 If Left(strHlNumber, 1) <> "-" Then 'Case positive number
250 Let DblReturn = CDbl(HlNumber) + Frction 'Hopefully a simple Mathematics + will give the correct Double Number back
260 Else 'Case -ve Number
270 Let strHlNumber = Replace(strHlNumber, "-", "", 1, 1, vbBinaryCompare) ' strHlNumber * (-1) ' "Remove" -ve sign
280 Let DblReturn = (-1) * (CDbl(strHlNumber) + Frction) 'having constructed the value of the final Number we multiply by -1 to put the Minus sign back
290 End If 'End checking polarity.
300 'Final Code Line(s) At this point we have what we want. We need to place this in the "Double Type variable" , CStrSepDbl , so that an assinment like = CStrSepDbl( ) will return this final value
310 Let CStrSepDbl = DblReturn 'Final Double value to be returned by Function
320 Else 'End MAIN CODE. === We came here if we have a Whole Number with no seperator, case no seperator
330 'Simple conversion of a string "Number" with no Decimal Seperator to Double Format
340 Let CStrSepDbl = CDbl(strNumber) 'String to be returned by Function is here just a simple convert to Double ' I guess this will convert a zero length string "" to 0 also
350 End If 'End checking for if a Seperator is present.
End Function
























'Long code lines: Referrences http://www.mrexcel.com/forum/about-board/830361-board-wish-list-2.html http://www.mrexcel.com/forum/test-here/928092-http://www.eileenslounge.com/viewtopic.php?f=27&t=22850
Function CStrSepDblshg(strNumber As String) As Double ' http://excelxor.com/2014/09/05/index-returning-an-array-of-values/ http://www.techonthenet.com/excel/formulas/split.php
5 If Left(strNumber, 1) = "," Or Left(strNumber, 1) = "." Then Let strNumber = "0" & strNumber
20 Let strNumber = Replace(strNumber, ".", ",", 1, -1)
40 If InStr(1, strNumber, ",") > 0 Then
170 If Left(Replace(Left(strNumber, (InStrRev(strNumber, ",", Len(strNumber)) - 1)), ",", Empty, 1, 1), 1) <> "-" Then
180 Let CStrSepDblshg = CDbl(CLng(Replace(Left(strNumber, (InStrRev(strNumber, ",", Len(strNumber)) - 1)), ",", Empty, 1, 1))) + CDbl(Mid(strNumber, (InStrRev(strNumber, ",", Len(strNumber)) + 1), (Len(strNumber) - InStrRev(strNumber, ",", Len(strNumber))))) * 1 / (10 ^ (Len(Mid(strNumber, (InStrRev(strNumber, ",", Len(strNumber)) + 1), (Len(strNumber) - InStrRev(strNumber, ",", Len(strNumber)))))))
190 Else
210 Let CStrSepDblshg = (-1) * (CDbl(Replace(Left(strNumber, (InStrRev(strNumber, ",", Len(strNumber)) - 1)), ",", Empty, 1, 1) * (-1)) + CDbl(Mid(strNumber, (InStrRev(strNumber, ",", Len(strNumber)) + 1), (Len(strNumber) - InStrRev(strNumber, ",", Len(strNumber))))) * 1 / (10 ^ (Len(Mid(strNumber, (InStrRev(strNumber, ",", Len(strNumber)) + 1), (Len(strNumber) - InStrRev(strNumber, ",", Len(strNumber))))))))
220 End If
250 Else
270 Let CStrSepDblshg = CDbl(strNumber)
280 End If
End Function


Demo Code to call Function

Sub TestieCStrSepDbl() ' using adeptly named TabulatorSyncranartor ' / Introducing LSet TabulatorSyncranartor Statement : http://www.excelfox.com/forum/showthread.php/2230-Built-in-VBA-methods-and-functions-to-alter-the-contents-of-existing-character-strings
Dim LooksLikeANumber(1 To 17) As String
Let LooksLikeANumber(1) = "001,456"
Let LooksLikeANumber(2) = "1.0007"
Let LooksLikeANumber(3) = "123,456.2"
Let LooksLikeANumber(4) = "0023.345,0"
Let LooksLikeANumber(5) = "-0023.345,0"
Let LooksLikeANumber(6) = "1.007"
Let LooksLikeANumber(7) = "1.3456"
Let LooksLikeANumber(8) = "1,2345"
Let LooksLikeANumber(9) = "01,0700000"
Let LooksLikeANumber(10) = "1.3456"
Let LooksLikeANumber(11) = "1,2345"
Let LooksLikeANumber(12) = ".2345"
Let LooksLikeANumber(13) = ",4567"
Let LooksLikeANumber(14) = "-,340"
Let LooksLikeANumber(15) = "00.04"
Let LooksLikeANumber(16) = "-0,56000000"
Let LooksLikeANumber(17) = "-,56000001"
Dim Stear As Variant, MyStringsOut As String
For Each Stear In LooksLikeANumber()
Dim Retn As Double
Let Retn = CStrSepDbl(Stear)
Dim TabulatorSyncranartor As String: Let TabulatorSyncranartor = " "
LSet TabulatorSyncranartor = Stear
Let MyStringsOut = MyStringsOut & TabulatorSyncranartor & Retn & vbCrLf
Debug.Print Stear; Tab(15); Retn
Next Stear
MsgBox MyStringsOut
End Sub





Code also Here:
https://pastebin.com/1kq6h9Bn

[DocAElstein]

_1 ) Way 1) Use the CDO (Collaboration Data Objects ) object library available in VBA
Main Code , Sub PetrasDailyProWay1_COM_Way() ,
and
Function Code for solution to this Thread and Post
http://www.excelfox.com/forum/showthread.php/2233-Urgent-support-needed-Multiple-emails-multiple-Excel-workbooks-at-once
http://www.excelfox.com/forum/showthread.php/2233-Urgent-support-needed-Multiple-emails-multiple-Excel-workbooks-at-once?p=10518#post10518






Option Explicit ' Daily Diet plan, Sending of Notes and an Excel File
Sub PetrasDailyProWay1_COM_Way() ' Allow access to deep down cods wollops from Microsoft to collaborating in particular in the form of messaging. An available library of ddl library functions and associated things is available on request, the Microsoft CDO for Windows 2000. We require some of these ' CDO is an object library that exposes the interfaces of the Messaging Application Programming Interface (MAPI). API: interfaces that are fairly easy to use from a fairly higher level from within a higher level programming language. In other words this allows you to get at and use some of the stuff to do with the COM OLE Bollocks from within a programming language such as VBA API is often referring loosely to do with using certain shipped with Windows software in Folders often having the extension dll. This extension , or rather the dll stands for direct link libraries. These are special sort of executable files of functions shared by many other (Windows based usually) software’s.
' Rem1 The deep down fundamental stuff , which includes stuff been there the longest goes by the name of Component Object Model. Stuff which is often, but not always, later stuff, or at a slightly higher level of the computer workings, or slightly more to a specific application ( an actual running "runtime" usage / at an instance in time , "instance of" ) orientated goes to the name of Object Linking and Embedding. At this lower level, there are protocols for communicating between things, and things relate are grouped into the to Office application available Library, CDO. An important object there goes by the name of Message.
'Rem 1) Library made available ====================#
With CreateObject("CDO.Message") ' Folders mostly but not always are in some way referenced using dll, either as noted with the extension or maybe refered to as dll Files or dll API files.
'Rem 2 ' Intraction protocols are given requird infomation and then set
'2a) 'With --------------------* my Created LCDCW Library, (LCD 1.0 Library ) (Linking Configuration Data_Cods Wollups) which are used and items configured for the Exchange at Microsoft’s protocol thereof; http://schemas.microsoft.com/cdo/configuration/ ......This section provides the configuration information for the remote SMTP server
Dim LCD_CW As String: Let LCD_CW = "http://schemas.microsoft.com/cdo/configuration/" ' Linking Configuration Data : defines the majority of fields used to set configurations for various Linking Collaboration (LCD) Objects Cods Wollops: These configuration fields are set using an implementation of the IConfiguration.Fields collection. https://msdn.microsoft.com/en-us/library/ms872853(v=exchg.65).aspx
.Configuration(LCD_CW & "smtpusessl") = True ' ' ' HTTPS (Hyper Text Transfer Protocol Secure) appears in the URL when a website is secured by an SSL certificate. The details of the certificate, including the issuing authority and the corporate name of the website owner, can be viewed by clicking on the lock symbol on the browser bar. in short, it's the standard technology for keeping an internet connection secure and safeguarding any sensitive data that is being sent between two systems, preventing criminals from reading and modifying any information transferred, including potential personal details. ' SSL protocol has always been used to encrypt and secure transmitted data
.Configuration(LCD_CW & "smtpauthenticate") = 1 ' ... possibly this also needed .. When you also get the Authentication Required Error you can add this three lines.
' ' Sever info
.Configuration(LCD_CW & "smtpserver") = "smtp.gmail.com" ' "securesmtp.t-online.de" '"smtp.gmail.com" "smtp.mail.yahoo.com" "smtp.live.com" "pod51017.outlook.com" "smtp-mail.outlook.com" "smtp.live.com" "securesmtp.t-online.de" 465 SMTP is just used to mean the common stuff..... Simple Mail Transport Protocol (SMTP) server is used to send outgoing e-mails. The SMTP server receives emails from your Mail program and sends them over the Internet to their destination.
' The mechanism to use to send messages.
.Configuration(LCD_CW & "sendusing") = 2 ' Based on the LCD_OLE Data Base of type DBTYPE_I4
.Configuration(LCD_CW & "smtpserverport") = 25 ' 465or25fort-online ' 465 'or 587 'or 25 ' The port of type somehow refered to by the last line
'
.Configuration(LCD_CW & "sendusername") = "excelvbaexp@gmail.com" ' "Doc.AElstein@t-online.de" ' .... "server rejected your response". AFAIK : This will happen if you haven't setup an account in Outlook Express or Windows Mail .... Runtime error '-2147220975 (800440211)': The message could not be sent to the SMTP server. The transport error code is 0x80040217. The server response is not available
.Configuration(LCD_CW & "sendpassword") = "Bollocks" ' "Bollox"
' Optional - How long to try ( End remote SMTP server configuration section )
.Configuration(LCD_CW & "smtpconnectiontimeout") = 30 ' Or there Abouts ;) :)
' Intraction protocol is Set/ Updated
.Configuration.Fields.Update ' 'Not all infomation is given, some will have defaults. - possibly this might be needed initially .. .Configuration.Load -1 ' CDO Source Defaults
'End With ' -------------------* my Created LCDCW Library ( Linking Configuration Data Cods Wollups) which are used and items configured for the Exchange at Microsoft's protocol therof;
'2b) ' Data to be sent
'.To = "Doc.AElstein@t-online.de"
.To = "excelvbaexp@gmail.com"
.CC = ""
.BCC = ""
.from = """Alan"" <Doc.AElstein@t-online.de>"
.Subject = "Bollox"
'.TextBody = "Hi" & vbNewLine & vbNewLine & "Please find the Excel workbook attached."
.HTMLBody = MyLengthyStreaming
.AddAttachment "G:\ALERMK2014Marz2016\NeueBlancoAb27.01.2014\AbJan 2016\Übersicht aktuell.xlsx" ' ' Full File path and name. File must be closed
Rem 3 Do it
.Send
End With ' CreateObject("CDO.Message") (Rem 1 Library End =======#
End Sub
Public Function MyLengthyStreaming() As String
Rem 1 Make a long string from a Microsoft Word doc
'1(i) makes available the Library of stuff, objects, Methods etc.
Dim Fso As Object: Set Fso = CreateObject("Scripting.FileSystemObject")
'1(ii) makes the big File Object " Full path and file name of Word doc saved as .htm "
Dim FileObject As Object: Set FileObject = Fso.GetFile("G:\ALERMK2014Marz2016\NeueBlancoAb27.01.2014\AbJan 2016\ProMessage.htm"): Debug.Print FileObject
'1(iii) sets up the data "stream highway"
Dim Textreme As Object: Set Textreme = FileObject.OpenAsTextStream(iomode:=1, Format:=-2) ' reading only, Opens using system default https://msdn.microsoft.com/en-us/library/aa265341(v=vs.60).aspx
'1(iv) pulls in the data, in our case into a simple string variable
Let MyLengthyStreaming = Textreme.ReadAll ' Let MyLengthyStreaming = Replace(MyLengthyStreaming, "align=center x:publishsource=", "align=left x:publishsource=")
Textreme.Close
Set Textreme = Nothing
Set Fso = Nothing
Rem 2 possible additions to MyLengthyStreaming






Last bit of Function ( must go here in the excelfox Test Sub Forum in HTML Tags as there are HTML Tags in the final text string string and this makes a mess in normal BB code tags, because in excelfox Test Forum HTML is activated ) :


Rem 2
Let MyLengthyStreaming = "<p><span style=""color: #ff00ff;"">Start=========== " & Format(Now(), "DD MMMM YYYY") & " " & Now() & " ------------------------------------</span></p>" & MyLengthyStreaming & "<p><span style=""color: #ff00ff;"">-- " & Format(Now(), "DD MMMM YYYY") & " " & Now() & " ==End, Sent from Doc.AElstein Mail ======</span></p>"
End Function

[DocAElstein]

Hi Smellbum,

It seems I was incredibly very stupidly in the rush to get the USB stick and stuff to the post, such that I did not copy all that was on the stick somewhere. How incredibly stupid for the “computer expert” I am trying to be.
I was intending to put a copy of it all here, later, working from the Microsoft Office Word file on the stick. So I can’t do that now, :(

Sorry about that.

It’s wet and cold today, but should warm up a bit tomorrow, for a last week of late summer. I was intending to sit indoors and do a few things on the computer. As a punishment for my stupidity, to help me kick myself in the arse so as to remember to not do it again, I will go out in the rain, in the wet mud, and do some stuff on my latest unconventional gardening project... ... its getting close to finished ....


(If you click on the small pics, then it should get bigger. There should be a bigger pic as well. It may or may not be there for you. And there is a small link to the pic. Hopefully at least one of them will work for you)

Alan



https://i.postimg.cc/Kc7vLsMc/Garden-Project-Sept-2024.jpg

5908 https://i.postimg.cc/Kc7vLsMc/Garden-Project-Sept-2024.jpg (https://postimg.cc/t7TjGkk8)











https://i.postimg.cc/zXnWyjJT/Garden-Project-Sept-2024.jpg

5909 https://i.postimg.cc/zXnWyjJT/Garden-Project-Sept-2024.jpg (https://postimg.cc/0byr37KN)






The green metal mesh stuff is conventional fence elements, but I am using them very unconventionally. The land before the “fence” is owned by the Town. I tend to use it, and I call it my occupied safety zone. I found that over the years it is quite effective at minimising the effect of all the stupid officially they have routinely inspecting / controlling etc. I fight them there, and it keeps them busy and they don’t look as much as they used to at the activities I do on my property. The end effect suits me better. The land behind the fence belongs to a fairly new neighbour. The older one it used to belong to was a real old miserable prat and pain in the arse to us. As with most of the neighbours nowadays, the new one gets on very well with me. We ( I now ) , have , one way or another, kind of organised the neighbours to suit. Its one of the many now really good things we worked hard to achieve, but are partly wasted, at least for Petra, as she seems to have gone totally mental and brain dead, as far as the two of us are concerned, and seems to have forgotten a lot of great and significant things that were the two of us, here, :(

[DocAElstein]

Hi Smellbum,
Just on the off-chance you pass here today….. Happy Birthday!! :-)

I probably won’t post much more here, at least not for a while. See first if my recent letter or whatever got across. (This is the first of three Birthday posts)

By the way, I don’t think anyone other than me and perhaps you will see these postings. They are in an old Thread thing, well down the list, and not many people even catch any of the new stuff here, let alone ever going down looking at the older Threads. Anyway I have almost “all power” here, so can edit, move , delete stuff of mine or anyone’s

The night before last I went to my “Pooh Bahnhof”, my original train station where I grilled a lot last year. Because of my new train area and special activities this summer season, I have not been there much yet this summer season. Possibly the last time would have been when I took your last letter to read, ( the one before the latest birthday wod of stuff )

The Bushes that hide me a bit, those in between my two tracks and the main two tracks still in use, ( I think I mentioned in a letter that they got massacred early this year by a random attack from the track workers that occasionally, seemingly randomly, tidy away some area alongside the main tracks ), they have not fully recovered yet, so I am less well hidden so it suits perhaps that I was there less this season. (remember to click on these small pics, then they should get bigger for you to see, or click on the small URL link )
https://i.postimg.cc/YC8S4Wc5/bushes-not-quite-recovered.jpg
5920

I was pleased to see that it appears no one has been there, everything was just as I left it. Just a bit more messy and untidy, - I think a few strong winds recently caused that, blowing around my buckets of rubbish, which in the previous year would have long since been burnt away on a grilling evening. My Beer Bottles are just as I left them. An oversight on my behalf, - when its dark, late and I am a bit drunk, I sometimes forget something, my two mates are always the top priority not to forget, - I have not forgotten yet to take them home with me at the end of a session
https://i.postimg.cc/26rBpDDY/Untouched-but-bit-messy-ans-windswept.jpg
5921


So I had your Birthday card as last to open still. (After the couple of wet cold days, the temperature went back up nicely, so it looks like my one off in a lifetime extended Birthday has / is going on a bit in extra time ) So we settled in nicely for the evening.
https://i.postimg.cc/mrXhnF8Y/Settled-in-for-the-Evening.jpg
59225922



We have a new friend, since this year. Even if we don’t go there for a while, he consistently appears, a little mouse. Usually he likes to play with and lick a small veggie meat-ball, but we had none this evening, but he was happy to chew on a veggie burger whilst chatting to Pooh
https://i.postimg.cc/L6Y5bX1f/Pooh-with-mouse.jpg
5923

[DocAElstein]

Later, just as it was starting to get dark, when I went to check out my new little area where I sit later in the dark, and had a nice surprise. – The very big story from the start of the year, ( which I have only given you some hints of so far ), gave me some concern that it looked rather obvious and substantial what I had done, and quite frankly I am amazed there was not a lot more interest taken by the track workers, railway police and people walking nearby occasionally. Many days at the end of my activity I threw a few random seed of anything I had. As it turned out, little came from those other than a few yellow mustard things, as in a pic I sent you last time. Instead nature was very kind and some torrential rain downpours encouraged a rapid growth in weeds and stuff that pretty well totally hid all I had been doing. Anyways, the nice surprise, was that in the weeks / months I have not been there, it seems a sunflower has appeared, almost certainly from a seed I threw, as since over 100 years only weeds and similar had ever grown there.
My old digital camera does strange things when it is starting to get dark. I have not figured out why yet. If it flashes the pic is good, but it looks darker than it is. Without the flash, if it chooses randomly not to use it, the quality is poor with a fuzzy pic. Some setting to do with the exposure time I expect, so for a good evening pic without the flash I need to have the camera mounted solidly perhaps. Here is the best few I managed to take on the evening
https://i.postimg.cc/fL8y6SC7/Early-Exening-Pick-when-camera-uses-flash.jpg
https://i.postimg.cc/NGPLDT5B/Fuzzy-early-Evening-Sun-Flower-pic.jpg

5924 5925



I went back the next day, partly just to get a better pic, and give it a couple of metal props to help it survive a few strong winds we are having in these last few otherwise nice late summer sunny days
( I had to hide first near my signal to let a long tanker train pass )

( https://i.postimg.cc/5yqVZhbh/Hide-from-Tanker-Train.jpg ) https://i.postimg.cc/W4CTrm5H/Day-Pic-and-pretty-props.jpg
( 5926 ) 5927




That sun flower is just about the only visible evidence of the major thing I did there early this year.
https://i.postimg.cc/kgW47pwg/Sun-Flower-Only-Evidence.jpg
5928

[DocAElstein]

The other reason I popped back in daylight was that again I forgot a few things, - your card, and I left my Beer in the sun. I must be more careful
https://i.postimg.cc/2y4kCqFX/Forgot-Card-and-to-hide-beer-from-th-Sun.jpg
5929




Just one of my hundreds or sunflower attempts at home this year, has turned into a big one, almost 4 meters. I may have sent a pic of that. As I stupidly did not make a copy of all I sent you for your Birthday, I am not sure.
Anyway, its showing quite nicely this morning in the late summer sun, making a reasonable attempt to be pretty for your Birthday.
:-)
https://i.postimg.cc/9fnFbHQ1/Friday-20-September.jpg
5930



Alan

[DocAElstein]

VMDSMV

[DocAElstein]

SDLMVDV

[DocAElstein]

MVÖLDSVV

[DocAElstein]

ÖÖLBVFDS

[DocAElstein]

just testing some links, ignore me

( The blue Triffids have mostly gone for this year, they tend not to stay around much after Summer, just one relatively smaller one hanging on between the park spaces https://i.postimg.cc/9F4ywJ6J/Last-middle-sized-Triffid-2024.jpg )



The blue wheelbarrow up on its makeshift pedestal was still struggling for most of Summer to do much other than have some quite long stems, stubbornly refusing to flower (

https://i.postimg.cc/d3wStgHH/Blue-Wheelbarrow-on-white-platform-late-August-2024.jpg )

That is making some attempt now to get a few flowers out

https://i.postimg.cc/RVvjMJ2S/Blue-wheelbarrow-work-up-a-bit.jpg



One of my two orange wheelbarrows has definitely suffered badly from the over population, and had unhealthy thin stems all Summer, ( https://i.postimg.cc/ZKfD47rH/Orange-Wheelbarrow-still-no-flowers-Sept-2024-too-densly-populated.jpg

https://i.postimg.cc/RZysnWpn/Orange-Wheelbarrow-too-densly-populated.jpg ) , this morning in the sun it has not really made a serious attempt to flower, just a few small unhealthy looking flowers

https://i.postimg.cc/XN1wvTzr/Orange-Wheelbarrow-1-thin-stems-end-of-Summer.jpg



A similar story, just slightly better with the second orange wheelbarrow

https://i.postimg.cc/L8LfNh6P/Orange-Wheelbarrow-2-thin-stems-end-of-Summer.jpg



I had forgot about a bunch of Sunflowers behind my VW Bus, - I just took a look, and they don’t look so bad, they have made a last attempt to flower a bit

https://i.postimg.cc/50hQBVdg/Sun-Flower-Bunch-behind-Bus-making-an-attempt-to-Flower.jpg





I had not been to my railway station much this summer, due to all the activities here. A few days ago I popped by. r At the end of zhe day working on an activity there early this year, I always threw a few seeds around, anything I had, so that hopefully things would grow to hide my river. As it happened some very heavy rain pours very conveniently encouraged weeds and bushes to grow rapidly. Few things seemed to appear from my seeding’s. Or so I thought… I was pleased to see a solitary Sunflower, which had grown in my absence. That is very likely from me, no flowers have ever grown there for the last 100 years I expect. It’s the only visible piece of evidence from what I did

https://i.postimg.cc/J752d409/Only-Evidence.jpg

[DocAElstein]

LKFNASFASN

[gads200]

Hiya
I've finally had a chance to sit down and play with this......origionally I couldnt get any photos to load as I didn't have an account or had registered...have done it now so they all appear!! Not sure if you'll get a notification? think ive pit me setting so that I will if you see/respond :)

[DocAElstein]

Hi there!!!!!!

What a surprise. (Funny name, I might have guessed that a decade or two ago, I thought it might be more like walks___ now?
I was not expecting you to reply. No matter. (I just asked my mate, the only moderator here, ( sandy666 (https://www.excelfox.com/forum/member.php/11857-sandy666) the chap with the devilish Avatar pic) not to delete you as a Spammer, :)
I am not sure what the best way is to “deal with you” here. But you do what you want for now. I will sort it all out later.

Just now I got my head in a hard technical question partly here and mostly in another forum. It might do my brain in for the rest of the day. But tomorrow I will catch up with you again.

(As for notifications, they have been getting worse and worse here as well as in a lot of these places. They work as they should sometimes, but more often they don’t. I should get notification of everything that happens here, as not much does happen, so I would not get flooded with a lot of notifications. I did not get the notification of your post as I should have. But I have finally got around to sitting a lot on my arse after all my summer activities, and that health insurance / health authority problem I mentioned a while back got even worse, but… it came to court, the odds where against me , but I wore your Wilson T-Shirt at the hearing and surprised myself how I sorted them all out, myself, without a Lawyer. So that is a weight off my mind, and so I spend more time at excelfox since a few weeks, trying to get on with my “computer career” attempt)

I will have to go and get on with the forum thing for a while

Catch you a bit later

Alan