Code:
' 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
Originally Posted by MARK858
Reply With Quote
Bookmarks