What is better in Excel VBA in terms of less memory allocation, better memory management and less memory usage:
(1) to use a static variable for the Range.Value which I have to update every time on worksheet change,
Dim myValue as Integer: myValue = Range("XYZ").Value: Debug.Print myValue
(2) or pointer to the Range object?
Dim myValue as Range: Set myValue = Range("XYZ"): Debug.Print myValue.Value
Honestly: You shouldn't care about it. Use whatever is more convenient for you.
Some remarks:
Local variables are always thrown away anyhow when a routine ends, so even if one of those variables would consume more memory, it doesn't matter.
Object variables are references (kind of pointer) and consume only the memory that is needed to that reference.
I highly doubt that there is any difference in speed for both attempts, and even if there is one, you can measure it only when you repeat the code a million times or so.
Your integer variable is not static. A static variable is a local variable that survives the end of a subroutine. It is used only very seldom, and in your case it makes no sense to have it static.
Even with a large amount of data, you usually shouldn't care about memory consumption. On every modern computer, using some MByte of memory temporarily doesn't matter at all. If you have to deal with a lot of data, read it into an Array before you process it - that speeds up execution time dramatically.
In VBA, forget about Integer and always use Long.