VBA - Increment variable value not changing

Question

I'am writting a small macro for opening links in browsers tab. Every 10th link is opened in new window. Can you tell me why variable index is not changing its value?

Sub OpenHyperLinks()

Dim xHyperlink As Hyperlink
Dim WorkRng As Range
Dim MaxTabs As Integer
MaxTabs = 10
Static index As Integer
index = 0

On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)

For Each xHyperlink In WorkRng.Hyperlinks
    If index Mod MaxTabs = 0 Then
        xHyperlink.Follow NewWindow:=True
    Else
        xHyperlink.Follow NewWindow:=False
    End If
    Inc (index)
Next
End Sub

And incrementation function:

Function Inc(ByRef i As Integer)
   i = i + 1
End Function

Thanks fo help :)

David W · Accepted Answer · 2016-10-22 12:17:06Z

3

There's no immediate reason for the index value not being incremented. The "On Error Resume Next' may be masking an error condition that is preventing the increment code from being called. I'd temporarily remove or comment out the error handler to diagnose that possibility further.

There's no reason to move the code that increments 'index' into a separate function. Replace inc(Index) with index=index+1 and eliminate the increment function. Additionally, the Inc function really operates as a Sub, not a function, because it does not return a value to the caller.

answered Oct 22, 2016 at 12:17

David W

10.2k38 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Alex P Over a year ago

+1 Agree best way is to increment within the subroutine itself rather than have separate function. Other option is to declare a public variable e.g. Public index as Integer and then get separate sub to increment it

Wicia Over a year ago

Its is working :) Thank you! Now is next question: why new browser window is not openning?

user3598756 Over a year ago

this is not the answer to "Can you tell me why variable index is not changing its value?" . Rather it's a workaround not to face the argument. For the real answer see my post

David W Over a year ago

The specific reason the ByRef argument is being ignored is because the parenthesis force an evaluation that causes the argument to be passed by value regardless of the explicit declaration of the function itself.

user3598756 · Accepted Answer · 2016-10-22 12:23:19Z

2

you must remove parenthesis

Inc index

the reason is here: since "you're not interested in the return value of a function, you can call a function the same way you call a Sub procedure. Omit the parentheses, list the arguments, and do not assign the function to a variable"

answered Oct 22, 2016 at 12:23

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

1 Comment

David W Over a year ago

The reason is not specifically because of the disinterest in the return value of the function. The reason is because the parenthesis force a ByVal push of the argument to the function, regardless of the parameter's ByRef declaration.

Collectives™ on Stack Overflow

VBA - Increment variable value not changing

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related