Results 1 to 1 of 1

Thread: Test

  1. #1

    Thread Starter
    New Member Doc.AElstein's Avatar
    Join Date
    Sep 2020


    Just testing.

    Sub Oops()
    Application.Calculation = xlCalculationAutomatic
    End Sub

    _____ Workbook: 20SepSample3.xlsm ( Using Excel 2007 32 bit )
    0.61 1.52 7.14 0.92 2.88 3.47 4.03
    0.73 -0.01 5.02 -2.43 -0.48 2.97 5.36
    0.8 0.99 7.63 8.76 11.18 6.56 5.95
    0.22 0.38 0.67 3.41 3.93 5.42 4.63
    Worksheet: FundSelection
    <blockquote class="imgur-embed-pub" lang="en" data-id="SnM2jm2"><a href="">View post on</a></blockquote><script async src="//" charset="utf-8"></script>
    Name:  arr1D_b()OptionBase0.jpg
Views: 3
Size:  53.5 KB

    The text that you have entered is too long (27539 characters). Please shorten it to 25000 characters long.

    The problem is solved here, but I am just adding some stuff, out of passing interest…

    I am not 100% sure about this, but I think Excel finds that problem giving Cells(i, 0) syntactically OK, ref!!.
    But it can’t find a range object in row 1 that is one column to the left of column A in a worksheet ref!!
    I think it is that problem that chucks up the error.

    A bit more info, just out of interest… _
    _... I am considering two different things which it is very easy to get mixed up
    Excel referencing spreadsheet cells, which is mostly a “relative referencing thing” of one sort or another, even if it might not appear to be at first glance, ref!!
    _ … How Excel takes arrays from ranges and pastes arrays into ranges

    First, a quick review of Excel spreadsheets and its conventional doings with arrays…
    Excel, when doing interactions with spreadsheet ranges and arrays, has a general convention of considering spreadsheet range values in a 2 D array, the first dimension refers typically to the horizontal row, and the second dimension refers typically to the vertical column. ( Conventionally/ usually the first row and first column is taken as indicia 1, (it does not necessarily always have to be when talking about the arrays associated with a 2 D range Area ) ).
    So pseudo like
    Arr(row,column) = {1 To n1, 1 To n2}
    As OptionBase1 said, conventionally the first row and first column is taken as indicia 1. Note this is when referencing cells in an entire spreadsheet- There is a small subtlety there ref!!. We are talking there about referencing spreadsheet cells, which is actually a relative thing as explained in ref!!

    **Also you need to be a bit careful when taking in or pasting out an array into a spreadsheet since its handled a bit differently as regard indicia:
    Excel will conventionally make an array filled from a spreadsheet as starting at first and second dimension ( pseudo “row” and “column” ) indicia of 1 , regardless of where the range is.
    The other way around is similar , but a bit different. It doesn’t matter where your indicia start. If you paste your 2D array out into a spreadsheet, it starts always at top left of the range where you paste it to: Top left is top left regardless of what you have chosen to be the start indicia of the 2D array that you are pasting out.

    Now, .. How a spreadsheet handles 1 D arrays / relevance to this Thread
    _..It seems that a lot of computer things in general that give out arrays of 1 dimension, often have their first integer indicia thing set at 0, at least by default, ref##
    I suppose technically speaking a 1 D array has no orientation, but some of us often think conveniently in our human minds of it as looking “horizontal” or like in a row. Excel goes along with that way of thinking sometimes, and in many things to do with a spreadsheet , if you pass a 1 D array, (even one with a first indicia thing set at 0 or any other value ) , then it does the same as it would if you gave it a 2 D array where the first dimension had a dimension of 1, ( Note: I said dimension of 1 there, not indicia ) . Saying the same thing in more simple, (not necessarily perfectly technically correct ) terms: In spreadsheet interactions, Excel will often take a 1 D array as if it was a 2 D , 1 “row” array, or 2D 1 “horizontal” array,
    Pseudo {0 to n2} taken as if it were {1 To 1, 1 To n2+1},
    pseudo {0 to n2} taken as if it were {7 To 7, 1 To n2+1}
    pseudo {0 to n2} taken as if it were {9 To 9, 65 To n2+65}
    pseudo {0 to n2} taken as if it were {8 To 8, 0 To n2 }
    etc.. etc..
    ( using n2 rather than n is just a personal convention of mine. It helps me keep my brain orientated in the perception that its referring to something to do with a column number in a row, and it either is associated with , or is pseudo equivalent to, a second dimension in a 2D array )
    ( Unfortunately Excel VBA has some String functions that came from VB which insist on a 1 D array: That can be a Bummer – since those functions wont take any of those 2 D , 1 “row” arrays instead )

    Coming back to the relevance to the OPs issue, or similar issues…

    In simple terms, if you had a 1 D array with values in it, ( even one starting at indicia 0 ), which you wanted to paste as a row in consecutive cells into a spreadsheet range, then you can do it directly in a single code line, pseudo like

    ___MyRnge = My1DArray()

    The code snippet below perhaps explains it a bit better, if you run it from the VB Editor in step ( key F8 ) mode .
    Sub OneD_TwoD_Arrays()
    Rem 0 worksheets info, use arbritrary worksheet for demos..
    Dim Ws As Worksheet: Set Ws = ActiveSheet  '   You don't have to do this. You can forget it and miss out all the  Ws.  bits   But that can catch you out sometimes, when you might end up referrencing a range in a different worksheet to that which you thought you were
    Rem 1 make arbritrary  1 D array
    '1_a) long way
    Dim arr1D_a(0 To 4) As String
     Let arr1D_a(0) = "BigJob": arr1D_a(1) = "Baveria": arr1D_a(2) = "Computer": arr1D_a(3) = "OldOne": arr1D_a(4) = "42"
    '1_b) quick way using  VBA Array function ,
    Dim arr1D_b() As Variant      ' The array needs to be dynamic and needs to have  Variant  type, because....._   _.. the next code line function  returns  a field of elements   which are housed in  elements of Variant type ,  so the type must be  Variant  regardles of what types are withing the elements or we will get type mismatch errors , and  _.. the working of the function is such that it sends infomation to size the thing taking it: If we have a fixed size array as in  _b  then Excel will error becuse Excel will insist on wanting to do this sizing of any recieving array, which it can't do if the array is fixed size.
     Let arr1D_b() = Array("BigJob", "Baveria", "Computer", "OldOne", "42") '   This returns us a 5 element array starting at indicia  0     So we have now  arr1D1_b(0) = "BigJob": arr1D_b(1) = "Baveria": arr1D_b(2) = "Computer": arr1D_b(3) = "OldOne": arr1D_b(4) = "42"
    Rem 2 paste arrays into spreadsheet
     Let Ws.Range("C1:G1").Value = arr1D_a()      ' Top left of where the 5 column, single row is pasted is cell  C1
     Let Ws.Range("D2:H2").Value = arr1D_b()      ' Top left of where the 5 column, single row is pasted is cell  D2
    Rem 3 Excel Spreadsheet array interaction:
    '3a) usually Excel makes a 2D array from a range with indicia starting at (1, 1)
    Dim arr2D1Row_a() As Variant   '  In the next code line, I am uisng the  .Value  property applied to a multi cell rectangular single area range. When i do that,  .Value  is programmed to return me an array of values which as far as it knows might be number values or text values,  or formulas or blanks or vb errors.  To acccomadte the possibility of us distinguisching later what type we have, all elelments in the returned array are  Variant  because only  Variant  can  "hold"  all the different types in it. (a number can go in a number type variable, (Single  Double  Long  Integer) or a  Variant  type variable; a text can go in a  String  type or a  Variant  type etc. etc).    So, because Excel is programmed to return an array of Variant type Elements, I must decalre my array, arr2D1Row_a() , as variant types, or else i will get a type mismatch error at the next code line
     Let arr2D1Row_a() = Ws.Range("D2:H2").Value  '  Excel decides the array type in this situation. It will always be a 2D 1 "row" array  {1 To 1, 1 To 5}  Nothing you can do will influence this
    '3b) When pasting out, top left is always top left of the range you specify, Excel doesn't care what indicia you've got in the array being pasted out
     Let Ws.Range("E3:I3").Value = arr2D1Row_a()  ' Top left of where the 5 column, single row is pasted is cell  E3
    Dim arr2D1Row_b(7 To 7, 42 To 47) As String   '  A 2D 1 "row" array with arbritrary inidia: I can decide this but ...._ Excel doesn't care what indicia you have chosen when pasting out
     Let arr2D1Row_b(7, 42) = "BigJob": arr2D1Row_b(7, 43) = "Baveria": arr2D1Row_b(7, 44) = "Computer": arr2D1Row_b(7, 45) = "OldOne": arr2D1Row_b(7, 46) = "42"
     Let Ws.Range("F4:J4").Value = arr2D1Row_b()  '  Top left is always top left, cell F4 in this case.  ...._ Excel doesn't care what indicia you have chosen when pasting out
    End Sub


    ref## In VBA, you can change the typical start indicia of 1 dimensional producing things from the default 0 to 1
    One way to do that is to write the Username of a certain VB forums member at the top of your code module like this
    In the case of the macro example above, that will make no change what so ever on the results.
    There is a small change to the workings: It is in the code line that uses the VBA Array function,
    arr1D_b() = Array("BigJob", "Baveria", "Computer", "OldOne", "42")
    Previously it gave me this:
    arr1D1_b(0) = "BigJob": arr1D_b(1) = "Baveria": arr1D_b(2) = "Computer": arr1D_b(3) = "OldOne": arr1D_b(4) = "42"

    Now it gives me this:
    arr1D1_b(1) = "BigJob": arr1D_b(2) = "Baveria": arr1D_b(3) = "Computer": arr1D_b(4) = "OldOne": arr1D_b(5) = "42"

    ( I get that Watch Window if I highlight any of the arr1D_b() bits of thee coding and then hit keys Shift+F9.
    Having done that, then after I have stepped past the code line that fills that array, the contents will be shown in that Watch Window after hitting that + symbol

    **There is no effect on any of the pasting out done in the macro, because top left is always top left of the range you specify, and Excel doesn’t care what indicia you’ve got in the array being pasted out, ( be it a 1 D array , or even a wildly indicied 2 D array, as in the last example in the macro )

    ref !! It is a bit more complicated if you are referencing a range somewhere in a spreadsheet, since then you can “go backwards from the origin”. Like
    Range("B10").Cells(0, 0) is like Range("A9")
    Range("B10").Cells(-1, 0) is like Range("A8").
    Something like,
    Range("B10").Cells(0, -1)
    will give you your Error 1004 - Application-defined or object-defined error VBA Excel. In this example its because you are trying to go 2 columns back from column B
    Last edited by Doc.AElstein; Today at 05:40 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width