The Excel Development Platform: December 2018

Friday, 28 December 2018

VBA - Persistence - CopyMemory can be used to serialise state

First, a health warning, the code in this blog comes with no warranty. Microsoft would say that copying memory can have unpredictable results. Nevertheless, if you are fascinated by VBA then you might be interested in the guts of VBA.

In the previous post I showed how LSet can be used to copy the state from a user-defined type (UDT) into a byte array and vice versa. This can be used in distributed systems but also one could save the byte array to a file and then load into a subsequent Excel session. Thus it is possible to persist a (UDT) to file, and if you use classes then you can gather all your class level variables into a UDT. One can begin to see how a whole object graph (i.e. the state of a whole hierarchy of classes) might be persist-able.

At the end of the previous post I introduced the CopyMemory() function and I used it to replace the copying of a byte array.

The copied byte array was located in a companion UDT defined solely for LSet-ting to the real UDT. I wondered if I could cut out the middle step and use CopyMemory() to copy directly from the byte array to the real UDT. I have conducted some experiments and you can so long as you use Byte arrays for your strings. Details of my experimental code follow.

modTestCopyStringToBytes Standard Module

So we need to treat strings differently because they are not inherently contiguous, we need to convert them to a byte array but this is not too difficult. What is important is ensuring that no overwriting of other memory happens. In the procedure TestCopyStringToBytes() below you can see that we find the length of the array and the length of the string we're asked to copy and we truncate the string if the byte array is not large enough. We copy over only safe number of bytes. The code has plenty of comments.

Option Explicit
'* No Warranty, use this code at your own risk!
Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (hpvDest As Any, hpvSource As Any, ByVal cbCopy As Long)
'*
'* Not used in the main code.
'* Illustrates how strings have be treated carefully as an array of bytes
'*
Private Sub TestCopyStringToBytes()
'* set up string
Dim sRHS As String
sRHS = "hello world"
'* defining the string length here but it could passed in and thus unknown
Dim abStringAsBytes(0 To 9) As Byte
'* standard array length determination, I know it is fixed above but ...
'* ... in other cases the array could be passed in and be of unknown length
Dim lMaxLen As Long
lMaxLen = (UBound(abStringAsBytes) - LBound(abStringAsBytes) + 1)
'* we'd like to not overwrite the allocated memory so truncate the string
'* remember strings are two bytes for each character so we divide by 2
Dim sSafeRHS As String
sSafeRHS = Left$(sRHS, lMaxLen / 2)
'* give a truncation warning to avoid shock
If lMaxLen < Len(sRHS) Then Debug.Print "Warning contents of sRHS will be truncated from '" & sRHS & "' to '" & sSafeRHS & "'"
'* this next function will copy the memory from the string sSafeRHS to the first byte of the byte array, abStringAsBytes(0)
CopyMemory ByVal VarPtr(abStringAsBytes(0)), ByVal StrPtr(sSafeRHS), LenB(sSafeRHS)
'* this next line works thanks to VBA being nice and interpreting a byte array as a string for us
'* (saves us having to reverse the memory copy operation)
Debug.Print abStringAsBytes
End Sub

If you run the code you should get a truncation warning and the output of the copied truncated string like this

Warning contents of sRHS will be truncated from 'hello world' to 'hello'
hello

modCopyMemoryDirectToUdt Standard Module

Here is a a very full listing with plenty of debug code to illustrate what is going on. The code is heavily commented. The indented code in Main() is not strictly necessary but it serves as a guide as to what is going on.

