VBA - Getting around the compile error "Fixed or static data can't be larger than 64K"

Posted on 2009-12-31
Last Modified: 2012-05-08
I have a VB program that uses user defined types but one of them causes the above error to occur.

An example of what i have is

Type First_type 'salary record
    First_type1 As String * 8
    First_type2 As String * 3
    First_type3 As String * 8
End Type

Type Second_type 'salary record
    Second_type1 As String * 10
    Second_type2 As String * 3
    Second_type3 As String * 10
End Type

I then declare each of these as a data_type (some have multiple occurences), i.e.

Public Type data_type
First (1) As First_type
Second (5) As Second_type
Third (2) As Third_type

The actual data i have i much larger there are approx 20 - 30 types in the data set.

I can get it to work by commenting a couple of the items out of the data set but wondered if there is a better solution?

Could I just split the data_type into two sections? would this cause me any problems elsewhere in the program?

Any advice would be appreciated.

Question by:graham666
    LVL 85

    Expert Comment

    by:Rory Archibald
    Have you tried using a class for the Data_type type? It's either that or use variable length strings, I think.

    Author Comment

    I haven't tried using a class but from searching the internet i understand the same limitation applies?

    I think the length of strings need to be specified because the relate to the exact lenght of the incoming data.
    LVL 85

    Accepted Solution

    No, it doesn't. Your current issue is specific to trying to declare a UDT that exceeds 64k. If you created a class with variables representing the other types, you should be fine. As a test I used the below code. If I uncomment the data_type declaration lines, I get your error. If I use the class, no error. :)

    ' Class module Class1
    Option Explicit
    Dim first(5000) As First_type, second(5000) As Second_type, third(5000) As Third_type
    Private Sub Class_Initialize()
        Dim n As Long
        For n = 0 To 5000
            With first(n)
                .First_type1 = Space(80)
                .First_type2 = Space(30)
                .First_type3 = Space(80)
            End With
            With second(n)
                .Second_type1 = Space(100)
                .Second_type2 = Space(30)
                .Second_type3 = Space(100)
            End With
            With third(n)
                .Third_type1 = Space(1000)
                .Third_type2 = Space(30)
                .Third_type3 = Space(100)
            End With
        Next n
    End Sub
    ' normal module
    Option Explicit
    Type First_type 'salary record
        First_type1 As String * 80
        First_type2 As String * 30
        First_type3 As String * 80
    End Type
    Type Second_type 'salary record
        Second_type1 As String * 100
        Second_type2 As String * 30
        Second_type3 As String * 100
    End Type
    Type Third_type 'salary record
        Third_type1 As String * 1000
        Third_type2 As String * 30
        Third_type3 As String * 100
    End Type
    'Type data_type
    '    first(5) As First_type
    '    second(32) As Second_type
    '    third(50) As Third_type
    'End Type
    Dim cls As Class1
    Sub test()
        Set cls = New Class1
    End Sub

    Open in new window


    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now