Compile error from Office 2010 to Office 365: Variable not defined

[mrsun]

Hello,

I have to migrate a very old excel file with many macros from Office 2010 to Office 365 (Office 2010 being no longer supported). The Excel file works perfectly on Office 2010.

When I run the VBA compiler, I get the following error message: Compile error Variable not defined

Here is the code: The issue highlighted by the VBA editor is:

 If strRec = SP_KUGIRI Then

How can the code be amended to be supported by O365?

Sub GetStorePlanFile(ByVal strFilePath As String, ByRef arrDataHeader As Variant, ByRef arrDataDetail As Variant)

    Dim fso As Object
    Dim ffile As Object
    Dim csvDataLine As Variant
    Dim lngHeaderCntC As Long
    Dim lngDetailCntC As Long
    Dim i As Long, j As Long
    Dim bDetailFlg As Boolean
    Dim intFree As Long
    Dim strRec As String
    
    Dim colHeader As Collection
    Dim colDetail As Collection
    
    Set colHeader = New Collection
    Set colDetail = New Collection
    
    bDetailFlg = False
    
    intFree = FreeFile
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Open strFilePath For Input As #intFree

    Do Until EOF(intFree)
        Line Input #intFree, strRec
        
      
        If strRec = SP_KUGIRI Then
            bDetailFlg = True
            i = 0
        Else
            If bDetailFlg Then
                colDetail.Add (strRec)
            Else
                colHeader.Add (strRec)
            End If
        End If
    Loop
    
    Close #intFree
    
    Dim oneLineH As Variant
    Dim oneLineD As Variant
    
    oneLineH = Split(colHeader.Item(1), ",")
    oneLineD = Split(colDetail.Item(1), ",")
    
 
    lngHeaderCntC = UBound(oneLineH) + 1
    lngDetailCntC = UBound(oneLineD) + 1
    
    ReDim arrDataHeader(colHeader.Count, lngHeaderCntC)
    ReDim arrDataDetail(colDetail.Count, lngDetailCntC)
    
    Dim ch As Variant
    Dim cd As Variant
    
    j = 1
    For Each ch In colHeader
        csvDataLine = Split(ch, ",")
        For i = 1 To lngHeaderCntC
            arrDataHeader(j, i - 1) = csvDataLine(i - 1)
        Next
        j = j + 1
    Next
    
    j = 1
    For Each cd In colDetail
        csvDataLine = Split(cd, ",")
        For i = 1 To lngDetailCntC
            arrDataDetail(j, i - 1) = csvDataLine(i - 1)
        Next
        j = j + 1
    Next
    
    Set colHeader = Nothing
    Set colDetail = Nothing
    
End Sub

Thank you!

 

[rlv01]

The problem does not have anything to do with Excel 2010, because when I test your code using that version I get the same error. It is more likely that SP_KUGIRI is a global/public variable defined at the module level and was not carried over when you migrated your code.

 

[mrsun]

The problem does not have anything to do with Excel 2010, because when I test your code using that version I get the same error. It is more likely that SP_KUGIRI is a global/public variable defined at the module level and was not carried over when you migrated your code.

Thank you for checking.

I am using exactly the same package as the one running with Office 2010. the only difference is that one system uses 32 bits and the other one 64 bits. I managed to solve some issues by adding PtrSafe so I am wondering it might be the case also here.

 

[Alex Blakenburg]

How did you "migrate" the code ?
Did you by any chance not have the Option Explicit line in 2010 and do have in it MS365 ?
Does your text file contain the text "SP_KUGIRI" ? in which case the word should be in quotes in your if statement ie

If strRec = "SP_KUGIRI" Then

 

[Joe4]

Did you check to see if you have any missing libraries/references?
This commonly happens when running the same code on different systems.

If you go to the system where it is working, go to the VB Editor, and under the Tools menu select References, and note all the selected libraries.
Now do the same on your new system, and see if there are any references that were selected from your old system that are missing from this new one.
If there are, find them in the list and select them (note that the version number may be slightly different, but the rest of text verbiage should be the same).

 

[mrsun]

How did you "migrate" the code ?
Did you by any chance not have the Option Explicit line in 2010 and do have in it MS365 ?
Does your text file contain the text "SP_KUGIRI" ? in which case the word should be in quotes in your if statement ie

If strRec = "SP_KUGIRI" Then

After checking further, the same issue happens in Office 2010.

Thank you for your help!

The issue is somewhere else...

 

[rlv01]

Thank you for checking.

I am using exactly the same package as the one running with Office 2010. the only difference is that one system uses 32 bits and the other one 64 bits. I managed to solve some issues by adding PtrSafe so I am wondering it might be the case also here.

SP_KUGIRI is a variable or constant from somewhere else. Somewhere outside of 'Sub GetStorePlanFile' . You must find it in order to know how to migrate it. It is not any kind of special difference between Excel O365 and 2010. @Joe4's suggestion to look at your library references is a good one. You might also look to see if the old setup had any add-ins installed that could have defined it.

In fact, if you still have the old 2010 setup available, then the quickest way might be to use the 2010 VBE debugger to set a breakpoint and inspect the value of SP_KUGIRI.

 

[mrsun]

Did you check to see if you have any missing libraries/references?
This commonly happens when running the same code on different systems.

If you go to the system where it is working, go to the VB Editor, and under the Tools menu select References, and note all the selected libraries.
Now do the same on your new system, and see if there are any references that were selected from your old system that are missing from this new one.
If there are, find them in the list and select them (note that the version number may be slightly different, but the rest of text verbiage should be the same).

Thank you for checking,

No missing references. The librairies are same on both systems.

I will keep checking

 

[Joe4]

Thank you for checking,

No missing references. The librairies are same on both systems.

I will keep checking

Then the most likely culprit is the SP_KUGIRI variable that rlv01 mentions.
Your code is not recognizing it, so you need to debug and figure out what it is, where it comes from, and and where it is being set.