Option Explicit
'* No Warranty, use this code at your own risk!
Private Const mlPAD As Long = 28 '* just for formatting
Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (hpvDest As Any, hpvSource As Any, ByVal cbCopy As Long)
'*
'* See here we only need one type per entity/class
'* DO NOT USE "aFixedString As String * 20" as it is not contiguous!
'* INSTEAD USE "aFixedStringAsBytes(0 To 19) As Byte"
'*
Public Type udtContiguousType
aLong As Long
anInt As Integer
aDouble As Double
aFixedStringAsBytes(0 To 9) As Byte '
aSecondLong As Long
aSecondDouble As Double
End Type
'*
'* The following property procedures could be adjusted to sit in a class
'* the class could store the string as a byte array in the class's state UDT
'* to the callers of the class the property could look like just a string
'*
Property Let MyFixedString(ByRef uType As udtContiguousType, ByVal sRHS As String)
'* array length determination
Dim lMaxLen As Long
lMaxLen = ByteArrayLen(uType.aFixedStringAsBytes)
'* we'd like to not overwrite the allocated memory so truncate the string
'* remember strings are two bytes for each character so we divide by 2
Dim sSafeRHS As String
sSafeRHS = Left$(sRHS, lMaxLen / 2)
'* give a truncation warning to avoid shock
If lMaxLen < Len(sRHS) Then Debug.Print "Warning contents of sRHS will be truncated from '" & sRHS & "' to '" & sSafeRHS & "'"
'* this next function will copy the memory from the string sSafeRHS ...
'* ... to the first byte of the byte array, uType.aFixedStringAsBytes(0)
CopyMemory ByVal VarPtr(uType.aFixedStringAsBytes(0)), ByVal StrPtr(sSafeRHS), LenB(sSafeRHS)
End Property
Property Get MyFixedString(ByRef uType As udtContiguousType) As String
'* this line works thanks to VBA being nice and interpreting a byte array as a string for us
'* (saves us having to reverse the memory copy operation)
MyFixedString = uType.aFixedStringAsBytes
End Property
'*
'* the main code, run this code
'*
Private Sub Main()
Dim bDebug As Boolean
bDebug = True
Dim uTypeSrc As udtContiguousType
Dim uTypeDest As udtContiguousType
'* set some values just like any other UDT, except for the strings
uTypeSrc.aLong = 1
uTypeSrc.anInt = 2
uTypeSrc.aDouble = 3.141
uTypeSrc.aSecondLong = -434634634
uTypeSrc.aSecondDouble = Sqr(10) * -1
'* we have to treat strings specially
'* the string's special treatment is abstracted by the property procedures
MyFixedString(uTypeSrc) = "hello world"
If bDebug Then
'* we have to be aware of truncation
Debug.Assert MyFixedString(uTypeSrc) = Left$("hello world", ByteArrayLen(uTypeSrc.aFixedStringAsBytes) / 2)
'* for illustration we print the memory addresses so that one can see the udt's memory layout , not strictly necessary
PrintMemoryAddresses "uTypeSrc", uTypeSrc
'* these next lines highlight the byte length of each member
'* not strictly necessary
Debug.Assert VarPtr(uTypeSrc) = VarPtr(uTypeSrc.aLong)
Debug.Assert VarPtr(uTypeSrc.anInt) - VarPtr(uTypeSrc.aLong) = 4
Debug.Assert VarPtr(uTypeSrc.aDouble) - VarPtr(uTypeSrc.anInt) = 4
Debug.Assert VarPtr(uTypeSrc.aFixedStringAsBytes(0)) - VarPtr(uTypeSrc.aDouble) = 8
Debug.Assert VarPtr(uTypeSrc.aSecondLong) - VarPtr(uTypeSrc.aFixedStringAsBytes(0)) =
BytesAlignedLen(ByteArrayLen(uTypeSrc.aFixedStringAsBytes))
Debug.Assert VarPtr(uTypeSrc.aSecondDouble) - VarPtr(uTypeSrc.aSecondLong) = 4
'* for illustration we print the memory addresses so that one can see the udt's memory layout , not strictly necessary
PrintMemoryAddresses "uTypeDest", uTypeDest
'* more layout illustration
Debug.Assert VarPtr(uTypeDest) = VarPtr(uTypeDest.aLong)
End If
CopyMemory ByVal (VarPtr(uTypeDest.aLong)), ByVal (VarPtr(uTypeSrc.aLong)), LenB(uTypeSrc)
If bDebug Then
Debug.Assert MyFixedString(uTypeDest) = Left$("hello world", ByteArrayLen(uTypeSrc.aFixedStringAsBytes) / 2)
Debug.Assert uTypeDest.aLong = uTypeSrc.aLong
Debug.Assert uTypeDest.anInt = uTypeSrc.anInt
Debug.Assert uTypeDest.aDouble = uTypeSrc.aDouble
Debug.Assert MyFixedString(uTypeDest) = MyFixedString(uTypeSrc)
Debug.Assert uTypeDest.aSecondLong = uTypeSrc.aSecondLong
Debug.Assert uTypeDest.aSecondDouble = uTypeSrc.aSecondDouble
End If
Stop 'please go look at the Locals window
End Sub
'*
'* standard array length calculation
'*
Private Function ByteArrayLen(ByRef abBytes() As Byte) As Long
ByteArrayLen = (UBound(abBytes()) - LBound(abBytes()) + 1)
End Function
'*
'* each member of a user defined type is 32-bit aligned so this function calcs the aligned byte length
'*
Private Function BytesAlignedLen(ByVal cbByteCount As Long) As Long
If cbByteCount \ 4 = cbByteCount / 4 Then
BytesAlignedLen = cbByteCount
Else
BytesAlignedLen = ((cbByteCount \ 4) + 1) * 4
End If
End Function
'*
'* this prints the memory addresses so one can see the memory laid out
'*
Private Function PrintMemoryAddresses(ByVal sVarName As String, ByRef uType As udtContiguousType)
Debug.Print()
Debug.Print Pad("&" & sVarName & "", mlPAD) & " = " & VarPtr(uType)
Debug.Print Pad("&" & sVarName & ".aLong", mlPAD) & " = " & VarPtr(uType.aLong) & " a Long has 4 bytes"
Debug.Print Pad("&" & sVarName & ".anInt", mlPAD) & " = " & VarPtr(uType.anInt) &
" an Int has 2 bytes but is 32-bit aligned so has 4 bytes"
Debug.Print Pad("&" & sVarName & ".aDouble", mlPAD) & " = " & VarPtr(uType.aDouble) & " a Double has 8 bytes"
Dim lMaxLen As Long
lMaxLen = ByteArrayLen(uType.aFixedStringAsBytes)
Dim lAlignedMaxLen As Long
lAlignedMaxLen = BytesAlignedLen(lMaxLen)
Dim sByteLen As String
sByteLen = " this byte array has " & lMaxLen & " bytes" &
VBA.IIf(lAlignedMaxLen <> lMaxLen, " but is 32-bit aligned to " & lAlignedMaxLen & " bytes", "")
Debug.Print Pad("&" & sVarName & ".aFixedStringAsBytes", mlPAD) & " = " & VarPtr(uType.aFixedStringAsBytes(0)) & sByteLen
Debug.Print Pad("&" & sVarName & ".aSecondLong", mlPAD) & " = " & VarPtr(uType.aSecondLong) & " a Long has 4 bytes"
Debug.Print Pad("&" & sVarName & ".aSecondDouble", mlPAD) & " = " & VarPtr(uType.aSecondDouble) & " a Double has 8 bytes"
Debug.Print Pad("&" & sVarName & " total length", mlPAD) & " = " & LenB(uType)
End Function
'*
'* this pads a string to help it looks columnar
'*
Private Function Pad(ByVal sText As String, ByVal lLen As Long) As String
Pad = Left$(sText & String(lLen, " "), lLen)
End Function

