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