VB6 VBA Read Large Binary Flat Files Past 2GB Limitation Using Windows API ReadFile WriteFile

If you deal with really large flat files in VB6 or VBA, how do you read past the 2 GB limitation? Visual Basic 6 / VBA has a very easy file read/write mechanism for binary files. It works great for user defined types (UDTs) and arrays of UDTs. Unfortunately, this interface was written before terabyte hard drives became standard. The actual limit is the size of a long integer (2,147,483,647). When reading using the binary Get function, I had the unpleasant experience of rolling the record number, which resulted in the value becoming negative, triggering a read error. I was fortunate to find a well-written demo that gives a solution using the Windows API file read / write methods to ReadFile and WriteFile. You can find the demo and more information at the link below:

VB6 HugeFixedFile

The code contains a class that encapsulates the basic methods you will need to deal with large files in VBA and VB6. See the code and forum posts for more details. I have since ditched the UDT interface as I found that LSet didn't do a great job converting my byte array to UDT. So I manually convert the byte arrays into the proper types. I had to find / build functions to convert a byte array to an integer and a long. The code is posted below in case some of you might find it useful to convert a byte array to an integer or a byte array to a long in VB6 or VBA:

Code:

Private Function ConvertByteToInteger(ByVal start As Long, ByVal lend As Long, ByRef byt() As Byte) As Integer
    ConvertByteToInteger = byt(start) + CLng(byt(lend)) * 256
End Function

Private Function ConvertByteToLong(ByVal start As Long, ByVal lend As Long, ByRef byt() As Byte) As Long
    ConvertByteToLong = byt(start) + CLng(byt(start + 1)) * 256& + CLng(byt(start + 2)) * 2& ^ 16 + CLng(byt(lend)) * 2 ^ 32
End Function

I also ditched the UDTs though the code at the link above shows how to convert a byte array to UDT using LSet. I found that by using the HugeFixedFile class and loading a byte array of 1000 records of 16 byte records, my processing time was cut by almost 2/3 when reading a large file. As a result, I edited the Read function (found in the demo) to allow for reading a larger byte array than the record count as follows: (assumes a zero-based byte array)

Code:

Public Function ReadRec(ByRef Record() As Byte) As Long
    If ReadFile(hFile, VarPtr(Record(0)), UBound(Record) + 1, ReadRec, 0) Then
        If ReadRec = 0 Then
            fEOF = True
        End If
    Else
        RaiseError HFF_READ_FAILURE
    End If
End Function

Writing A Chunk of Data to File in C# and VB6

In Visual Basic 6 / VBA it is possible to quickly write a lot a data to file in just a couple of steps. By creating a user defined type that contains the data, and then using an array of user defined types, all that data can be loaded from and saved to disk in a very easy manner. Below is some pseudo code melded from a couple of my VB6 projects to illustrate the point for loading all data in one shot:

VB6 Code:

' declarations
' 16 byte data storage
Private Type TimeSales5
    ntSymNum As Byte
    ntDay As Byte
    ntMonth As Byte
    ntYear As Integer
    ntHour As Byte
    ntMinute As Byte
    ntSecond As Byte
    ntBid As Long
    ntAsk As Long
End Type

VB6 Code:

' Main Load Routine
Sub LoadFromFile(ByVal sName as string)
    dim uTick() as TimeSales5
    iFreeTSFile = FreeFile
    Open "C:CompleteData2\" & sName & ".ts3" For Binary As #iFreeTSFile
    cLen = LOF(iFreeTSFile)

    lRecords = cLen / 16 - 1
    If lRecords >= 0 Then
        ' size the uTick type to receive all the file's records
        ReDim uTick(lRecords) 
        Get #iFreeTSFile, , uTick ' load all the data
        ' code to assign the fields from uTick to native 
        ' types (double, long etc)
    End If
End Sub

Simple VB6 code to save all data in a user defined type one line of code based on the snippet above might be:
Put #iFreeTSFile, , uTick

Is there an equivalent to VB6 binary file writing of user defined types in C#? I searched quite a bit for a simple yet elegant solution to reading and writing custom file formats in C#.  The answer, it seems is using C#'s Stream object for file access and BinaryFormatter to serialize a custom data class to and from disk.

Now that the table has been set, it's time for a paradigm shift from the VB6 mentality of writing binary files to the C# way of writing data to file via Serialization. There are other ways to write data to file in C# but using C# Serialization and DeSerialization for file writing and reading  seems most similar to writing a VB6 user defined type to file in one shot or reading from file in one shot.

Code from SaveFormat class

C# Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace MyNamespace {
    [Serializable()]
    class SaveFormat {
        private static int MAXROWS = 1000;
        private static int MAXTABS = 50;
        public DateTime[] dateTime = new DateTime[MAXROWS];
        public double[, ,] EquityCurve = new double[4, MAXTABS, MAXROWS];
        public int[] EquityCurveIndex = new int[MAXROWS];
    }
}

