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.