So when the Main() code runs we have two things to look at (1) the Immediate Window and (2) the Locals Window. Here are mine, firstly the Immediate Window

Warning contents of sRHS will be truncated from 'hello world' to 'hello'

&uTypeSrc                    = 5240168
&uTypeSrc.aLong              = 5240168 a Long has 4 bytes
&uTypeSrc.anInt              = 5240172 an Int has 2 bytes but is 32-bit aligned so has 4 bytes
&uTypeSrc.aDouble            = 5240176 a Double has 8 bytes
&uTypeSrc.aFixedStringAsByte = 5240184 this byte array has 10 bytes but is 32-bit aligned to 12 bytes
&uTypeSrc.aSecondLong        = 5240196 a Long has 4 bytes
&uTypeSrc.aSecondDouble      = 5240200 a Double has 8 bytes
&uTypeSrc total length       = 40

&uTypeDest                   = 5240128
&uTypeDest.aLong             = 5240128 a Long has 4 bytes
&uTypeDest.anInt             = 5240132 an Int has 2 bytes but is 32-bit aligned so has 4 bytes
&uTypeDest.aDouble           = 5240136 a Double has 8 bytes
&uTypeDest.aFixedStringAsByt = 5240144 this byte array has 10 bytes but is 32-bit aligned to 12 bytes
&uTypeDest.aSecondLong       = 5240156 a Long has 4 bytes
&uTypeDest.aSecondDouble     = 5240160 a Double has 8 bytes
&uTypeDest total length      = 40

