Range in Variables?

[zerowalker]

I got help here before with my script, but i am still tuning it, and i have a simple question i think, how can i prevent writing to cells, and instead keep everything in variables?

Range("K11:L11").Value = .LinEst(rng1y, rng1x)

There is an example, i would like to write the result in 2 variables, and not K11, L11.

I have tried many things, but i canīt figure out how to do it.

I was thinking that rng1y and rng1x should work, i mean, it writes from them.

But i canīt use them for some reason, not sure why. Maybe i need to convert them or something.

[Solus Rankin]

That method returns an array. The variables would have to be declared as such.

[zerowalker]

Sadly that doesnīt tell me much, what can i do?


The results i want to have, are in numbers.
And i use later in the script functions that will use them (and if they are not number, it will not work.)

[AB33]

If you syntax is correct, you can store in to variable.

sub test
k As Variant
  With ActiveSheet
   k = .LinEst(rng1y, rng1x)
  End With
End Sub

[zerowalker]

The problem in that code there, is, K will store both results, right?

I need to have Linest(A,B) separate.

Or can i choose which value i want to use from K?

[Norie]

Have a wee shifty here http://www.ozgrid.com/forum/showthread.php?t=33544.

[zerowalker]

I read that, but i still donīt get how i can get the results.

[Solus Rankin]

I'm confused as to what you mean by LinEst(A,B) separate. rng1y and rng1x are two arguments passed to it not returned from it.

[zerowalker]

Well i am bad at this, but will give an example.


Letīs say i do this: Range("K11:L11").Value = .LinEst(rng1y, rng1x)

Now i will get 2 results, one written in K11 and the other in L11.

so K11 is for example , 11.245, and L11 is 23.55


Now what i want is to not write it in any cell, as i have no reason to do so.
I would rather have it in 2 variables.

so x = 11.24 and y = 23.55.

Hope that explains it.

[MrShorty]

Perhaps you can explain what you mean by "2 variables"? The statement:

dim k as variant
...
k = .LinEst(rng1y, rng1x)

stores the array returned by the linest function into k. Recognizing that k is now an array (or perhaps, more accurately, a variant containing an array), we can use array indices to access the different values in k. Assuming our linest regression is a simple straight line y=mx+b, k(1) contains m, k(2) contains b.

If this is different enough for you, you can simply leave it at that. If you really want two different variable names, you could simply have a couple of statements after the linest results are stored in k

m=k(1)
b=k(2)

[zerowalker]

Great that was the thing i was looking for!

Variable(1)
Variable(2)

So i can choose the result.

Then i donīt need to separate Variables either!

Sadly, i am at another problem now thanks to my lack of knowledge.

Public Sub UserForm_initialize()


    Dim rng1x       As Range
    Dim rng1y       As Range
    Dim rng2x       As Range
    Dim rng2y       As Range
     Dim rng3x       As Range
    Dim rng3y       As Range

Dim v1 As Variant, v2 As Variant, v3 As Variant


    Set rng1x = Range("A2", Range("A2").End(xlDown))
    Set rng1y = Range("C2", Range("C2").End(xlDown))

    Set rng2x = Range("G2", Range("G2").End(xlDown))
    Set rng2y = Range("H2", Range("H2").End(xlDown))
    
    Set rng3x = Range("A2", Range("A2").End(xlDown))
    Set rng3y = Range("B2", Range("B2").End(xlDown))


      With WorksheetFunction
        v1 = .LinEst(rng1y, rng1x)
        v2 = .LinEst(rng2y, rng2x)
        v3 = .LinEst(rng3y, rng3x)

FPS1 = (1000 / 12) / (10000000 / 333333)
FPS2 = (1000 / Cells(11, 11)) * (10000000 / 333333)
FPS3 = Cells(12, 13) * 48000

 End With
 

fps = 10000000 / 333333
fpsu = FPS1 * fps
bit = 2
s = 48000
Label1.Caption = "Correct Factor " & Cells(15, 11)


TextBox4.Value = v1(2) - v2(2)
TextBox6.Value = v1(2)
TextBox7.Value = v2(2)
ReadData
End Sub

Public Sub ReadData()

FPS1 = (1000 / Cells(11, 11)) / (10000000 / 333333)
FPS2 = (1000 / Cells(11, 11)) * (10000000 / 333333)
FPS3 = v1(1) * 48000

Here is an example from my code, i dinīt copy everything as it didnīt matter.

But if if you look at the other sub (ReadData), i am trying to use the v1 variable from the other (UserForm_initialize) sub.
But it doesnīt work, i guess you canīt get the other variables from other subs just like that.
I currently have done some very bad shortcuts to solved this, but it has worked.

But now i canīt do anything, so i would like to know what i am supposed to do to let it use the variable.

Thanks

[Solus Rankin]

Zerowalker,

If you original question is answered please mark thread as solved, and ask any further questions in a new post.


To use the v1 variable in both subs you'll have to make it a public variable by diming it outside a procedure. If declared inside a procedure vba deletes the value when the procedure has run.