• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 457
  • Last Modified:

Vb help

Please can you help with an error message I get in my spreadsheet, the weird thing is that I have the same code in another and do not encounter this error.

When I click the command button "j = " comes back in error (Compile error variable not defined)

My best guess is that the code works in up to date versions of excel, however it does not seem to work in excel 2003.

Please can you help.
help-12-09-2012.xls
0
Kiwi-123
Asked:
Kiwi-123
  • 4
  • 2
1 Solution
 
Martin LissRetired ProgrammerCommented:
Is j defined in the sheet where you are having the problem? If not and you want both sheets to share the same j you should move the definition to a module and define it as Public, like

Public j As Integer ' or whatever type it currently is
0
 
Kiwi-123Author Commented:
Hi Martin, many thanks for your advice, I don't think j is defined anyplace else. Could this be done at the top of the VB pane, something like 'Option Explicit'?

If not would you be able to show me an example of the module?

Thanks,

Gaz
0
 
Martin LissRetired ProgrammerCommented:
What I'k talking about is what is called the scope of a variable. Briefly...

If a variable is defined in a Sub or Function it's scope is only that Sub or Function which means that it's only available in that Sub or Function.

If a variable is defined at the top of a Sheet with Dim or Private it's scope is only that sheet.

If a variable is defined as Public in a module it's scope is the whole workbook.


I'm assuming that you have it defined in one sheet and you're trying to use it in another which is why you are getting the error, so as I said, move the definition to a module.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
NorieCommented:
Just add Dim j As Long at the top of the sub.
Private Sub CommandButton3_Click()
Dim i As Long
Dim j As Long
Dim row As Range

Open in new window

0
 
Martin LissRetired ProgrammerCommented:
Just add Dim j As Long at the top of the sub.
Don't do that if you want to use the same j as in your other sheet.
0
 
NorieCommented:
Martin

There is no other code.:)

Also, it's probably not a good idea to have a variable like this as public.
0
 
Martin LissRetired ProgrammerCommented:
I didn't notice his attachment and of course I know that it's not a good idea.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now