Results 1 to 13 of 13

Thread: Ranges in VBA

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jul 2008
    Posts
    28

    Ranges in VBA

    I am using the code below, but it will not pass through the function. Will range not take the concatenated values? How do you use a user defined range?
    Dim rowfind as Integer
    rowfind = 1
    Dim matrix As Range
    matrix = Range(Worksheets("PP").Range("M" & (rowfind + 10)), Worksheets("PP").Range("S" & (rowfind + 20)))

  2. #2
    Lively Member
    Join Date
    Jun 2008
    Location
    Chicago and Grand Rapids
    Posts
    95

    Re: Ranges in VBA

    close, you forgot one word.

    change
    Code:
    matrix = Range(Worksheets("PP").Range("M" & (rowfind + 10)), Worksheets("PP").Range("S" & (rowfind + 20)))
    to
    Code:
    set matrix = Range(Worksheets("PP").Range("M" & (rowfind + 10)), Worksheets("PP").Range("S" & (rowfind + 20)))
    this should fix your problem. when using objects (ranges are objects) you must use "set" when setting them equal to something.
    vw = Volkswagen
    gti = GTI
    R32 = R32
    vr6 = VR6 engine
    lets go racing

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Jul 2008
    Posts
    28

    Re: Ranges in VBA

    Thanks, that did the trick. Is there a way to copy or export the values from the watch window?

  4. #4
    Lively Member
    Join Date
    Jun 2008
    Location
    Chicago and Grand Rapids
    Posts
    95

    Re: Ranges in VBA

    well i am a little confused by this. is there a reason that you can't just copy the values from the actual ranges that the watch window is watching?
    vw = Volkswagen
    gti = GTI
    R32 = R32
    vr6 = VR6 engine
    lets go racing

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Jul 2008
    Posts
    28

    Re: Ranges in VBA

    Its creating the range behind the scenes, but no actual output to copy. The only output is to the watch window.

  6. #6
    Lively Member
    Join Date
    Jun 2008
    Location
    Chicago and Grand Rapids
    Posts
    95

    Re: Ranges in VBA

    i think the .Source property is the one you need here.

    If you want to get the value by the cell that it's referencing this will work:
    Code:
    Application.Watches(Workbooks("Book1").Sheets("Sheet1").Range("A3")).Source
    this would return whatever the watch window had for cell A3 (or an error if cell A3 was not being watched)

    if you want to copy all of the values in the watch window, i might do this (although there are a bunch of different possibilities, as is usually the case ):
    Code:
    Dim Temp As Variant
    Dim WatchValuesCollection As Collection
    Dim i As Long
    
    Set WatchValuesCollection = New Collection
    
    With Application.Watches
       For i = 0 To .Count - 1
          Temp = .Item(i).Source
          WatchValuesCollection.Add Temp
       Next i
    End With
    this will give you a collection (ordered by the order in which the cells were added to the watch window) containing all of the values, which you can loop through and write somewhere else (copy or export).

    hope this helps
    vw = Volkswagen
    gti = GTI
    R32 = R32
    vr6 = VR6 engine
    lets go racing

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Jul 2008
    Posts
    28

    Re: Ranges in VBA

    Could you use this to loop through a function? For example if I am going to run a function X times and want to aggregate the arrays from the runs?

  8. #8
    Lively Member
    Join Date
    Jun 2008
    Location
    Chicago and Grand Rapids
    Posts
    95

    Re: Ranges in VBA

    im a little confused, sorry. do you mean you have a function and each time you run it you want to create an array containing the values it produces from watch window? then when you are done you want to combine all of the arrays?
    vw = Volkswagen
    gti = GTI
    R32 = R32
    vr6 = VR6 engine
    lets go racing

  9. #9
    Lively Member
    Join Date
    Jun 2008
    Location
    Chicago and Grand Rapids
    Posts
    95

    Re: Ranges in VBA

    also, would the user be specifying beforehand how many times he/she was going to run this function, or the user just run it over and over (im wondering if certain variables would lose scope or not)

    where would you want to output this aggregated array?
    vw = Volkswagen
    gti = GTI
    R32 = R32
    vr6 = VR6 engine
    lets go racing

  10. #10

    Thread Starter
    Junior Member
    Join Date
    Jul 2008
    Posts
    28

    Re: Ranges in VBA

    Sorry this is a seperate question than the watch window. I have a function that fills an array. I want to run the function on say three sets of data where it would fill the array, then run the function again where the I would have an array that would be aggregge of the arrays (array1+array2+array3). I know I could rebuild my function as a macro and loop through, I just didnt know if I could simplify by having a loop that says run fx for count =1 to x and call on function(period) within the macro rather since the function is being run in the macro. In terms of losing scope I would redim (1 to max) if thats what you mean.

  11. #11
    Lively Member
    Join Date
    Jun 2008
    Location
    Chicago and Grand Rapids
    Posts
    95

    Re: Ranges in VBA

    by losing scope i meant if a user ran the function and filled the array and then the code was done, unless that array gets written somewhere, it would be removed from memory after all code finishes executing. so, you would have to write the values to a different range or sheet or something in order to aggregate them in the end.

    you could indeed call your routine in a for loop (i think). its a little hard for me to say how since i dont know exactly what you are doing, but it sounds to me like you could call your function to fill the array as many times as you need. then, after that loop is done, you could aggregate however many arrays you have just made before exiting the macro. depending on your preference and purpose, you could maybe just use one array, and simply add values to the existing ones each time you run the function to fill the array. it might go like this

    define your array

    redim it to the correct size

    run it on the first set
    add the second set to the first set
    add the third set to the array (already the first and second)

    run as many times as you need

    the question there would be how to specify how many times to run and on what ranges (maybe put your data sets in adjacent columns and loop through until an empty column is found)

    this may be more complicated than what you already have... who knows
    vw = Volkswagen
    gti = GTI
    R32 = R32
    vr6 = VR6 engine
    lets go racing

  12. #12

    Thread Starter
    Junior Member
    Join Date
    Jul 2008
    Posts
    28

    Re: Ranges in VBA

    Does it have to be physically written in the sheet or is there a way to write to an array that is accessible globally and can be called up in the function?

  13. #13
    Lively Member
    Join Date
    Jun 2008
    Location
    Chicago and Grand Rapids
    Posts
    95

    Re: Ranges in VBA

    i just did some testing and it seems like you can declare a global array and read/write to it as long that instance of Excel is open (i definately did not know that). So yes, you can declare a global array, use a function to fill it, and then access it in another function, even if code has completely stopped running in between.

    Is it getting close now?
    vw = Volkswagen
    gti = GTI
    R32 = R32
    vr6 = VR6 engine
    lets go racing

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