I have two questions:
1) I have have copied the code and used the makearray() formula that microsoft produce to be able to produce arrays where cells are non-adjacent to each other. I have left the code at the bottom of this message to be reviewed and adjusted for this question. The formula works fine, however when using the =makearray() formula it does not seem to recognise anything else except the cell address. So say I wanted to take the absolute value, by using the =ABS() fucntion, for one of the cells that I am selecting to be in my array, i get the error #VALUE! in each cell, of my resultant array. How should I can i get arround it so that formulas can be recognised in this makearray() forumla. I suspect I would need to adjust some of the VBA code, but was wondering how exactly would it need to be adjusted so that the function {=makearray(A1, ABS(B33), E12)} would work, without obviously producing another cell that already carries out the ABS(B33) in a separate cell, and then the makearray formula referencing that new cell.
2) I am using the makearray formula in the define name section (Insert>Name>Define). I have basically been calling a range of sorts, say "orange", which is comprised of 3 cells that are not adjacent to each other, and then using that range name in another formula. However this does not work, as I keep getting the error #REF!. Normally named ranges can be used in formulas using the =INDIRECT(orange) function, however when using this function, i still get the same error message. But this function does work when you go through the traditional method of highighting adjacent cells and naming that range and using that name in the indirect function, so that it references the named range. I think the reason that my formula is not working using the indirect function, is that it is not a named range (since it has been produced in the Insert>Name>define section) in the sense that it is not on the list of named ranges on the top left hand side of the screen which normally shows the address of the cell, but thats just my opinioni and is a stab in the dark.
Any help for either of these two questions would be greatly appreciated.
Below is tha VBA code that related to the =makearray formula.
Function MakeArray(ParamArray CellAddress()) As Variant
' Declaration of function variables.
Dim Temp As Variant
Dim TheArray() As Variant
Dim Count As Integer, Ver As Integer
Dim W As Integer, X As Integer, Y As Integer, Z As Integer
' Initialize the Count variable.
Count = 1
' Set the variable Ver = 0 if the version of Microsoft Excel is
' greater than 8 (8 is Microsoft Excel 97 for Windows).
If Left(Application.Version, Len(Application.Version) - 1) >= 8 Then
Ver = 0
Else
Ver = 1
End If
' Set variable X from Ver to the total number of arguments in
' the CellAddress array.
For X = Ver To UBound(CellAddress, 1)
' Temp equals the first element of the CellAddress array.
Set Temp = CellAddress(X)
' Test Temp to see whether it is an array.
If IsArray(Temp) Then
' If Temp is an array, set Y from 1 to the total number
' arguments in the Temp array's first dimension.
For Y = 1 To UBound(Temp.Value, 1)
' If Temp is an array, set Z from 1 to the total number
' arguments in the Temp array's second dimension.
For Z = 1 To UBound(Temp.Value, 2)
' ReDimension TheArray, Preserving any existing
' values, from 1 to Count.
ReDim Preserve TheArray(1 To Count)
' TheArray, element Count equals Temp, element Y in the
' first dimension by element Z in the second dimension.
TheArray(Count) = Temp(Y, Z).Value
' Increment the Count variable by one.
Count = Count + 1
Next Z
Next Y
' If Temp is not an array, proceed from here.
Else
' ReDimension TheArray, preserving any existing
' values, from 1 to Count.
ReDim Preserve TheArray(1 To Count)
' TheArray element Count equals Temp.
TheArray(Count) = Temp
' Increment the Count variable by one.
Count = Count + 1
' End the block If statement.
End If
Next X
' Return TheArray to our function MakeArray.
MakeArray = TheArray
End Function
1) I have have copied the code and used the makearray() formula that microsoft produce to be able to produce arrays where cells are non-adjacent to each other. I have left the code at the bottom of this message to be reviewed and adjusted for this question. The formula works fine, however when using the =makearray() formula it does not seem to recognise anything else except the cell address. So say I wanted to take the absolute value, by using the =ABS() fucntion, for one of the cells that I am selecting to be in my array, i get the error #VALUE! in each cell, of my resultant array. How should I can i get arround it so that formulas can be recognised in this makearray() forumla. I suspect I would need to adjust some of the VBA code, but was wondering how exactly would it need to be adjusted so that the function {=makearray(A1, ABS(B33), E12)} would work, without obviously producing another cell that already carries out the ABS(B33) in a separate cell, and then the makearray formula referencing that new cell.
2) I am using the makearray formula in the define name section (Insert>Name>Define). I have basically been calling a range of sorts, say "orange", which is comprised of 3 cells that are not adjacent to each other, and then using that range name in another formula. However this does not work, as I keep getting the error #REF!. Normally named ranges can be used in formulas using the =INDIRECT(orange) function, however when using this function, i still get the same error message. But this function does work when you go through the traditional method of highighting adjacent cells and naming that range and using that name in the indirect function, so that it references the named range. I think the reason that my formula is not working using the indirect function, is that it is not a named range (since it has been produced in the Insert>Name>define section) in the sense that it is not on the list of named ranges on the top left hand side of the screen which normally shows the address of the cell, but thats just my opinioni and is a stab in the dark.
Any help for either of these two questions would be greatly appreciated.
Below is tha VBA code that related to the =makearray formula.
Function MakeArray(ParamArray CellAddress()) As Variant
' Declaration of function variables.
Dim Temp As Variant
Dim TheArray() As Variant
Dim Count As Integer, Ver As Integer
Dim W As Integer, X As Integer, Y As Integer, Z As Integer
' Initialize the Count variable.
Count = 1
' Set the variable Ver = 0 if the version of Microsoft Excel is
' greater than 8 (8 is Microsoft Excel 97 for Windows).
If Left(Application.Version, Len(Application.Version) - 1) >= 8 Then
Ver = 0
Else
Ver = 1
End If
' Set variable X from Ver to the total number of arguments in
' the CellAddress array.
For X = Ver To UBound(CellAddress, 1)
' Temp equals the first element of the CellAddress array.
Set Temp = CellAddress(X)
' Test Temp to see whether it is an array.
If IsArray(Temp) Then
' If Temp is an array, set Y from 1 to the total number
' arguments in the Temp array's first dimension.
For Y = 1 To UBound(Temp.Value, 1)
' If Temp is an array, set Z from 1 to the total number
' arguments in the Temp array's second dimension.
For Z = 1 To UBound(Temp.Value, 2)
' ReDimension TheArray, Preserving any existing
' values, from 1 to Count.
ReDim Preserve TheArray(1 To Count)
' TheArray, element Count equals Temp, element Y in the
' first dimension by element Z in the second dimension.
TheArray(Count) = Temp(Y, Z).Value
' Increment the Count variable by one.
Count = Count + 1
Next Z
Next Y
' If Temp is not an array, proceed from here.
Else
' ReDimension TheArray, preserving any existing
' values, from 1 to Count.
ReDim Preserve TheArray(1 To Count)
' TheArray element Count equals Temp.
TheArray(Count) = Temp
' Increment the Count variable by one.
Count = Count + 1
' End the block If statement.
End If
Next X
' Return TheArray to our function MakeArray.
MakeArray = TheArray
End Function
