Deleting elements of an array

[aashir272]

Hi. I have this function that is supposed to delete the element of an array at a required index. All input arrays start from 1 and are 2-dimensional. However, at the ReDim stage, it gives me an error that says subscript out of range. I can't seem to figure out what is wrong with the code.

Sub DeleteElementAt(ByVal index As Integer, ByRef required_array As Variant)
Dim i As Integer

' Move all element back one position
For i = index + 1 To UBound(required_array)
required_array(i - 1, 1) = required_array(i, 1)
Next
required_array(i - 1, 1) = -1

ReDim Preserve required_array(1 To i - 2, 1)
End Sub

 

[ChrisM]

a ReDim Preserve only allows you to change the LAST element of the array. You are trying to change the first.

It would have to be: ReDim Preserve required_array(1, 1 To i - 2)

But of course, to do that you will have to transpose your array.

Probably be easier in the end to just create a new second array with one less element, then read your original array in to the new one.

 

[Rick Rothstein]

Hi. I have this function that is supposed to delete the element of an array at a required index. All input arrays start from 1 and are 2-dimensional. However, at the ReDim stage, it gives me an error that says subscript out of range. I can't seem to figure out what is wrong with the code.

Sub DeleteElementAt(ByVal index As Integer, ByRef required_array As Variant)
Dim i As Integer

' Move all element back one position
For i = index + 1 To UBound(required_array)
required_array(i - 1, 1) = required_array(i, 1)
Next
required_array(i - 1, 1) = -1

ReDim Preserve required_array(1 To i - 2, 1)
End Sub

Since it is always going to be a two-dimensional array, formed from a range I presume, and since you will be removing the equivalent of one of the rows from that presumed range, then the following code should do what you want...

[table="width: 500"]
[tr]
	[td]Sub DeleteElementAt(ByVal Index As Integer, ByRef ArrayIn As Variant)
  Dim X As Long, Indices As String
  For X = 1 To UBound(ArrayIn)
    If X <> Index Then Indices = Indices & ";" & X
  Next
  ArrayIn = Application.Index(ArrayIn, Evaluate("{" & Mid(Indices, 2) & "}"), Split("1 2"))
End Sub[/td]
[/tr]
[/table]

 

[aashir272]

Rick,

I used the above method and it started giving me an error in the function below:
'This function calculates k*stdev for a p chart
Function p_stdevs(ByVal multiple As String, ByRef n_values As Variant, ByRef x_values As Variant, ByVal flag As Integer) As Variant
Dim p_bar As Double
Dim n_bar As Double
Dim m_stdev() As Variant
Dim count As Integer
ReDim m_stdev(1 To UBound(n_values))
p_bar = Application.WorksheetFunction.Sum(x_values) / Application.WorksheetFunction.Sum(n_values)
n_bar = Application.WorksheetFunction.Sum(n_values) / Application.WorksheetFunction.count(n_values)
multiple = CDbl(multiple)
For count = 1 To UBound(n_values)
If (flag = 0) Then
m_stdev(count) = multiple * Sqr((p_bar) * (1 - p_bar) / n_bar)
Else
m_stdev(count) = multiple * Sqr((p_bar) * (1 - p_bar) / n_values(count, 1))
End If
Next
p_stdevs = m_stdev
End Function
it says "unable to get the sum property of the worksheet function class". Could the two be related since this error only came when I implemented the function you suggested?

 

[Rick Rothstein]

Rick,

I used the above method and it started giving me an error in the function below:
'This function calculates k*stdev for a p chart
Function p_stdevs(ByVal multiple As String, ByRef n_values As Variant, ByRef x_values As Variant, ByVal flag As Integer) As Variant
Dim p_bar As Double
Dim n_bar As Double
Dim m_stdev() As Variant
Dim count As Integer
ReDim m_stdev(1 To UBound(n_values))
p_bar = Application.WorksheetFunction.Sum(x_values) / Application.WorksheetFunction.Sum(n_values)
n_bar = Application.WorksheetFunction.Sum(n_values) / Application.WorksheetFunction.count(n_values)
multiple = CDbl(multiple)
For count = 1 To UBound(n_values)
If (flag = 0) Then
m_stdev(count) = multiple * Sqr((p_bar) * (1 - p_bar) / n_bar)
Else
m_stdev(count) = multiple * Sqr((p_bar) * (1 - p_bar) / n_values(count, 1))
End If
Next
p_stdevs = m_stdev
End Function
it says "unable to get the sum property of the worksheet function class". Could the two be related since this error only came when I implemented the function you suggested?

First of all, I would remove the parentheses from the blue highlighted text... you are going to assign an array to the Variant variable... the Variant variable does not have to be a dynamic array for such an operation. Second, I'm confused by your question. The red highlighted line is creating a one-dimensional array, not a two-dimensional one (I don't see any other arrays in the function), but no matter since I do not see you calling the DeleteElementAt subroutine anywhere inside this function (so how could it be producing an error for you?). When or where are/were you planning on calling the DeleteElementAt subroutine?