Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

steve728

Programmer
Joined
Mar 16, 2003
Messages
536
Location
US
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...
 
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?


 
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
 
Here is another thought:
Code:
    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
 
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
 
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
 
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
 
I'll let you know what I end up with. Thanks Buddies!
 
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
 
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)
 
Thanks! The Redim worked! Star for you Cyber Buddy!

Steve
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top