VBA One Integer is always a certain value greater than another

Question

I'm just curious to know if there was an easy way to do this in VBA.

Suppose I always use a counter variable in my loops, say i and I always want, whatever i's valuen to have another variable, say k a certain value above or beneath it.

So, for example:

Dim i as integer
Const JumpVal = 3
Dim k as integer

k = i + JumpVal ' !!And this is to be true regardless of i's value!!

But I would want k to always be JumpVal away from i... I know I could do this with a simple function taking i's value and returning an integer, but I was curious to know if there was another way to do this via assignment.

Hope this question makes sense!

Thanks!!

There's no way to set `k` as automatically being some increment of `i` without handling it in your code. But you don't really need k here anyway if you instead use `i + JumpVal`. — Tim Williams, Jul 21 '14 at 16:03
Thanks - I figured `i + jumpval` was the way, I was just more curious to know if there was another way... Thanks! — John Bustos, Jul 21 '14 at 16:39

score 1 · Accepted Answer · answered Jul 21 '14 at 16:54

1

You can't do it via assignment but, as you implied, you could write the following:

Private Const JumpVal as Integer = 3
Private i as Integer
Private Function k()
    k = i + JumpVal
End Function

And then you can use the function k just like you would if it were truely stored. I made i and JumpVal private in this example, but if you need to access them outside of your module, simply replace Private with Public.

Happy Coding!

answered Jul 21 '14 at 16:54

CodeJockey

1,922
1
15
20

That's not VBA, but the logic remains basically exactly the same... That's what I figured. Thanks! – John Bustos Jul 21 '14 at 17:27

score 1 · Answer 2 · edited May 23 '17 at 10:25

You could get close and always have k = i using pointers. This is hacky way purely for fun and not to be taken seriously!

More info on how this works is here

Private Declare PtrSafe Sub CopyMemory Lib "kernel32" _
    Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)

Private Declare PtrSafe Sub FillMemory Lib "kernel32" _
    Alias "RtlFillMemory" (Destination As Any, ByVal Length As Long, ByVal Fill As Byte)

Sub Test()
Dim i As Variant
Dim k As Variant
Const JumpVal = 3

    'NOTE k is only declared and not initialised.
    i = Array(56, JumpVal)

    CopyMemory k, i, 16

    Debug.Print k(0) + k(1)

    i(0) = 96

    Debug.Print k(0) + k(1)
    'or  Debug.Print Application.sum(k)

    FillMemory k, 16, 0

End Sub

LOL - Definitely a cute and "out the box" solution!!! ... Kind of an atom-bomb to kill a mouse, but it does do the trick!! +1 — John Bustos, Jul 21 '14 at 19:22

VBA One Integer is always a certain value greater than another

2 Answers2