Global Variable in Visual Basic

cunnins4

Hi folks,

I'm trying to automate an excel workbook to create a new tab using a macro and to rename the tab according to the number of tabs created. To do this I need to create a counter of some type and increment it everytime the macro runs. This is where I'm having trouble-I can declare a variable inside the sub and I can increment it once, but that's it. I need to declare it outside the function and be able to increment it everytime.

This is the basics of what I have:

Sub NewTab()
'
' NewTab Macro

Dim num As Double
num = 1

Sheets(num).Copy After:=Sheets(num)
Sheets(num + 1).Name = num + 1

End Sub

Once I get the variable passing sorted in my head I should be able to do everything else I need to with this.

I don't know VB at all, but I've studied quite a bit of C++.

Any advice is most welcome!

cunnins4

as always, when you ask the question the answer comes!

num=sheets.count


beautiful!

mods delete if ye want!:pac:

collsoft

cunnins4 reply is the way I would do it - let Excel worry about the sheet numbers, but if you want to use a global variable like you asked all you have to do is move the decleration of num out of the function to the module level like such;

Option Explicit
Dim num As Double

Sub NewTab()
'
' NewTab Macro

'Assume num = zero initially
num = num + 1
Sheets(num).Copy After:=Sheets(num)
Sheets(num + 1).Name = num

End Sub

This decleration of num using "Dim num as double" makes it semi-global in that it is only global within the code module in which it is declared - i.e. you cant reference it from another module in that spreadsheet.

if you want to make it global accross all modules then you would declare it as "Public num as double".

One other very important note is the "Option Explicit" directive at the top of the module. This forces VB to require all varibles to be declared before they are used. If you did not have this directive then VB creates all variables that are not explicitly declared automatically so bugs just creep in. You will need to put this at the top of all code modules.

You can set VB to always insert this "Option Explicit" by selecting "Tools" > "Options" then tick the "require variable Decleration" option"