User-Defined Arrays and Errors

blairintern

New Member
Joined
Jul 16, 2007
Messages
21
I am having difficulty with setting a loop to end when it has reached the end of an array (of objects of a user-defined class).

I will be the first to admit that the following code is not pretty, nor is it efficient. I welcome any suggestions whatsoever, but ultimately I need help getting this operation to run smoothly and effectively.

Here is the code which errors as I attempt to get the Array's elements to print out in my desired format.
Code:
Sub DrawIssues()
    Dim i As Integer ' used to keep track of index in array
    Dim k As Integer ' used to keep track of time through loop
    Dim xDeals As Double ' number of deals in array
    Dim r As Integer
    Dim factor As Integer
    Call MakeIssue <-----------------Is this proper?
    Sheets(2).Activate
    Sheets(2).Range("A1").Select
    r = 6
    i = 0
    xDeals = UBound(AllIssues) <---ERROR: Subscript out of Range
    factor = 3 + (4 * i)
    For k = 1 To xDeals
        Do
            With Sheets(2).Range("A1").Offset(r, factor)
                If r = 6 Or r = 12 Then
                    If r = 6 Then
                        .Range("A1:D1").MergeCells = True
                        .Range("A1") = AllIssues(k).DatedDate
                    ElseIf r = 12 Then
                        .Range("A1") = "Amount"
                        .Range("D1") = "Coupon"
                    End If
                    .Interior.Color = RGB(255, 255, 224)
                ElseIf r = 13 Or r = 14 Or r = 46 Or r = 51 _
                Or r = 58 Then
                    .Interior.Color = RGB(255, 255, 255)
                    If r = 13 Then
                        .Range("A1:D1").MergeCells = True
                        .Range("A1") = AllIssues(k).Rating
                    End If
                ElseIf r > 6 And r < 12 Then
                    .Range("A1:D1").MergeCells = True
                    .Range("A1:D1").Interior.Color = RGB(255, 255, 255)
                    If r = 7 Then
                        .Range("A1") = AllIssues(k).Obligation
                    ElseIf r = 8 Then
                        .Range("A1") = AllIssues(k).Series
                    ElseIf r = 9 Then
                        .Range("A1") = AllIssues(k).Par
                    ElseIf r = 10 Then
                        .Range("A1") = AllIssues(k).CallInfo
                    ElseIf r = 11 Then
                        .Range("A1") = AllIssues(k).Maturity
                    End If
                ElseIf r > 14 And r < 46 Then
                ElseIf r = 47 Then
                    .MergeCells = True
                    ' .TextAlign (2)
                    .Interior.Color = RGB(204, 204, 204)
                ElseIf r > 47 And r < 51 Then
                    .MergeCells = True
                    ' .TextAlign (2)
                    .Interior.Color = RGB(255, 255, 255)
                ElseIf r > 51 And r < 58 Then
                    ' .TextAlign (2)
                    .Interior.Color = RGB(255, 255, 255)
                ElseIf r > 58 And r <= 60 Then
                    .Interior.Color = RGB(255, 255, 255)
                End If
                If r <> 14 And r <> 46 And r <> 51 And _
                r <> 58 And r <> 7 And r <> 8 And r <> 9 _
                And r <> 10 And r <> 11 Then
                    Call BoxIt(Sheets(2).Range("a1:d1").Offset(r, factor))
                End If
            End With
             r = r + 1
        Loop Until r = 61
        k = k + 1
        i = i + 1
        factor = 3 + (4 * i)
    Next
End Sub

The CALL MakeIssue above (bolded) calls this function which returns an array of IssueClass
Code:
Function MakeIssue() As IssueClass()
    Application.ScreenUpdating = False
    Sheets(1).Activate
    Sheets(1).Range("A1").Select
    Dim i As Integer ' indicates the index of the loop
    Dim n As Integer ' indicates what issue loop is on
    i = 0
    n = 1
    ' ReDim Preserve AllIssues(1 To n)
    ' redim AllIssues with loop to detect each issue.
 
    Do
        With Sheets(1).Range("A1")
            If .Offset(i, 0) = "" Then
                Set Deal = New IssueClass
            Else
                Set Deal = New IssueClass
                Deal.Issue = .Offset(i, 2)
                Deal.Purpose = .Offset(i, 2) & " " & .Offset(i, 4) _
                & " " & .Offset(i, 5)
                Deal.DatedDate = .Offset(i, 0)
                Deal.Series = .Offset(i, 3)
                If .Offset(i, 7) = "#" Then
                    Deal.Par = .Offset(i, 7)
                End If
                Deal.CallDate = .Offset(i, 8)
                Deal.CallPrice = .Offset(i, 9)
                Deal.Fitch = .Offset(i, 10)
                Deal.Moody = .Offset(i, 11)
                Deal.SandP = .Offset(i, 12)
                Deal.Underwriter = .Offset(i, 16)
                Deal.Counsel = .Offset(i, 17)
                Deal.Refund = .Offset(i, 19)
                ' Deal.Insurance = .Offset(i, 25) ' not yet on form
                n = n + 1
                ReDim Preserve AllIssues(1 To n)
                Set AllIssues(n) = Deal
            End If
            i = i + 1
        End With
    Loop Until i = 1000
    MakeIssue() = AllIssues()
End Function

I know this code isn't going to win awards for brevity or elegance, but I just need it to work properly.
Thanks for your help in advance!
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,329
Office Version
  1. 365
Platform
  1. Windows
Where exactly are declaring AllIssues?

PS I really think you need to take a 'step back' and rethink your approach.

You just seem to be overcomplicating things.

Arrays? Classes? User defined types?

Functions that return user defined types.:eek:

By the way you shouldn't really be calling a function especially one that's supposed to return a value.
 

blairintern

New Member
Joined
Jul 16, 2007
Messages
21
I think somewhere in there, I got logically snowed-under.

As far as replacing the user-defined class (i've done a bit of java, so that was a logical decision for me...they're more common there) should I consider making IssueClass a TYPE?

AllIssues is declared outside of the Subs in the Module (declaration section, according to VBE) and reads:

Public AllIssues() As IssueClass
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,329
Office Version
  1. 365
Platform
  1. Windows
I would steer clear of both user defined types and classes in the first place.:)

I know using these in java, C, whatever is standard practice but it isn't really in VB/VBA.

I'm not saying it's not possible to use these methods, but it just seems to me that by doing so you're just overcomplicating things.

PS Are you sure Excel is the best application for what you are doing?

It seems to be that you are trying to create some kind of database.

If that's the case then maybe using an actual database application might be an idea.:)
 

blairintern

New Member
Joined
Jul 16, 2007
Messages
21
A database would certainly be a better resolution. However, my employer isn't so keen on that so i've been encouraged to use excel.

My problem is this:
I have a program which outputs .txt files which are delimited. I then open them in excel and I want to take the information they present and transform it into a form that we have been using for a long time here at my company.

Reason being, this basically autmates the interns :)

Any willingness to help me out in figuring out a better logic scheme for this one?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,102
Messages
5,768,104
Members
425,454
Latest member
khoro

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top