Note the [Serializable()] attribute which tells C# that the SaveFormat class may be written to and read from a file. The class can contain any data format you need to store and retrieve, including as in the above example, multidimensional arrays.

Code from SaveFile class

C# Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Runtime.Serialization.Formatters.Binary;
using System.Windows.Forms;
namespace MyNamespace {
    class SaveFile {
        public static void SaveMultiA(SaveFormat Data, string name) {
            try {
                using (Stream stream = File.Open (name  ,  FileMode.Create)) {
                    BinaryFormatter bin = new BinaryFormatter();
                    bin.Serialize(stream,  Data);
                }
            } catch (IOException ex) {
                MessageBox.Show("Error Saving SaveFormat Storage Class : " 
                 + ex.ToString());
            }
        }
    }
}

The SaveFile class essentially just creates a new C# Stream object with the specified file name and the FileMode.Create option. This stream object is then plugged into a new BinaryFormatter object using the Serialize method. The entire SaveFormat class (and this could be any format) is then serialized to disk via BinaryFormatter.

And finally this is the LoadFile class

C# Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Runtime.Serialization.Formatters.Binary;
using System.Windows.Forms;
namespace MyNamespace {
    class LoadFile {
        public static void LoadMultiA(ref SaveFormat Data, string name) {
            try {
                if (!File.Exists(name)) return;
                using (Stream stream = File.Open (name  ,  FileMode.Open)) {
                    BinaryFormatter bin = new BinaryFormatter();
                    Data = (SaveFormat)bin.Deserialize(stream);
                }
            } catch (IOException ex) {
                MessageBox.Show("Error Loading SaveFormat Storage Class : " 
                 + ex.ToString());
            }
        }
    }
}

The LoadFile class just does this in reverse. Again a new Stream object is created with a valid file name, using the FileMode.Open option. The stream object is then plugged in again to the BinaryFormatter object using the Deserialize method. the Data is cast as (SaveFormat) to ensure the proper translation of the bits.

Allow me to pause for a moment to admire the beauty of the C# code I just posted. While this may seem like a lot of code, sans the using statements, this is just a few lines of actual code in C# and it gives me that good feeling I get from the pithy VB6 code being able to read and write in one swift, decisive operation. Beautiful!

Calling R From VB6 VBA

I've been using 7Bit's very useful utility to call R from VB6. Originally the mt4R.dll tool was created to interface to R from Metatrader. Knowing that this is a windows DLL, therefore mt4R can be accessed from a number of languages, including VB6, and C# if a suitable interface is used.

To that end I translated the existing Metatrader interface to a simple drop-in VB6 class that may be accessed in Visual Studio, or from VBA from Excel. This handy interface in conjunction with 7Bit's dll allows a VB6 / VBA developer to access the power of the R project directly from code.

I've been using this for some time but decided it was time to share with everyone else. So I put together a simple demo application that shows how to access mt4R.dll in a simple VB6 form application.

You can download Calling R From VB6 VBA. (Please follow the instructions on that page) for use in your own projects.

A note for VB6 / VBA developers:

The array structure of R and VB is not the same. Whereas in R the arrays are arranged as:

A[row, column]

In VB6 / VBA the arrays are declared as follows

Dim A(column, row) as double

When transferring this matrix via Rm (the matrix operation) it will send the data in the correct format if the array in VB is declared as above.

Please let me know if you have any comments, questions or problems getting this code to work.

Misc VB6 to C# Commands

Quick reference VB6 to C#

Q: How do you replicate VB6's Debug.Print in C#?
A: In C#, Add using System.Diagnostics; to the top of the project. Then it is possible to use:

   Debug.Print("debug text");
   Debug.Write("writes without adding the newline character");
   Debug.WriteLine("writes with adding the newline character");

All three methods write to the Immediate window in C# just as Debug.Print does in VB6.

----------------------------------------------------------------
Q: How do you replicate the VB6 mid$ function in C#?
   VB6: mid$("ABCDEFG12345", 7, 2)
returns "G1"

   C# Debug.Print("ABCDEFG1234".Substring(6, 2));
returns "G1"
(note how in VB6 strings the first string's (A in the string above) reference is 1, while in C# the first string's reference is 0.)
----------------------------------------------------------------
Q: How do you call a routine in C# or why do I get the error: 

Only assignment, call, increment, decrement, and new object expressions can be used as a statement

VB6: Call myRoutine or
         myRoutine

C#: myRoutine(); // you must add the () at the end of the routine in C#!

MT4 to R Project

