OaklandJim
Well-known Member
- Joined
- Nov 29, 2018
- Messages
- 878
- Office Version
- 365
- Platform
- Windows
I thought that this would be straightforward. I have of string values in one dimensional array. I want to develop code to remove the array element with a specified string. I'm pretty sure that the function is handling the task correctly. The difficulty I'm having is getting the result array in the UDF returned to the caller. I've tried a few ways but clearly I am missing something about how VBA handles arrays. Code below generates the Debug.Print results as shown below the code. What basic concept am I missing?
Array element to omit = 2
< found index, result array = A
> found index, result array = C
> found index, result array = D
Ubound result array = 3
Elements in ByRef array parameter after processing
A
B
C
VBA Code:
Option Explicit
Option Base 1
Sub test2()
Dim arr1(1 To 4) As Variant
arr1(1) = "A"
arr1(2) = "B"
arr1(3) = "C"
arr1(4) = "D"
' Causes "Can't assign to array" error.
' arr1 = DeleteElement("B", arr1)
' Send arr1 as byref parameter
Call DeleteElement("B", arr1)
Debug.Print Chr(10) & "Elements in ByRef array parameter after processing"
Debug.Print arr1(1)
Debug.Print arr1(2)
Debug.Print arr1(3)
'Debug.Print DeleteElement(3) 'Causes error: parameters not optional.
End Sub
Function DeleteElement( _
sValueToDelete As String, _
pavList() As Variant) As Variant
Dim iElementLoop As Long, iElementFound As Long
Dim avResult() As Variant
ReDim avResult(UBound(pavList) - 1)
For iElementLoop = 1 To UBound(pavList)
If pavList(iElementLoop) = sValueToDelete Then
iElementFound = iElementLoop
Exit For
End If
Next iElementLoop
' Dim Results Array to have one fewer elements than the parameter array.
ReDim avResult(UBound(pavList) - 1)
Debug.Print "Array element to omit = " & iElementFound
' Loop array parameter to put the respective value into the results array.
' Skips the parameter array element specified by iElementFound.
For iElementLoop = 1 To UBound(pavList)
If iElementLoop < iElementFound Then
avResult(iElementLoop) = pavList(iElementLoop)
Debug.Print "< found index, result array = " & avResult(iElementLoop)
ElseIf iElementLoop > iElementFound _
Then
avResult(iElementLoop - 1) = pavList(iElementLoop)
Debug.Print "> found index, result array = " & avResult(iElementLoop - 1)
End If
Next iElementLoop
' ReDim then refill the ByRef array parameter.
' This causes an error: Array is fixed or locked.
' ReDim pavList(iElementLoop - 1)
' Iterate the result array and refill the ByRef array that was resized above.
' For iElementLoop = 1 To iElementLoop - 1
' pavList(iElementLoop) = avResult(iElementLoop)
' Next
'
' Assign ByRef parameter to the results array. Causes a type mismatch error.
' pavList() = avResult()
' pavList = avResult
' Assign ByVal parameter to the results array. Causes a type mismatch error.
' pavList() = avResult()
' pavList = avResult
Debug.Print "Ubound result array = " & UBound(avResult)
DeleteElement = avResult
End Function
Array element to omit = 2
< found index, result array = A
> found index, result array = C
> found index, result array = D
Ubound result array = 3
Elements in ByRef array parameter after processing
A
B
C