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

Posted on 2009-12-31
Medium Priority
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
  • 2
LVL 85

Expert Comment

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

Author Comment

ID: 26153009
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

Rory Archibald earned 500 total points
ID: 26153068
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

850 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