I stumbled upon an interesting interface written to connect Metatrader (MT4) to the R project for statistical computing. If you have an active R installation, you can access R through MT4 via the mt4R.dll. mt4R.dll was created as an interface to wrap the functions of Rterm.exe, a component of the R project that is meant to allow batch mode access to the R terminal. mt4R.dll is a wrapper for Rterm.exe that exposes the R functionality in a well documented and intuitive manner.

Metatrader is a charting package for trading forex. If you like Metatrader, you will like the R for MetaTrader project, where you can download mt4R.dll as well as sample MT4 code to interface MT4 with R. There is a discussion about R for Metatrader on Forex Factory.

I plan to use mt4R.dll to interface VB6 to R, thus saving me from having to implement my own interface. In this effort I have found partial success, though I'm still struggling with a matrix conversion issue of the original interface, and may post more on a VB6 wrapper for mt4R.dll as time goes by.

How To Write a C# 2010 DLL and Call It Within VB6 - CSharp Interoperability

Interoperability in C# is a real PITA. I know because I just spent the entire day chasing down a simple example. Unfortunately the C# documentation out there is so sparse and out of date (in this area the C# language seems to have changed quite a bit pre 2010) on the subject that doing a simple task like linking a C# dll from VB6 seems next to impossible. Maybe the topic is so mind numbingly simple that only pure C# neophytes struggle with something so mundane. But there is hope at the end of this rainbow. This short tutorial will give you the code necessary to write a simple C# 2010 DLL, and link that DLL via COM in VB6.

C# code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;


namespace testMath
{
    public interface Math
    {
        int Multiply(int x, int y);
        int Add(int x, int y);
    }


    [ClassInterface(ClassInterfaceType.None)]
    public class Calc : Math
    {
        public int Multiply(int x, int y)
        {
            return(x * y);
        }


        public int Add(int x, int y)
        {
            return (x + y);
        }
    }
}
The public interface is what is shown in VB6 when you look at the referenced class's methods and properties. If the interface is excluded, the class shows up as not having any way to access the methods (interface-less).

The only real kicker is the [ClassInterface(ClassInterfaceType.None)] line that eliminates extraneous class interfaces, including the desired Multiply and Add functions from showing up in the interface. You can verify this by making the removing "public" from in front of the interface declaration.

However, because a public interface is declared with Multiply and Add, those interface elements show up in VB6 as if they were the actual functions, and because the class that inherits the ": Math" interface also contains the Multiply and Add functions, VB6 is none the wiser.

On the AssemblyInfo.cs page make sure you see the following line:
[assembly: ComVisible(true)]

If it is set to false, make the change. This can also be done from within the Visual Studio environment under the Project/Properties menu. Choose to "Register for COM interop" on the Build tab, and on the Application tab under Assembly Information, make sure to check that "Make assembly COM-Visible" is checked.

Note: the C# DLL was saved as testClass.dll.


VB6 code:

Option Explicit
Private o As testClass.Calc
Private Sub Form_Load()
    Set o = New testClass.Calc
    Debug.Print "testClass.Calc"
    Debug.Print o.Add(5, 10)
    Debug.Print o.Multiply(5, 5)
    Set o = Nothing
End Sub

Yes the VB6 end of the equation really is that simple. In the VB6 IDE, just add the reference to the .tlb that was created in C# under Project/References (menu) and you're good to go.

VB6 to C# (CSharp) - The Journey Begins!

As a long time Visual Basic 6 (VB6) developer, I have put off transitioning from VB6 to C# (C Sharp) until now. Now seems to be the right time to make the gradual transition to C# due to my need for a true multi-core programming solution and the feeling that it's just time to update my skills. This blog will chronicle my journey.

As a VB6 user, why have I chosen to learn C# and not VB.NET? After evaluating the .NET framework and learning that VB.NET really is a totally different language, and that VB.NET does not have the ability to write unmanaged code I started looking at C#. I've been reading the Windows API documentation for years and as a result have some familiarity with C. I have also done some work in a couple of C-like scripting languages, including MQL4 for Metatrader. I really like the tight, compact syntax of C, but also like having a managed environment, which rules out C++. The quirkiness of C++ is also a turn off. I also felt that learning C# would be a better way to round out my development skill set and to make myself more marketable. Since I already know VB6, after learning the ins and outs of C#, I feel it will be fairly straightforward to code in VB.NET if need be.

I consider my VB6 level of proficiency to be expert (modesty). I regularly use Windows APIs to enhance the speed and capability of VB6. I don't write my own type libraries or anything crazy like that, but I have found that there are few problems that can't be solved with VB6. That being said, since starting this journey learning C#, my eyes have been opened to how VB6 is really a subset of a full or complete programming language. I won't call it a toy programming language but perhaps a dwarf is a more accurate term. That being said, I still enjoy coding in VB6 and plan to make use of both technologies, while upgrading new development to C#.

Thanks for coming along for the ride. I hope we both learn something!