By creating a Variable that is defined as a string with a fixed-length, is the used bytes per cell or range?

Question

as a relatively new user to Excel, I could not seem to find any confirmation if the a string with a fixed-length has the memory assigned per range or cell.

I am thinking it is per range, because I could not create a string with a fixed-length and set the range as the last cell in a row.

Ex:

Dim HilvlActivity as String * 3
HilvlActivitySource = Range("F3", "F:F").End(xlDown).Row

And instead, had to use

Dim HilvlActivity as String * 5000
HilvlActivitySource = Range("F3", "F:F").End(xlDown).Row

So my question is basically: is the assigned fixed-length definition per cell (Ex: F3) or per the entire assigned range?

I may be overthinking this, or should have coded the end of the row more efficiently (will change later). But this is still a basic concept that I want to make sure I understand. Some of the information I have looked into is John Walkenbach's book for Power Programming with VBA, Microsoft (https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/data-types/string-data-type), PowerSpreadSheets (https://powerspreadsheets.com/vba-data-types/#Long-VBA-Data-Type), but still can't seem to find the correct answer.

Anyone know of any good resources that really dives into variable details, it would be appreciate. Otherwise, thanks for the help! :)

Answer 1

Well, first: You haven't defined HilvlActivitySource as a variable anywhere...

The length of a fixed-length-string is applied to the string variable itself. For example:

Dim HilvlActivity as String * 3
HilvlActivity = ActiveSheet.Range("F3").Value
MsgBox Len(HilvlActivity)

will always show the message 3 - if F3 contains less than 3 characters, then there will be spaces added to the end. If F3 contains more than 3 characters, then only the first 3 will be stored.

Answer 2

Dim a as String * 10 means that the string a will be always of length 10 and if an assigned value goes above this length it will be cut to the first 10 chars.

This illustrates it:

Public Sub TestMe()

    Dim a   As String * 3
    Dim b   As String
    Dim c   As String * 10

    a = "ABCD"
    b = "ABCD"
    c = "ABCD"

    Debug.Print a                    'ABC
    Debug.Print b                    'ABCD
    Debug.Print Len(c)               '10  
    Range("A1") = StrReverse(c)      '      DCBA in range "A1" with 6 empty spaces upfront

End Sub