# Off Topic > The Water Cooler >  >  KISS Principle

## EssoExplJoe

Just a word of advice to some beginners programming with VBA and Excel.  Always remember the KISS principle as someday you will likely have to come back to your coding and "unravel it" to add/delete features.  Most of the stuff I have seen on these forums are poorly organized and/or overly complicated.  There are some very bright gurus on these forums who use very complex and long statements to save a bit of typing shorter more easily understandable code.  I have been programming for 45 years off and on having started out with Fortran and assembler on the old IBM 360's at Pratt and Whitney Research center in Florida and I can still understand the old code I wrote.

----------


## MrShorty

There is value in this. One of the comment statements in something my mentor coded (also in Fortran) noted something he did, admitted that this could slow execution down, but he preferred this structure because he felt it was easier to control what was going on. Easier to control made it easier to program, easier to debug, and easier to edit, and easier change and adapt to new situations that would arise later.

----------


## shg

You'll find that some people post code intended to be expository, some intended to be code golf, and some that falls somewhere between bad and willfully obfuscated. It's the nature of a free forum.

----------


## JBeaucaire

> There are some very bright gurus on these forums who use very complex and long statements to save a bit of typing shorter more easily understandable code.



Although I agree with the overall sentiment, my experience leads to the opposite observation to the statement above.   I see some VERY capable experts who provide very SHORT vba code that is by its brevity almost impossible to read by any sort of beginner to decipher, at all.   These experts use BRIEF code to avoid longer more detailed expository coding or variable naming.

I used to be the horridly long-winded coder sort, so I get the original idea above, too.  Now that I getting more versed at the "short techniques"  I am trying to force myself to still use longer-variable names where appropriate to keep from obfuscating the whole thing.

On the other hand, now that I do understand it more, it is cool too look at a short routine with a bunch of single-letter variables and know what it does.  :Wink: 

Anyway, my REAL comment here is _"Whether you use short array programming style or more lengthy range-based programming style, please always remember to COMMENT your code, that's the greatest gift to the self-learners out there using the codes they find here."_

----------


## Doc.AElstein

Hi, 
 So I had to Google *KISS* Principle first. Then later I had to Google what *expository* and *obfuscated* meant. What shg  means by *code golf*  is still a mystery to me  :Confused: 

* There* lies the crux of the problem IMO. 

 What is Simple to one is complicated to another. 
_ A beginner may find a short code with a few very clever code lines very hard to understand and complicated, ( at least if he is interested in learning and in understanding the code )
_  An experienced programmer would often find a code “opened up” to include many in between steps along with explaining comments very irritating and in fact often such a professional  can grasp and understand quicker those short compressed more clever code lines

  ( BTW. , ( By the way ) , I agree with Jerry. Most of the cleverer helpers provide shorter code that is less comprehensible to a beginner. )


 From my experience, if there is a good solution to the above dilemma, then it unfortunately takes a lot more time. ####

_..............

 I am probably the world’s greatest use of ‘Comments in VBA Code. I take full advantage that there is a large amount of text space going off to the right ( I think about 1023 characters !!! ) in the code window, which is only visible if you scroll to the right. I will typically cram  as much information as possible, starting off with the most useful and relevant to the code line, and then going off “ Hidden to the right “ I will have  the more background theory and / or URL Links to where more detailed explanation can be found. 
 storage is very cheap and likely to get increasingly cheaper. As far as I know, comment lines will not affect execution time?  
As much of the ‘Comments are only visible on scrolling to the right, then it does not necessarily make the code too messy and put people off viewing it. Never the less the extra code lines and  longer variable names  are considered complicated or at least annoying by an expert. There could also be an increase in execution time when using extra variables when “opening up” a code, compared to substituting there values in. 

 The solution I believe that does come close to satisfying all is the following. Give 2 code versions. The first the efficient bare bones version. 

 Then under, ( which can also be a bit hidden by having to scroll down in a Forum Code Window) , you can give a Full version.

_ .

just for Water Cooler Fun: A code to show the length of a comment line, first the simplified compact form here, with the minimum of ‘Comments 



