In an article titled Optimize string handling in VB6 - Part I, under the sub-section "Store strings in .res files", the author discusses storing strings in a .res file which requires only 1 byte per character compared to 2 bytes for string literals in VB6.
The article is on VB6 but I'm looking at application of this in VBA (which is based on VB6).
You can't - not directly anyway. In VB6 .res files are one of the various component types you can add to your project, but in VBA the striped-down IDE doesn't have the Resource Editor feature (not sure about the actual name of it, it's been a little while..), without a doubt a direct ancestor of the .resx designer in modern Visual Studio.
You technically could use .res files with your VBA project, but then you might as well use the more modern .resx format, which MSXML technically should be able to work with... although, even in .NET we don't typically manipulate .resx files directly, and there's a code generator involved turning each resource key into a property of a generated class - that's how this:
<data name="SelectAll_Button" xml:space="preserve">
<value>Select All</value>
</data>
...automagically turns into this:
/// <summary>
/// Looks up a localized string similar to Select All.
/// </summary>
public static string SelectAll_Button {
get {
return ResourceManager.GetString("SelectAll_Button", resourceCulture);
}
}
The advantage of using .res/.resx files for your resource strings from the start, even when your application "will only ever need en-US strings", is mainly about separation of concerns: resource strings aren't code, they're data, and data doesn't belong in code.
Now, in VBA-land, distributing an Excel workbook with a .resx file alongside is... meh. .res/.resx is just a data format, what matters is the problem it's solving: storage for resource keys. And in VBA-land hosted in Excel, that's easily a table on some hidden worksheet.
And then ResourceManager can be a VBA class whose responsibility would be to go into that hidden table (possibly caching the content into some dictionary to avoid repeatedly hitting the worksheet) and get the given string for the given culture key ("en-US", "fr-CA", "de-DE", etc.), although LCID codes might make things easier to integrate with in COM-land (e.g. the native/Win32 APIs that work with such IDs).
Imagine you're building a complex in-house app, and do that from the start: you separate the displayable string content from the code, and build everything that way. One day the company acquires a company in Spain, or opens an office in Germany, and suddenly your app needs to be translated.
Because localization was a concern all along, all you have to do is to translate the resource strings, and poof!, the app knows there's a new column in the hidden table and thus a new language to choose from, and everything "just works" and life is great. You kindly unhide the worksheet and send them as-is with instructions.
If localization was an afterthought... my sincere sympathies... this won't be fun. #BTDT