Hi,Could use some help with VBA. Would appreciate any inputSay I had a matrix in my worksheet from cell a1 to d4. I want my VBA program to retrieve that whole array and tret it like an object. What si the best way of doing this:1. Read off the values and write them to an array in my VBA program then ercopy the new values back into the sheet?2. Somehow get VBA to regonsie that whole block as a matrix object in which case any changes to the object are made directly to the sheet/How could I implemet the second?ThanksSam

how about:Sub mySub()dim myRange as Rangeset myRange=Range("A1 : D4")myRange(1,1)="hello"myRange(1,2)=8End Suband so on..

Thanks!This is what I was looking for but couldn't get the syntax!Sam

a pleasure!

Ok, I'm caught up with somethig else now...What is the syntax for comparing 2 cells to one dcimal place? If was comparing cells, then using the '=' symbol means that VBA will look for cells that are EXACTLY equal. Is there a way to specify that condition that the cells are equal to only one decimal place?Thanks Again

use the TRUNC function:=IF(TRUNC(A1,1)=TRUNC(B1,1),"Cells equal to 1dp","Cells not equal to 1dp")syntax for TRUNC is (Cellref, #dp)

well maybe you could help me with the stupidiest question ever...I have to deal with dynamic arrays for the 1st time but there's no big deal so farexcept that my functions with arrays as parameters return a #value! when I test them in excel...so I wrote this simple function to see what happens with dynamic arrays:Public Function test(ByRef x As Double) As Double'Dim arr() As Double'arr(1) = 1.2test = x '+ arr(1)End Functioneverything's fine in excel when I call test(A1), but when I drop the ' commentaries then there's this damn #value! ...it's my first days in VB, and honestly I have no idea about the problem this time... I feel quite stupidany hint?thkx

Sam - I realised that TRUNC is not available directly in VBA - but there's a much easier way that is:If Int(Cells(1,1)*10)=Int(Cells(1,2)*10) Thenetc

Brownian - even with a dynamic array, you need to dimension it at some point - the dynamic bit just means you can dimension it outside of the Dim statement by using Redim at a later stage:Public Function test(ByRef x As Double) As DoubleDim arr() As DoubleRedim arr(1)arr(1) = 1.2test = x + arr(1)End Functionthis should now work - of course the real benefit comes when you need to dimension the array based on a variable. You can't type Dim arr(y), but you can use Dim arr() then Redim arr(y)

Yukathanks a lot, but I realized it a few minutes after my post...in fact, as I said it was only a test function for a more general problem...I was looking for a relation between a range of cells and an array:is it possible to create a function with a dynamic array as parameter, and use it directly in a cell of excel with a range of cells as parameters,I mean:function test(ByRef MyArray() as double) as doubleand in excel, in any cell:=test(A1:A5)how will I be able to Redim my array? will I have to count with a loop the number of cells first?In fact, I would like to avoid any loop in my code, since the purpose is to use (a) dll function(s) to "accelerate the code"...Well, there are many questions in fact...thanks for your help

if you want to do the second approach,one important aspet should be kept in mind VBA and EXCel sheet are two different animals.Now to do the second step,do the following,1)define your matrix as a range ( give some name)2) define a range object in a macro (vba part)dim abc as rangenow use this to retrieve the matrix contents and copy them to a local matrix.3) now write onemore line to publish a new content from VBA.whenever you change some numbers and want to reflect it on the sheet,write a selectionchange event.if you want actual code, for reading the matrix content , i can pass you the excel sheet wrote by mesincerelychandra

Have you read this?http://support.microsoft.com/default.as ... US;Q177991&

thanks allone more thing if you don't mind...I've read you can do this to fill an array from an excel range of cells:MyArray = Range("A1:A5")But it does not seem to work if my range is a parameter of my function, not explicitely in a R1D1 format...Well, this is my code:*******************************************Public Function wrap(ByRef rang As range) As IntegerDim MyArray() As DoubleDim i As IntegerDim count As Integercount = Application.WorksheetFunction.Count(rang) ReDim MyArray(1 To count) 'MyArray = range(rang) 'wrap = test(MyArray) this is another function which has an array as parameter wrap = realEnd Function*******************************************the bold line doesn't work, unless if I explicitely define my range as a "A1:A5" format, which I don't want to do...any idea how to pass a range by ref?thanks a lot

brownian,how about:MyArray = rang

Brownian,In your code:MyArray = Range(rang)The argument to the Range function is an address or the name of a named range rather than another range object. It gets confusing because there are so many different ways to use the Range object (and it's so important).If you actually want a copy of the entire Range object (or rather another reference to the same object) then you should use:Set MyRange = rangIf you actually only want the contents of the range as an array of data then use:MyArray = rang.ValueAs pointed out in a previous postMyArrat = rangwill give you exactly the same, as Value is the default property of the Range object.