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.

Please Login or Register  to view this content.

[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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

[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.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

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.