Now here is my Locals Window screenshot with the variables expanded to show the members. It shows the contents successfully copied from uTypeSrc to uTypeDest.

Commentary

It is interesting that VBA could potentially participate in a persistence scheme. I am minded to investigate the persistence interfaces of the COM specification such as IPersist to see if VBA could indeed play in this league. One problem is that IPersist has non Automation types so one would need to define a proxy component and a proxy interface.

Final Warranty Warning

I can't give a warranty for this code (nor any code on this blog for that matter).

Thursday, 27 December 2018

VBA - Persistence - Use LSet to serialise user defined type to a byte array

First, a health warning; there is no warranty for this code in this blog post; use at your own risk.

Transmitting State In a Distributed System

Years ago, I encountered a technique from a great technical author called Rockford Lhotka who wrote a classic VB6 book called Professional Visual Basic 6 Distributed Objects; as its title suggests it's concerned with using VB6 to build systems that distributed objects across machines. VBA is a version of VB6 so the techniques found therein apply to Excel Developers. In this blog post (and some planned future posts), I comment upon those techniques; specifically the serialisation/persistence technique.

In a distributed system, at some point one inevitably passes an object as a parameter and this raises the potential problem of excessive network traffic. So, if an instance of Class1 residing on MachineA wants to call an instance of Class2 residing on MachineB and passes as a parameter a pointer to an instance of Class3 which was created on MachineA then MachineB will make network calls to query the state of Class3. If these network calls are excessive then you will want to redesign your system to instead serialise the state of Class3 to a byte stream (or other state vessel), pass the byte stream over the network as the parameter, instantiate a duplicate of Class3 on MachineB initialising its state from the passed parameter byte stream and make local calls to the local duplicate. If you change the state of Class3 you'll need to serialise the state and return it back to Machine1 back so its original instance of Class3 is synchronised.

As you can imagine designing distributed systems can be hard and actually this is not what this blog post is to be about. This blog post is meant to be about the persistence mechanism used by Rockford Lhotka, using the LSet keyword. But if you're interested in this problem, Rockford Lhotka, has gone to become a big name in the problem space of distributed systems, promoting his solution Component-based Scalable Logical Architecture (CSLA) an early version of which is found in the above referenced book.

The key takeaway from this section is that there are techniques to serialise a (VB6/VBA) class's state to a an array of bytes and then instantiate a duplicate. Just bear that in mind as a use case for the code below.

