Assign NA value to an array??

[raven_squire]

Hello,

I am copying data to a VBA array, processing it a whole bunch and either coppying it back into to a worksheet or generating a chart directly from the array (I assume that is possible).

What is the best practice for a null or #N/A situituation?

I cant use My_Array(2, i) .Formula = "=NA()" is there something similar?

I cant avoid invalid data as each row of the array has an element that uses the previous row as a comparison, so row 0 is gaurenteed to have invalid data.

Thanks

 

[StephenCrump]

I cant avoid invalid data ....

I'd be looking at adjusting my formulae!

But also have a look at VBA's IsError function, to see if that works for you.

 

[raven_squire]

Thanks for the suggestion,

Unfortunatly it can't be avoided.

An example would be logging the time and temp and then the difference in temp from the last sampe. The very first line will never have another sample to work out the difference in temp so I need a way to manage it.



IsError is a test where I need to assign a value of some kind. So far I am using the text string "N/A" however I get the feeling this wont cut it as my project gets more complex. It would be good if excel knew that the value was an error.

 

[StephenCrump]

You can drill down into specific error values like this:

If IsError(My_Array(i, j)) Then
    If My_Array(i, j) = CVErr(xlErrNA) Then
        'N/A error
        'Do something
    Else
    
'...

Here are the error values: Cell Error Values

 

[raven_squire]

Thank you, that article is very helpfull. So I believe I can make the value in the array <code class="lang-vb hljs vbnet expanded">= CVErr(xlErrNA) if I do not have valid data to put in the array. This should allow Excel to recognise that there is no valid data.

I will have to check if it works later this afternoon.
</code>

 

[StephenCrump]

Yes, you can set MyArray(i,j) = CVErr(xlErrNA), and write #N/A errors back to Excel.

But sorry, I was under the impression you were testing MyArray(i,j) on the way in to VBA.

If I was writing MyArray back to Excel, #N/A error values would look to me like errors in the workbook.

I'd be using simple strings myself, e.g. "n/a" or "-" or even "".

 

[raven_squire]

I would normally use simple strings and they sound like an effective way to go in most cases.

My project gets a bit more complicated as I know there will be many instances when there is no valid data for various reasons and I am also dealing with a lot of data.

I whish to display all this data on a graph that is a bit more complicated than a standard bar graph so I thought that by using actual NA vaules it would mean that the graph behaves a lot better.


Thank you for your help, I couldn't find that info anywhere in google