```
Please Login or Register  to view this content.
```



_.  Now if you have the time and  some migraine tablets to hand, the following posts are what I needed to “ open up” and explain the code fully. 
http://www.excelforum.com/showthread...t=#post4513461
http://www.excelforum.com/showthread...t=#post4513463
http://www.excelforum.com/showthread...t=#post4513472
Admittedly I have done an extreme example to emphasise the points that I have been making. The above 3  posts just about give enough  information for anyone  to fully understand everything. But to be quite honest, even though I wrote it myself, the sight  of it scares me and puts me off reading it. 
 :Roll Eyes (Sarcastic): 

_ So you see the dilemma. It mostly comes down to a time factor. #### You will likely take the simplified code initially, and hope to have time to understand it later. At least with my suggested way all the info is there. 
_ But expecting a volunteer helper to take the time to present so much info, can be asking a lot, especially as many answer and help so many people. 

_ #### My last theory and thought to that would be, that if this time was taken, then eventually, the increased knowledge imparted would reduce the number of posts,  both as a Fuller answer would likely satisfy better a google search as well as making it more likely that OPs could learn and so have a better chance of answering more of their problems themselves


*Alan*






*EDIT* - I sussed it - here you go - "Ki_SS_"
http://listenonrepeat.com/watch/?v=e...roll_all_night!!!

