What better for memory usage: define constants in subs or in module

Question

I understand that is some of VB basics, but can't figure out how to ask google:

How VBA manage constants? In some languages compiler replace them by values. But how that works in VBA? Will it matter if i declare them in sub/function, not in global space. Especially, if a sub/function is called many times in runtime.

I'm often declare function name and some other strings as constants in sub/function space - it's easier for me to read my code. For example: SUB_NAME in Get_AppExcel_Ref() in code below, to use it for logging error events. If the Get_AppExcel_Ref() will be called couple times while programm running - SUB_NAME will be allocated in memory once, on first run, or on every call? Or maybe there is some kind perfomance issue and better declare SUB_NAME as global.

Private Sub Get_AppExcel_Ref(ByRef appObject As Object)
  Const SUB_NAME As String = "Get_AppExcel_Ref"    

  On Error GoTo ERR_NOT_OPENNED
  Set appObject = GetObject(, "Excel.Application")
  Exit Sub 

ERR_NOT_OPENNED:    
  If Err.Number = 429 Then
    Err.Clear
    Set appObject = CreateObject("Excel.Application")
  Else    
    Call LOG.printLog("open Excel", Err, SUB_NAME)
  End If    
End Sub 

'LOG - user class type variable, printLog params: Description, Error, Source Name

A `Const` declared local has local scope so it will not be known outside that scope. — Paul Ogilvie, Mar 25 '19 at 09:36
A `Const` such as your string, will be stored somewhere in memory (otherwise its value will not be known). So for memory usage it doesn't matter if you declare it local or global. — Paul Ogilvie, Mar 25 '19 at 09:37
A `Const` in VB is probably implemented as a read-only variable. It will exist only once in memory, independent of how often you call the fuction where it is declared. The compiler/interpreter may fold multiple identical Const values onto a single value, thus saving memory. But I wouldn't worry about these things for VB. — Paul Ogilvie, Mar 25 '19 at 09:42
I can't imagine why this code is actually necessary. If nothing else it won't work with a `Private Sub` followed by `End Function` — CallumDA, Mar 25 '19 at 10:47
CallumDA, this my mistake, sorry for misslead. Changed "function" to "sub" in my code, but forgot to do full replace in this example. Of course there - 'private sub: exit sub: end sub'. — XoR, Mar 25 '19 at 13:05

score 1 · Accepted Answer · answered Mar 25 '19 at 10:00

Declaring your Const as global will make no sense, as it would display the same string, no matter where you would use it.

You could declare it as a global variable (for example to save the extra parameter to you logging routine) and assign the name of the routine, but you would have the name of you routine as a (constant) string in your code also (so same amount of memory used). And at the end, it will completely mess up your logic, because when calling a subroutine, the content will be overwritten and when after that call an error occurs, your log will show the wrong routine name. So don't go that path.

As Paul Ogilivie writes in his comments, think about a constant as a read-only variable - and don't waste any thoughts about the exaxt implementation (but I think it is save to assume that the string is put only once in memory). You have more than enough memory available for your VBA code, and string handling is so fast that you will never experience any runtime issues.

My credo: Use everyhing as it fits your needs as programmer best - and readability is an important aspect to this. Don't care too much about memory consumption or runtime speed - except if you really hit problems. And if you do, these are most likely caused by other things.

CallumDA · Answer 2 · 2019-03-25T10:45:20.287

0

Firs of all, welcome to SO.

I think the VBA compiler does also replace constants with their values, as with other languages. So they are not the same as variables.

I don't think a constant is necessary here. I only tend to use them for parameters, not just to replace any string.

Your code would be just fine like this:

LOG.printLog "open Excel", Err, "GetAppExcelRef"

edited Mar 25 '19 at 10:45

answered Mar 25 '19 at 10:35

CallumDA

12,025
6
30
52

Thank you for answer. This is not best example, that i choose to show. And it's realy looks questionable to use const here vs put exact string in calling method. But there is some ground for that decision. It's like the scheme that i use to create methods. Copy/paste some abstract constructions (predefined const with method name and log calling) + some troubles with adequate naming at first approach led me to this solution. With my modest experience in programming that approach produce less mistakes. ;) Like using obsolete "Call" for better code reading in VBA IDE. – XoR Mar 25 '19 at 12:15

What better for memory usage: define constants in subs or in module

2 Answers2