Microsoft seems to frown upon LSet (and by implication Rockford Lhotka's CSLA)

Above, I've written above quite a lot about Rockford Lhotka because his ideas seems to be a little at odds with the the official Microsoft documentation on LSet, here's an eye-catching quote ...

Using LSet to copy a variable of one user-defined type into a variable of a different user-defined type is not recommended.

But this is exactly how Rockford Lhotka achieves his trick of serialisation. We'll move onto an example before discussing the point at issue further.

Sample LSet to Byte Array Code

So how does Rockford Lhotka serialise state in VB6 and VBA? Here is some code which demonstrates an instance of a user-defined-type being serialised and the state then used in turn to create a second identical instance of the same user-defined-type.

modUdtToByteArrayByLSet Standard Module

Option Explicit
'* No Warranty, use this code at your own risk!
'Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (hpvDest As Any, hpvSource As Any, ByVal cbCopy As Long)
Public Type udtMyType
aLong As Long
anInt As Integer
aDouble As Double
aFixedString As String * 10
aSecondLong As Long
aSecondDouble As Double
End Type
Public Type udtByteArray48
value(0 To 47) As Byte
End Type
Private Sub TestSaveToByteArray()
Dim uContiguousType As udtMyType
uContiguousType.aLong = 1
uContiguousType.anInt = 2
uContiguousType.aDouble = 3.141
uContiguousType.aFixedString = "Hello"
uContiguousType.aSecondLong = -434634634
uContiguousType.aSecondDouble = Sqr(10) * -1
Dim abSaved() As Byte
abSaved() = SaveToByteArray(uContiguousType, 48)
Dim uContiguousType2 As udtMyType
LoadFromByteArray abSaved(), uContiguousType2, 48
Debug.Assert uContiguousType2.aDouble = uContiguousType.aDouble
Debug.Assert uContiguousType2.aFixedString = uContiguousType.aFixedString
Debug.Assert uContiguousType2.aLong = uContiguousType.aLong
Debug.Assert uContiguousType2.anInt = uContiguousType.anInt
Debug.Assert uContiguousType2.aSecondLong = uContiguousType.aSecondLong
Debug.Assert uContiguousType2.aSecondDouble = uContiguousType.aSecondDouble
Stop
End Sub
'******************************************************************************************************
'*
'* Persistence routines, you'll need these three for each type you intend to persist
'* and customise the signatures
'*
'******************************************************************************************************
Public Sub LoadFromByteArray(ByRef abBytes() As Byte, ByRef puContiguousType As udtMyType, ByVal lSizeOf As Long)
CheckSizeOf puContiguousType, lSizeOf
Dim uByteArray48 As udtByteArray48
'CopyMemory ByVal (VarPtr(uByteArray48.value(0))), ByVal (VarPtr(abBytes(0))), lSizeOf
Dim idx As Long
For idx = 0 To lSizeOf - 1
uByteArray48.value(idx) = abBytes(idx)
Next idx
LSet puContiguousType = uByteArray48
End Sub
Private Sub CheckSizeOf(ByRef puContiguousType As udtMyType, ByVal lSizeOf As Long)
Dim uByteArray48 As udtByteArray48
If Not LenB(puContiguousType) = LenB(uByteArray48) Then Err.Raise vbObjectError, , "#size of byte arrays do not match!"
If Not LenB(puContiguousType) = lSizeOf Then Err.Raise vbObjectError, , "#size of byte arrays do not match size_of!"
End Sub
Public Function SaveToByteArray(ByRef puContiguousType As udtMyType, ByVal lSizeOf As Long) As Byte()
CheckSizeOf puContiguousType, lSizeOf
Dim uByteArray48 As udtByteArray48
LSet uByteArray48 = puContiguousType
SaveToByteArray = uByteArray48.value
End Function

So to run the above code run TestSaveToByteArray() whose Stop statement conveniently allows inspection of the Locals Window which should look something like that below which shows two instances of the user-defined type myType with identical contents but the second has been populated from a byte array calculated from the first. Both reading and writing the byte array to the user-defined-typed is done with LSet...

Commentary

So whilst the above code works there is quite a lot with which I am unhappy. Primarily, if your UDT is 48 bytes long then you need to separately define another type, the serialisation type, to have one single member byte array of length 48 bytes. Now, I'm assuming that this technique implies that each class has a UDT to contain the state for the class (for I do not know any trick to access the state of a VBA class, if you know do please comment below). So imagine if you have twenty classes in your project of varying byte lengths then you'd need twenty separate UDTs for serialisation to cope with the varying lengths.

For each class, one would need the three persistence routines given in the code: LoadFromByteArray(), CheckSizeOf() and SaveToByteArray()

I just wonder if we can do better than this.

One immediate change I see is to optimise the copying of the array as given in LoadFromByteArray(). Iterating through each byte in the byte array in VBA is clearly sub-optimal, much better here to copy memory directly because an array of bytes is guaranteed to be contiguous. So there is a replacement for the array loop on lines 57-60, you need to uncomment line 56 and line 03; then comment out line 57-60.

But if you cross the Rubicon and start using CopyMemory() then aren't even more optimisations available? This will be the subject of the following posts.

Wednesday, 5 December 2018

VBA - WIN32 API : Parse strings to numbers

VBA can call into the COM run-time parsing functions to get some serious help with parsing. In this example I show string being parsed to a numeric type. This code also handles exponential notation in case you not sufficiently impressed.

One point of note is that if you pass a number with 4 or fewer decimal places then the COM runtime will by default parse to the Currency type. I think most programmers would expect as Double so I've added some post-processing code whilst keeping the Currency option open.

If you really want a currency then I suggest you read this blog post where I parse currencies.

modVarNumFromStr Standard Module

Option Explicit
'*
'* Brought to you by the Excel Development Platform blog
'* https://exceldevelopmentplatform.blogspot.com/2018/12
'*
Private Type NUMPARSE
cDig As Long
dwInFlags As Long
dwOutFlags As Long
cchUsed As Long
nBaseShift As Long
nPwr10 As Long
End Type
Private Declare Function VarParseNumFromStr Lib "oleaut32" (ByVal strIn As Long, ByVal LCID As Long, _
ByVal dwFlags As Long, ByRef numprs As NUMPARSE, ByRef rgbDig As Byte) As Long
Private Declare Function VarNumFromParseNum Lib "oleaut32" (ByRef pnumprs As NUMPARSE, _
ByRef rgbDig As Byte, ByVal dwVtBits As Long, ByRef pvar As Variant) As Long
Sub TestVarNumFromStr()
Dim v(0 To 9)
v(0) = VarNumFromStr("14")
Debug.Assert v(0) = 14
Debug.Assert VarType(v(0)) = VbVarType.vbInteger
v(1) = VarNumFromStr("15.1234")
Debug.Assert v(1) = 15.1234
Debug.Assert VarType(v(1)) = VbVarType.vbCurrency
v(2) = VarNumFromStr("15.11111")
Debug.Assert v(2) = 15.11111
Debug.Assert VarType(v(2)) = VbVarType.vbDouble
v(3) = VarNumFromStr("1.512E6")
Debug.Assert v(3) = 1512000
Debug.Assert VarType(v(3)) = VbVarType.vbLong
v(4) = VarNumFromStr("1.512E-6")
Debug.Assert v(4) = 0.000001512
Debug.Assert VarType(v(4)) = VbVarType.vbDouble
Stop
End Sub
Public Function VarNumFromStr(ByRef sText As String, Optional bConvertCurrencyToDouble As Boolean = True) As Variant
Dim rgbDig() As Byte
Dim uNUMPARSE As NUMPARSE
Dim lLen As Long
lLen = VBA.Len(sText)
If lLen > 0 Then
ReDim rgbDig(lLen - 1)
'* https://technet.microsoft.com/ru-ru/ms221690(v=vs.90)
With uNUMPARSE
.cDig = lLen
.dwInFlags = &H1FFF& 'NUMPRS_STD
End With
'* https://docs.microsoft.com/en-us/windows/desktop/api/oleauto/nf-oleauto-varparsenumfromstr
If VarParseNumFromStr(StrPtr(sText), 0, 0, uNUMPARSE, rgbDig(0)) = 0 Then
'* https://docs.microsoft.com/en-us/windows/desktop/api/oleauto/nf-oleauto-varnumfromparsenum#vtbit_decimal
If VarNumFromParseNum(uNUMPARSE, rgbDig(0), 2047, VarNumFromStr) = 0 Then
'* do we need any post-processing
If bConvertCurrencyToDouble And InStr(1, sText, ".", vbTextCompare) > 0 And _
VarType(VarNumFromStr) = vbCurrency Then
VarNumFromStr = CDbl(VarNumFromStr) '* convert decimal currency
End If
Else
Err.Raise vbObjectError, , "#Error whilst parsing '" & sText & "'!"
End If
Else
Err.Raise vbObjectError, , "#Error whilst parsing '" & sText & "'!"
End If
End If
End Function

HTML5 Animations with CSS & VBA: Fading in text

Browser GUI technologies are clearly better than VBA's. Html5 is a wonderful leap forward with its animation capabilities. However, sometimes you'll want some code to help avoid fiddling with CSS. Why not VBA?

First Message

Second Message

Third Message

Fourth Message

So I hope you just seen the above messages fade in one after the after. How is this achieved? Firstly, there is the opacity attribute (0 = invisible, 1 = visible). Then we have to animate the opacity and for that need the animation-name, animation-delay and animation-duration attributes. We also need a @keyframes rule to give the schedule for the opacity to change over time, in this example given as percentages. The animation-name attribute must tie in to the identifier given in the @keyframes rule. What follows is the source to drive this simple animation.

HTML to fade in text messages

<style>
.allText {
font-size: 2rem;
font-family: sans-serif;
color: #000000;
}
@keyframes FadeIn10_20 {
0% { opacity: 0; }
10% { opacity: 0; }
20% { opacity: 1; }
100% { opacity: 1; }
}
.text0 {
animation-name: FadeIn10_20;
animation-delay: 0s;
animation-duration: 10s;
}
@keyframes FadeIn30_40 {
0% { opacity: 0; }
30% { opacity: 0; }
40% { opacity: 1; }
100% { opacity: 1; }
}
.text1 {
animation-name: FadeIn30_40;
animation-delay: 0s;
animation-duration: 10s;
}
@keyframes FadeIn50_60 {
0% { opacity: 0; }
50% { opacity: 0; }
60% { opacity: 1; }
100% { opacity: 1; }
}
.text2 {
animation-name: FadeIn50_60;
animation-delay: 0s;
animation-duration: 10s;
}
@keyframes FadeIn70_80 {
0% { opacity: 0; }
70% { opacity: 0; }
80% { opacity: 1; }
100% { opacity: 1; }
}
.text3 {
animation-name: FadeIn70_80;
animation-delay: 0s;
animation-duration: 10s;
}
</style>
<div class='allText text0'>First Message</div>
<div class='allText text1'>Second Message</div>
<div class='allText text2'>Third Message</div>
<div class='allText text3'>Fourth Message</div>

Initially, I struggled with the animation directives, I grew frustrated editing source CSS manually so I wrote some VBA to help set the timings because a change in duration of one message had a knock-on effect on the following messages. I needed some code to total the timings in seconds and then express the timing of the animation events as percentages.

I am aware that there are CSS pre-processors out there such as LESS and SASS but I'd rather use VBA. Here is the source

modCSSFadeInAnimation Standard Module

Option Explicit
Option Private Module
'*
'* Brought to you by the Excel Development Platform Blog
'* http://exceldevelopmentplatform.blogspot.com/2018/12/
'*
Private mdicLines As Scripting.Dictionary
Private Type udtMessage
sText As String
dStart As Double 'In seconds
dFadeIn As Double 'In seconds
lStartPercentage As Long
lFadeInPercentage As Long
lTop As Long
dDuration As Double 'In seconds
End Type
Private Type udtMessages
Messages() As udtMessage
End Type
Private mlTotalSeconds As Long
Private Sub Main()
Dim bFullHtmlDocument As Boolean
bFullHtmlDocument = False '*<---- change to True to get a full html document instead of a fragment
Dim uMessages As udtMessages
AddMessage uMessages, 10, 2, "First Message", 2
AddMessage uMessages, 20, 2, "Second Message"
AddMessage uMessages, 30, 2, "Third Message"
AddMessage uMessages, 40, 2, "Fourth Message"
CalcFades uMessages
OpenStyleTag bFullHtmlDocument
If bFullHtmlDocument Then WritePositioningCSS uMessages
WriteAnimationCSS uMessages ', 30
WriteEndOfStyleBlock bFullHtmlDocument
WriteBody uMessages, bFullHtmlDocument
Debug.Print Join(mdicLines.Items, vbNewLine)
End Sub
Private Sub AddMessage(ByRef uMessages As udtMessages, ByVal lTop As Long, ByVal dDuration As Double, ByVal sText As String,
Optional dStart0 As Double)
Dim lIndex As Long
If Not IsArrayInitialized(uMessages) Then
'* not yet initialised
lIndex = 0
ReDim uMessages.Messages(0 To 0) As udtMessage
Else
lIndex = UBound(uMessages.Messages) + 1
ReDim Preserve uMessages.Messages(0 To lIndex) As udtMessage
End If
If lIndex = 0 Then
uMessages.Messages(lIndex).dStart = dStart0
Else
uMessages.Messages(lIndex).dStart = uMessages.Messages(lIndex - 1).dStart + uMessages.Messages(lIndex - 1).dDuration
End If
uMessages.Messages(lIndex).sText = sText
uMessages.Messages(lIndex).lTop = lTop
uMessages.Messages(lIndex).dDuration = dDuration
End Sub
Private Function IsArrayInitialized(ByRef uMessages As udtMessages)
On Error GoTo ErrHand
Dim lUbound As Long
lUbound = UBound(uMessages.Messages)
IsArrayInitialized = True
Exit Function
ErrHand:
End Function
Private Sub CalcFades(ByRef uMessages As udtMessages, Optional dDefaultFade As Double = 1)
Dim lLoop As Long
For lLoop = LBound(uMessages.Messages) To UBound(uMessages.Messages)
If uMessages.Messages(lLoop).dFadeIn = 0 Then
uMessages.Messages(lLoop).dFadeIn = uMessages.Messages(lLoop).dStart - dDefaultFade
End If
If uMessages.Messages(lLoop).dFadeIn < 0 Then uMessages.Messages(lLoop).dFadeIn = 0
Next
mlTotalSeconds = uMessages.Messages(UBound(uMessages.Messages)).dStart +
uMessages.Messages(UBound(uMessages.Messages)).dDuration
For lLoop = LBound(uMessages.Messages) To UBound(uMessages.Messages)
uMessages.Messages(lLoop).lStartPercentage = 100 * uMessages.Messages(lLoop).dStart / mlTotalSeconds
uMessages.Messages(lLoop).lFadeInPercentage = 100 * uMessages.Messages(lLoop).dFadeIn / mlTotalSeconds
Next
End Sub
Private Sub OpenStyleTag(Optional bWriteHtmlTags As Boolean = False)
Set mdicLines = New Scripting.Dictionary
If bWriteHtmlTags Then
AddLine "<!DOCTYPE html>"
AddLine "<html>"
AddLine "<head>"
AddLine "<title>Presentation</title>"
AddLine "<meta name='viewport' content='width=device-width, initial-scale=1'></meta>"
End If
AddLine "<style>"
AddLine ".allText {"
AddLine " font-size:2rem;"
AddLine " font-family:sans-serif;"
AddLine " color: #000000;"
AddLine "}"
AddLine ""
End Sub
Private Sub WritePositioningCSS(ByRef uMessages As udtMessages)
Dim lLoop As Long
For lLoop = LBound(uMessages.Messages) To UBound(uMessages.Messages)
AddLine ".text" & lLoop & " {"
AddLine " position: absolute;"
AddLine " left: 5%;"
If uMessages.Messages(lLoop).lTop = 0 Then
AddLine " top: " & 5 * (lLoop + 1) & "%;"
Else
AddLine " top: " & uMessages.Messages(lLoop).lTop & "%;"
End If
AddLine "}"
AddLine ""
Next
End Sub
Private Sub WriteAnimationCSS(ByRef uMessages As udtMessages, Optional lTotalSeconds As Variant)
If Not IsMissing(lTotalSeconds) Then mlTotalSeconds = lTotalSeconds
Dim lLoop As Long
For lLoop = LBound(uMessages.Messages) To UBound(uMessages.Messages)
'* write keyframe, store keyframe name
Dim sKeyFrameName As String
KeyFramesFadeIn uMessages.Messages(lLoop).lFadeInPercentage, uMessages.Messages(lLoop).lStartPercentage, sKeyFrameName
AddLine ".text" & lLoop & " {"
AddLine " animation-name: " & sKeyFrameName & ";"
AddLine " animation-delay: 0s;"
AddLine " animation-duration: " & mlTotalSeconds & "s;"
AddLine "}"
AddLine ""
Next
End Sub
Private Sub WriteEndOfStyleBlock(Optional bWriteHeadTag As Boolean = False)
AddLine "</style>"
If bWriteHeadTag Then AddLine "</head>"
End Sub
Private Sub WriteBody(ByRef uMessages As udtMessages, Optional bWriteHtmlAndBodyTags As Boolean = False)
If bWriteHtmlAndBodyTags Then AddLine "<body>"
Dim lLoop As Long
For lLoop = LBound(uMessages.Messages) To UBound(uMessages.Messages)
AddLine "<div class='allText text" & lLoop & "'>" & uMessages.Messages(lLoop).sText & "</div>" 'position: absolute;"
Next
If bWriteHtmlAndBodyTags Then
AddLine "</body>"
AddLine "</html>"
End If
End Sub
Private Function KeyFramesFadeIn(ByVal lStartPercent As Long, ByVal lEndPercent As Long, ByRef psKeyFrameName As String)
psKeyFrameName = "FadeIn" & Pad(lStartPercent, 2, "0") & "_" & Pad(lEndPercent, 2, "0")
AddLine "@keyframes " & psKeyFrameName & " {"
AddLine " 0% { opacity: 0; }"
AddLine " " & Pad(lStartPercent, 3, " ") & "% { opacity: 0; }"
AddLine " " & Pad(lEndPercent, 3, " ") & "% { opacity: 1; }"
AddLine " 100% { opacity: 1; }"
AddLine "}"
AddLine ""
End Function
Private Sub AddLine(ByVal sLine As String)
mdicLines.Add mdicLines.Count, sLine
End Sub
Private Function Pad(ByVal lNum As Long, ByVal lLen As Long, ByVal sChar As String) As String
If Len(CStr(lNum)) > lLen Then lLen = Len(CStr(lNum))
Pad = Right$(String$(lLen, sChar) & CStr(lNum), lLen)
End Function