Have not sussed  the "expositoryobfuscated" yet - guess that may be a foreign Band? ( or another Form of ' Explicit pedantacisms ? in code golfs ?

----------


## JBeaucaire

> I am probably the world’s greatest use of ‘Comments'



I'm sorry, Doc, that should be your signature, a bit on-the-nose.

 :Cool:  :Smilie:  :Roll Eyes (Sarcastic):

----------


## xladept

The less moving parts the less that can go wrong.  "if it takes a long time, you're probably doing it the wrong way" etc etc
Sometimes I actually believe that the code is self-explanatory :Smilie:

----------


## tim201110

i do not think this principle for what you described
for example, passing a range to an array for calculations is much faster than the following: For each cell in Rrange: cell.value some code: next cell
but it not easy understandable and shorter
the same with: On Error resume next for adding an item to a collection

----------


## shg

> What shg  means by *code golf*  is still a mystery to me



Google is your friend:

https://en.wikipedia.org/wiki/Code_golf

http://codegolf.stackexchange.com/qu...gged/code-golf

----------


## tim201110

logic is better
i knew golf rules and guessed right  :Smilie:

----------


## xladept

Hi Tim,

Passing the range to an array is making the routine use "less moving parts" as the external I/O is eliminated :Smilie: 

And the code is virtually the same. :Smilie:

----------


## tim201110

Hi, xladept,
I think the parallel to mechanics is not from here.
Passing the range to an array is a built-in VBA feature. The higher level of a programmer the deeper he knows a language.
Even in mechanics using a pulley block is more effective (this is the point) than just pulling a rope.  :Smilie:

----------


## Doc.AElstein

@ shg



> Google is your friend:....



Hi shg,
Big Brother Mind Killing Personality Stealing Brain Washing Brain Killing Computer Virusing Memory Stealing Google *is no friend of Mine*. But I confess  I live with and use him sometimes,  so I should have got the computer golf stuff. I would have thought with Object Orientated Programming type  stuff it might be a bit difficult to Judge what is the shortest ... god knows what is going on behind it


As for  expositoryobfuscated .  I am a Rock Drummer, so I expect I will get to hear of them eventually  Sounds like a Death Metal Band  theyre not usually on my personal Play list or covered songs    Lol.....
Actually, Only joking here   I did get Expository and Obfuscated from my friend Google. I find your choice of words often as obfuscation, at least I have since I learnt what obfuscation meant, 
 :Roll Eyes (Sarcastic): 
so it is probably just down to my ignorance, sadly.  :Frown:  

_....

As for the other stuff, I still think if you have time two codes are handy a full one littered with extra variables to help debug and full use of the hidden area to the right to write a book of explanations and references.  I think that can get close to pleasing everyone. I am quite proud of some of the One liner codes I have managed in my condensed SHimpfGlified code versions  :Wink:   that is one interesting aspect of the Object Orientated Programming that VBA isnt

_....

@ tim201110
I have not quite been able to follow what you are saying.  But my programmer level is about as low as you can get. I can say one definite conclusion from my short Computer career.  In many cases you are left to experiment to find out what is likely the most efficient or easiest, through things like  time comparison experiments for different codes to do the same thing. And sometimes an idea or theory about what is actually going on from one person is as good as a different one from someone else. Often we are left with interacting with some interface and exactly what is behind it was written by god knows who god knows when and he / she  forgot and never documented it clearly enough or in a way anyone or even himself could understand later..
You see it all the time with the best experts getting the Hump sometimes trying to explain something clearly that they should know. They really just do not know even if they once did. Thankfully there are some exceptions in those people and thankfully they are sometimes active in Forums like these. ....  

Also you have to accept that on a free Forum people have the right to post as they choose, or indeed have time to  and some will prefer to help as many people as possible to get a final solution rather than helping a smaller number to understand fully the code they give. Sadly, in my opinion, a lot of OPs just want their work done and are not interested in learning. 


*FWIW ( For What Its Worth ) Regarding Arrays versus Spreadsheet Interaction Type interactions Codes*
I think the Spreadsheet Interaction Type interactions Codes are definitely *initially* a lot easier to understand than Arrays, but only *initially*. As Orrin suggested the code can be written to look very similar. For example, if you separate an  Array code into a section that does the capturing of the data initially, a middle part that does the work , and a last part that does the pasting out to the spreadsheet of the Final array with results in, then that middle part can be made to look very similar to a Spreadsheet Interaction Type interactions Code . If one does this then the understanding of an Array code comes very quickly. If you get used to using the Watch Windows to expose the contents of an Array then you get quite close to seeing as much as in a Spreadsheet when debugging and working inn Debug ( F8 ) mode, - but it can be a bit difficult for very large data. If I have time I often do a Spreadsheet Interaction Type interactions Codes and Array type code, trying to keep them as similar as possible.

Certainly all my experiments suggest Array work, especially with large one dimensional ones is very efficient, but bear in mind that some Worksheet Functions appear much more efficient when working on a Spreadsheet range rather than an Array  just written that way I guess.
http://www.excelforum.com/tips-and-t...ml#post4380613




*Alan
*

----------


## xladept

Hi Doc,

I've yet to learn to use worksheet functions out of the worksheet.  But, if you code the initial routine using the Cells method, then the transition to the worksheet array is super simple.

Writing this routine, wherever the "i' is now was the "Cells" :




```
Please Login or Register  to view this content.
```

----------


## snb

@xladept

What does your code illustrate ? (apart from .... ???)




```
Please Login or Register  to view this content.
```

----------


## xladept

Hi snb,

I was just showing that the transition from the Cells method to a worksheet array is a simple matter.

At least that's what I thought I was doing :Smilie: 

When I was pondering using a WorksheetFunction in an array, I thought of you ( and shg).

Can it be done?  I've always written the calculation either straight in the code or with an UDF.

----------


## Doc.AElstein

:Confused:   :Roll Eyes (Sarcastic): 
Hi
I almost did not get it at first, silly me.
Thanks for the examples of obfuscation
 :Wink: 
( At least I am totally Obfuscated by what you are both doing !  :Frown:  )
*Alan*

----------


## snb

Hi @xladept

I did some exploring here: http://www.snb-vba.eu/VBA_Arrays_en.html#L_6.16.3 

ciao

----------


## EssoExplJoe

The use of Arrays is the simplest way to go if you have ever programmed VB.Net or Fortran and reading a range into a variant array, manipulating the array, then writing back to a range is pretty simple and just as easy to read than the for each cell

----------


## Doc.AElstein

> .... if you separate an  Array code into a section that does the capturing of the data initially, a middle part that does the work , and a last part that does the pasting out to the spreadsheet of the Final array with results in, then that middle part can be made to look very similar to a Spreadsheet Interaction Type interactions Code . If one does this then the understanding of an Array code comes very quickly. If you get used to using the Watch Windows to expose the contents of an Array then you get quite close to seeing as much as in a Spreadsheet when debugging and working inn Debug ( F8 ) mode,......







> ...... and reading a range into a variant array, manipulating the array, then writing back to a range is pretty simple and just as easy to read than the for each cell



_  Might just be worth noting that we are talking  about if you are only interested in working with the Values** in the cells. ( You can also distinguish between the 3 Values you see ( Value(argument1)  this is the default Value , Value(argument2) , Value(argument3)  , the underlining value2, the strings of Formulas in the Cells ( in the various string formula formats (   Formula,  FormulaLocal,   FormulaR1C1,  FormularR1C1Local,       FormulaArray)  )  
FormulaValuesProperties.JPG http://imgur.com/OULIRhE 
.ValueProperties (Method?)   http://www.excelforum.com/showthread...=8#post4519378 
 )	
_...
_... ____________

Anyways, how about a fairly simple non Obfuscated example: ( I am still Obfuscated by what Orrin and snb are playing about with ? )

Lets say I have a Simple range in a spreadsheet. It has formulas and simple values in it, 

*Values* (.Value(xlRangeValueDefault) or .Value )

Using Excel 2007 32 bit
Row\Col
A
B
C

*1*
2
2
TextRC  "

*2*
2
2



Worksheet: *RCFormulas*

-..............

*Formulas* (.Formula) as you typically see in the typical column letter and row number format

Using Excel 2007 32 bit
Row\Col
A
B
C

*1*
=$B1
=B$2
="TextRC  """

*2*
2
2



Worksheet: *RCFormulas*

_..............

*Formulas* (.FormulaR1C1) displayed in   the R C Notation Style
 http://www.excelforum.com/showthread...t=#post4518687 
https://smurfonspreadsheets.wordpres...r1c1-notation/
http://powerspreadsheets.com/r1c1-formular1c1-vba/

Using Excel 2007 32 bit
Row\Col
A
B
C

*1*
=R[0]C2
=R2C[0]
="TextRC  """

*2*
2
2



Worksheet: *RCFormulas*
Or in the more usually seen  shorthand version for when the unfixed relative offset bits from the cell where the formula is of 0
Using Excel 2007 32 bit
Row\Col
A
B
C

*1*
=RC2
=R2C
="TextRC  """

*2*
2
2



Worksheet: *RCFormulas*

_.......................

In the next post two simple codes will be done and explained that will put only the formulas  in another arbitrary range Offset from the Input Range by 1 row down and 5 columns across 
Using Excel 2007 32 bit
Row\Col
A
B
C
D
E
F
G
H

*1*
=$B1
=B$2
="TextRC  """






*2*
2
2



=$B1
=B$2
="TextRC  """

*3*










Worksheet: *RCFormulas*

_ ( by the Way, there are infinite ways to do this, - and most of them probably better, but I am just trying to do a version to demonstrate the possibility to see and understand the similarities in two codes to do the same thing, one of which uses _Array techniques_, and the other _Spreadsheet interaction techniques_ )

Continued in next Post ( _Spreadsheet interaction techniques_ ) _......

----------


## Doc.AElstein

_....Continued from last post.. now on to..  “_Spreadsheet interaction techniques_” 

*Spreadsheet interaction techniques code*
I will present  the  “_Spreadsheet interaction techniques_” code first, just because a Beginner would probably be a bit more familiar with it. But I actually did the “_Array techniques_” first so that when I did the “_Spreadsheet interaction techniques_” I would start with that and both modify and remove (    ‘Comment out   ) the extra few lines towards the start and end of the Code that you need only for the “_Array techniques_” code. ( Also ‘In the middle of both codes I leave the very similar lines in from both codes in for easier comparison. As appropriate the not needed lines are ‘Commented  ) 

*The code first*: 




```
Please Login or Register  to view this content.
```


*Now a “walk through it”.*

Rem 1 
It is always useful to use variables for things like Worksheet and Range Objects. That way things do not “go astray” , ( and in addition , once a variable object is declared, you can get help through intellisense when you type a  *.* after the variable. What you actually get is the drop down list of offered possible under Objects and Properties and Methods    
FormulaValuesProperties.JPG 
http://imgur.com/OULIRhE       )
If you declare Objects in a logical sequence then you can use the variables in further declarations as I do Using the declared Worksheet object variable in the code lines to 
50 Set a variable to the input and 
60 output Range Object
It is a convention to declare most variable in this first section. I personally sometimes declare some variable as I need them. I believe that the declarations are only read once at compile and so their position in code ( as long as they occur before the first use of the variable ) has no effect on code performance.

Rem 2)
This is the main looping through of each cell in the Input Range. 
I follow the typical convention of taking ... at  
170 __ every or our rows ... I ... go  
180 ____ along 
‘
‘ Middle Loop code section ----- - ‘
‘
250 ____ every column before going   
260 __ "down"   to the next row ...    

In the ‘ middle of this Loop code section   
190 we do a simple check based on the first character from the left in the String of the Cell content. If it is equal to “*=*” Then this will satisfy the condition that we have a Formula string in the Cell.( We must reference the .Formula ( or .FormulaR1C1 ) Property, rather than the value so that we get the Formula string rather than using .Value which would give us the value that the Formula returns for us to “see” in the Spreadsheet )
With the condition met, 
210 we put the Formula string into the  appropriate Cell of the Output Range.

It might be worth just spending a bit of time looking at the particular form of this line as I have chosen a particular way to do it to help make a simple comparison to the “_Array techniques_” code

Let rngOut.Cells(Rws, Clms).Value = rngIn.Cells(Rws, Clms).Formula

rngOut is our fixed Range Object Area of contiguous Cells which we Set arbitrarily to start top left at Cell F2. VBA has confusingly by its name a Range Property, or ( as we are using ) a Cells Property, which refers in its arguments, (Rws, Clms),  to the Cells going ( down , across ) starting from the top left of our Range Object rngOut. ( In fact VBA is a bit silly and allows us to specify Cells going ( down , across ) outside our Range Object boundaries . ** This is not the case in our “_Array techniques_” code )
We are using the Cells Property of our Range object to reference the appropriate cell in our Output Range. ( _....We could just as well of used the Cells Property of the Worksheet along with the Offset Property in such a code line, -..
 wsIn.Cells(Rws, Clms).Offset(1, 5) .Value = rngIn.Cells(Rws, Clms).Formula. 
_.. But I am trying to show a good comparison with the “_Array techniques_” code where I similarly “Pseudo” refer to the “Array’s Range” via
220 arrOut(  “Cells”(“down”, “across”) )
Or
220 arrOutMemberElement(“down”, “across”) 
_...)


I think that is probably it for the  “_Spreadsheet interaction techniques_”  _......

----------


## Doc.AElstein

_....Continued from last post .. now on to _ Array techniques_  code



*Array techniques  code*
*The Code:*




```
Please Login or Register  to view this content.
```


*The explanation of above Code*:

A quick look shows that it is virtually the same code. We noted we need a 
_(i)  extra bit at the start to put the Input data into an Array , 
_(iii) an extra bit at the end to paste a final Output Array out, 
_ (ii) and a minimum of minor changes  in the middle of the code ( in our case within the Middle Loop code section )

*Extra start bit*
*90   '1b) ".... Read input Range into a Variant Array*

The simple difference between a _Spreadsheet interaction techniques_  code and a _ Array techniques_  code is that we 
_(i) have all our data in an Array rather than a Spreadsheet,
_ (ii)  we work on that internally as it were in VBA. Rather than paste out as we go along to the Spreadsheet output Range, we put all our output in an Array for Outputting. 
_(iii) This Array is then pasted out, usually in one go towards  the end of the code 

One major advantage in such a code is speed as it is generally the case that an interaction with a spreadsheet slams the brakes on in a code run. But we would not have a great advantage if  *_(i) before* and *_(iii) after* our main code workings we  _(i) brought in our spreadsheet values and then_(iii) pasted out our Array values one at a time. 
At the heart of the Array code advantage is therefore that for the case of the various values  Properties, VBA allows us to assign in one code the values  from a Range directly to  the member Elements of an Array and vice versa allows  to assign the member Elements of an Array to the spreadsheet Cell values of a Range. I doubt many people are privy to exactly how this internally works , but experiments show clearly that the process is considerably quicker than looping and taking each value in one by one. 
The different .value Properties are therefore the way to refer to the values in a Range
http://www.excelforum.com/showthread...t=#post4519378

Applying such a Property to  or referring to that Property of a Range Object ( of more than one cell)  will  return a Field ( Array ) of Variant Element Member types . ( _.. Presumably this is done to allow not only for the different Number and String possibilities in the properties but to also allow for the possibility of Empty, which is a particular type of Variant to indicate an empty Cell
http://www.mrexcel.com/forum/excel-q...ml#post4411660  ..._...)
 because of this type a Variant type
100  Array must be declared to take the returned Field
We want in our Array the Formula string so as to be able to check for the *=* . Consequently we can use 
110 any of the .Formula  values , Properties. I choose .Formula for convenience to keep everything in the familiar column letter and row number notation convention

Our output Array is of known size and will be filled in the _(ii) Looping as an alternative to pasting out each value that meets the criteria to be included in the Output. In principal we could therefore
120 Declare our Output Array as String type. In most cases this is adequate and good programming practice. However there are some un answered oddities which occur when an entire Output Array containing Formula Strings  is applied to the values of a Range Object.
http://www.mrexcel.com/forum/excel-q...dinates-2.html
http://www.eileenslounge.com/viewtopic.php?f=30&t=22787
A work around seems to be to 
130 declare the Member Elements as Variant types 

_......................

*_(ii) Middle section Looping Minor modifications..*

Taking a look at the two minor differences in the two codes

190 200 The same basic criteria check is done. The arrIn() Member Elements
200  are referred to using exactly the same row and column Number variables which are used for the corresponding Cell Co ordinates in the Spreadsheet

Similarly the same row and column Number variables are used to refer to the corresponding positions within
210 the Output Range,  ( Left hand side of equation ) and
220 the Array for output ( left hand side of equation )
For the right hand side of each equation we get our formula string that has met the criteria from
210 the Input range, and
220 the Input Array 

Those are the only slight changes. 

( ** Note we may not go out of the dimensions of the Arrays with  our row and column  coordinate references )

_............................

_(iii) Output to Worksheet in one go
Rem 3)  This extra section of one 
300 code line simply pastes out as allowed the values in one go: The values of the Range are set to those values within the Output Array. It should be noted here that .Value and .Formula type values  appear to work in a more Method type way in that they will recognise Formulas in either the column letter and row number Notation or the R C type notation and assign correctly the properties to the Range.  The .FormulaR1C1  Property appears more fundamentally to be a property type reference and will only work when any present Formulas are in the R C notation
http://www.excelforum.com/showthread...78#post4521059

_...

There you go 
 :Smilie: 
Alan

P.s. Simplified code comparison in next Post.

----------


## Doc.AElstein

*Simplified code comparisons for codes discussed in last few posts
*




```
Please Login or Register  to view this content.
```

----------


## snb

@Alan:

FYI:




```
Please Login or Register  to view this content.
```





```
Please Login or Register  to view this content.
```


against the KISS principle: (oder: zuviel Aufwand)
- using 'Let'
- using step1 in a loop
- declaring a Variant
- using redim where VBA adjusts the size automatically when reading Range values into an array.

----------


## Doc.AElstein

Hi snb
_ I probably should have knocked those bits off my last SHimpfGlified codes. from Post #23 . I usually do for the last SHimpfGlified codes

_ I am not sure about in the other codes. I think whether adding extra unnecessary arguments, using named arguments, using arguments that are the default, and  explaining ‘Comments do not adhere to the KISS Principal . 
_ The KISS principal is given sometimes as just Keep It Simple, other times as keep it simple to Understand. If you leave a lot of explaining material out, then to understand you have to go off, read up, and probably give up and write the code again from scratch to understand it. That does not sound very simple to me. And wastes a lot of time. 
_ I think what Kiss means is slightly up to interpretation. 

_ Depends on ones intelligence level as well, or simple knowledge of code. 

_ I expect most people could follow my last few post and would understand what I am doing. Some experienced People would be irritated as they understand it all already. But they would understand, and the codes I did / do are clearly intended for the more novice level , so the more experienced should not necerssarily expect to get too much out of them.

_ I  see what you are doing. It is an interesting alternative. Using  the (2)  which is  Special cells Enumeration of 2 (  (XlCellType is xlCellTypeConstants) )  to be cleared from the copied Range leaving then the formulas.  Thanks for that. I would probably of done this ( and Dim ed variablrs, used variables for worksheets etc...)


```
Please Login or Register  to view this content.
```


That solved the Task a lot better. I was less concentrating on the Task, rather the _Array code type_/ _Spreadsheet code type_ Comparison. But it is great to see an alternative anyway...  :Smilie: 
_... The .HasFormula Property I see will return a True or False for whether the cell applied to it has a formula in it. A nice simple ways to solve the problem. And in curiosity I tried_....  
_Dim v As Variant
__Let v = Range("A1:A2").HasFormula
_.... and finally got to produce a Null as a special type of Variant to indicate it has been “used” but not yet assigned valid data. I was hoping I might finally come across seeing a Null produced one day  :Smilie: 

_ My codes are too much work to solve the problem.
_ But I stressed in my post I was comparing a Array type and Spreadsheet type Code and that  I deliberately chose a way to make the codes similar. 
But yours does show the simple way to achieve the final product. Yours is almost Kiss to get the final result.  :Smilie: 
Mine is Kiss to demonstrate the similarities there can be in Array type and Spreadsheet type Code, and getting the point across about the Extra Array capture and paste out necessary in the Array type code.   :Smilie: 

Thanks for the reply
Alan
 :Smilie: 

*Edit P.s*




> ...
> - using Redim where VBA adjusts the size automatically when reading Range values into an array.



I brought my Array in such that VBA adjusted the size automatically when reading Range values into an array. The ReDim is done on the  Output Array filled in  the Loop ( I realise an alternative would be just to assign directly the Output Array to the input Array if the types are similar, but The point I was particularly explaining an unsolved problem of mine about String and Variant types necessary in the output Array.. Otherwise the Output Array could have been String Type members....all in the ‘Comments  :Wink:   and explanations. I missed out though the bit about explaining in the 'Comments that the Array was necessarily Dynamic to be sized automatically when reading Range values into the array. I did emphasise the need for it to be a Variant type Member Element Array. You see I did not quite get the Kiss right – needed to add a bit more  :Wink:   :Smilie:  ( 'The Array must not be fixed. It must therefore be a Dynamic Array, as VBA adjusts the size automatically when reading Range values into an array )
You see I really must add a lot more ‘Comments and explanations. Thanks for the heads up about that  :Wink:   :Wink: 

P.s.2
Dear Oh dear, I really did mess this one up -- I am glad you popped by – I meant to add finally
_1 ) Put a stop at the end of the code. 
_2) Highlight any occurrence of arrIn() or ArrOut() in the VB Editor code Window, -- Hit *Shift+F9* ( -– click on the *+* to the left of the varuable name to open up   http://imgur.com/l8zJfmU  ) --   and display the contents of the Array in the Watch Window after a run, or during a run in F8 Debug Mode.
arrInOutWatchWindow2.JPG
http://imgur.com/biAjJys
http://imgur.com/wCYuEv3
arrInOutWatchWindow.JPG




Updated Codes
http://www.excelforum.com/showthread...22#post4529131
http://www.excelforum.com/showthread...22#post4529129

----------

