Please help me lessen my If/EndIfs with smarter code! 3

[steve728]

Will one of you Cyber Buddies show me how to shorten the
below list of If/EndIfs? I'm obviously not good enough
to figure it out myself.

Public Function VBAUpdateInsert(MODE As String, TableName As String, WhereStr As String, ParamArray Arguments() As Variant)

' The following code will convert the single array into a more useful 2 dimensional one.
Dim DataArray(101, 2) As Variant
Dim IntCounter As Integer

Do While IntCounter <= UBound(Arguments)

For IntCounter = 0 To UBound(Arguments)
If IntCounter = 0 Then
DataArray(0, 0) = Arguments(IntCounter)
End If
If IntCounter = 1 Then
DataArray(0, 1) = Arguments(IntCounter)
End If
If IntCounter = 2 Then
DataArray(1, 0) = Arguments(IntCounter)
End If
If IntCounter = 2 Then
DataArray(1, 1) = Arguments(IntCounter)
End If
... through to

If IntCounter = 100 Then
DataArray(50, 0) = Arguments(IntCounter)
End If
If IntCounter = 101 Then
DataArray(50, 1) = Arguments(IntCounter)
End If

Next
Loop ' Then I process the 2 dimensional array...

 

[Zion7]

Dim DataArray(UBound(Arguments), 1) As Variant
Dim x1 As Integer
Dim x2 as integer
Dim UB As Integer

If UBound(Arguments) Mod 2 = 0 Then
UB = UBound(Arguments)/2
End If


For x1 = 0 To UB
For x2 = 0 To 1
DataArray(x1,x2) = Arguments(x1)
Next x2
Next x1

this will fail, if UBound(Arguments) is an uneven number.
...can you figure out the resolve?

 

[steve728]

Thank you so much for this quick reply on Saturday afternoon! I will try this code out and overcome the problem with an uneven number. You get a star!

Steve

 

[Remou]

Here is another thought:

    For IntCounter = 0 To UBound(Arguments)
        If IntCounter Mod 2 = 0 Then
            DataArray(IntCounter / 2, 0) = Arguments(IntCounter)
        Else
            DataArray(IntCounter \ 2, 1) = Arguments(IntCounter)
        End If
     Next

 

[steve728]

I'll try it out Buddy! I'm sending you a star. It look's like my weekend may be a positive one after all!

Steve

 

[Zion7]

Sorry Steve, I wrote it very fast.
I dimensioned the 2 elemant array, incorrectly.

Just to explain, in order to gat All elemants, of your
paramater array, you have to add a false value to your last, 2nd element,
if Ubound(argumants) is uneven.

Something like this;

Dim x1 As Integer
Dim x2 as integer, blnUneven As Boolean
Dim UB As Integer,varValue As Variant

If UBound(Arguments) Mod 2 = 0 Then
UB = UBound(Arguments)/2
Else
UB = (UBound(Arguments)/2) + 1
blnUneven = True
End If

Dim DataArray(UB), 1) As Variant


For x1 = 0 To UB
For x2 = 0 To 1

varValue = IIf( x1 = UB And x2 = 1 And blnUneven = True,"",Arguments(x1))

DataArray(x1,x2) = varValue

Next x2
Next x1

 

[Zion7]

I should've listened to Remou...

If UBound(Arguments) Mod 2 = 0 Then
UB = UBound(Arguments)/2
Else
UB = (UBound(Arguments)\2) + 1
blnUneven = True
End If

 

[steve728]

I'll let you know what I end up with. Thanks Buddies!

 

[steve728]

The following line of code is giving me grief:
Dim DataArrayX(UB, 1) As Variant
I get a compile error of "Constant expression required"
because Access wants the memvar UB to be a hard coded #.
The rest of the code looks excellent. Can you help me?

Steve

 

[MajP]

I think you have to dimension it first and redim it in order to use a variable.
Dim DataArrayX() As Variant
redim DataArrayX(UB,1)

 

[steve728]

Thanks! The Redim worked! Star for you Cyber Buddy!

Steve