Input/output values into/from an array

I have a long list of values, a few thousand lines, that I need to input into an array so I can run a formula on the values in the array and then take those results and put them into another array which I can then call to output down a different column. Is there a faster way to accomplish this than the typical For Loop with the activecell.value and activecell.offset commands, that just takes forever.

This is currently my output code loop:

Please Login or Register  to view this content.

Hello Brane Ded,

Welcome to the Forum!

To make your posts easier to read, copy, and edit please wrap your code. I did it for you this time. Here is how you can do it next time.

How to wrap your Code
1. Select all your code using the mouse.
2. Click on the # icon on the toolbar in the Message window. This will automatically wrap the text you selected with the proper Code tags to create a Code Window in your post.
3. To do it manually...
Use the Bulletin Board Code Tags
[code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] at the end of the last line.


To learn more about BB codes used in this forum, just click on the link below...

Bulletin Board Codes Tags

Not very clear but you can use a Range to fill an Array if that's your question ?

Please Login or Register  to view this content.

The above will by default be a 2-dimensional array ... if the source is say a vertical vector and you prefer a single dimension then Transpose the range

Please Login or Register  to view this content.

If the vector is horizontal transpose twice for single dimension array.

It is the 1 dimensional array I'm using but that code did not add the ranged values to an array that I can call each value one by one

I'm afraid the above makes little sense to me. You can pass the entire range to the Array and iterate the Array as you would any other. However that said I confess there's quite a bit I don't follow in your code so it's hard for me to pass much comment.

Dim i, n As Integer, D_Range, O_Range As Range, U_max As Single
Dim O_Array(10000), U_Max_Array(10000) As Single

In the above: n is declared as an Integer, O_Range as a Range, U_max As Single, U_Max_Array as a single Array ... all other variables are declared as Variants. In VBA you must explicitly declare each variable... eg

Please Login or Register  to view this content.

VBA is not option explicit by default (XL is not designed for programmers per se) and as such any variable not explicitly declared will default to a Variant and Variants can hold any value so your code will work without issue. Use Option Explicit at the head of your module if you prefer to check declarations etc...

My next point would be you Dim your Arrays as having 10001 entries, then establish a count against a (smaller) range iterate & populate the Array.
The nature of the iteration would imply the numbers are in sequential order in the O_range.
Based on the above you would be far better served (IMO) leaving the size of the dimension unspecified initially (as Variant) and populate based on the count

Please Login or Register  to view this content.

In all honesty I can't really follow the code after that but it's late and I'm tired so perhaps a few further reads and the penny will drop at which point if I can I will pass further comment (if desired).

It work with "Option Explicit" on by default. Setting O_Array as a variant and using the application.transpose section worked to inout the values into an array. I cannot seem to output them properly in a similar manner though.

Sorry, yes, I should have been clearer... the variables are declared (so will not debug) but they're not being declared quite how you think... if you bring up the Locals window when you iterate your code you will see how i for ex. is being initialised as a Variant rather than an Integer (as I suspect you suspect it is).

I will look into this thread again tomorrow in regard to your output question - if not resolved in the meantime by others.

If you're able to post a watered down version of your file (in terms of data quantity) but which outlines what it is you're working with and the desired output this will invariably help - as is we're trying to visualise which given our poor imagination can lead to difficulties! A file generally speaks a dozen posts.

Try this:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

The dot means that the reference is qualified by the reference in the With statement, equivalent to

Please Login or Register  to view this content.

Please Login or Register  to view this content.

refers to the last cell in Col D; End(xlup) is the equivalent of doing Ctrl+Up from that cell in the user interface.

is it possible to replace "Rectangular" with something to default the process to the currently open sheet?

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Odd problem just occured, if I run the macro by holding down the F8 key it works perfectly but if I run the program either by hitting the green triangle play button or the excel run button all my output values end up wrong. They all end up as the same value when they are all supposed to be different. I tried starting over and it happened the same way, tried restarting the computer and still messes up. What just happened?

*Edit* The error only appears to happen if I click in the vba editor after hitting the green play button. The entire process speeds up significantly as well.

Originally Posted by Brane Ded

I just realized that the max of my output values can also be negative and I need to be looking for the absolute values of my outputs. I tried to combine the worksheetfunction.max with some sort of abs variant but nothing I tried worked, what is the correct method to accomplishing this?

there are a few routes you could go down... the simplest perhaps (given size of real ranges) would be along lines of:

Please Login or Register  to view this content.

(else you're looking at running an Evaluate call with SUMPRODUCT / Array which could be quite slow)

In terms of improving overall performance I would strongly advise adding the below prior to the With statement

Please Login or Register  to view this content.

and subsequently (though not technically required) the below prior to End Sub

Please Login or Register  to view this content.

Deactivating the repaint should lead to a significant improvement in performance (not saying it's resolving underlying performance issues but it will lessen the pain to some extent).

On an aside you mention oddities... if you're running in ManualCalc mode you will want to enforce a calculation on the requisite ranges (ie all those impacted by alteration to B12)
Without knowing as to whether or not you have other Volatile functions in your file (and/or other formulae linked to B12 not incl. in sample) it's hard to know whether reverting to a Manual method with Range calculate will make a significant impact on performance or not.

hi all,

and subsequently (though not technically required) the below prior to End Sub

Please Login or Register  to view this content.

(post # 16)
DonkeyOte, I'm curious, for which version of Excel is the screenupdate reset "not technically required"?

Brane,
If you are using "application.transpose" your error of the same value being put into all cells could possibly (?) relate to Transpose's size/item limitation when transferring from VBA/memory to a spreadsheet range as discussed in KB177991 & in: http://www.excelforum.com/excel-prog...transpose.html
http://www.dailydoseofexcel.com/arch...nge-using-vba/

hth
Rob

Rob, I think you've shown me up [again]

I think now based on this: http://www.mrexcel.com/td0133.html that the default reset ceased post XL2000 ... luckily I always code a reset out of habit after all the same logic applied to other App settings like Events would of couse be horrendous!

Thanks for tripping me up though - I only like to give out so much false information on any one given day

(That said I'm off to do some testing... )

off topic a little, though with the screen updating. Ive found that xl 2007 will reset this back to true at the end of the procudure.
I ran this and didnot see any changes untill the last sub had finished running.

Please Login or Register  to view this content.

So from what i read in the link it seam microsoft may have gone back to how things wherer 2000 & prior.
I have only used 2007 so i couldnot compare to anythink.

At the risk of thread hijacking...

Thanks for the clarification DonkeyOte (& Dave for the example)
I've learnt something new for the day & I may have to get myself a copy of Excel 2000!
- over different projects, I've literally spent hours wondering why Excel won't respond to me and when debugging code I now often have a module of my personal.xls open in the VBE so I can easily run a wee RefreshAppSettings macro (I haven't gotten around to adding it to a button).

Yes, I agree the impact could be horrendous. I think that requiring it to be explicitly reset is good because it is then consistent with other app' level settings.



Rob

As I recall I believe DisplayAlerts is another App level that did/does reset by default.... I must try and find some definitive examples (or get someone who actually knows definitively to chip in!)