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

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.

Who is Participating?
Rory ArchibaldConnect With a Mentor Commented:
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

Rory ArchibaldCommented:
Have you tried using a class for the Data_type type? It's either that or use variable length strings, I think.
graham666Author Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.