DaniWeb Forum Index > Software Development > Visual Basic 4 / 5 / 6

Visual Basic 4 / 5 / 6 RSS

Basic Variables Question

Thread Solved

•

Join Date: Jul 2006

Posts: 2

Reputation: DarkAlex is an unknown quantity at this point

Solved Threads: 0

DarkAlex

Offline

Newbie Poster

Basic Variables Question

Jul 14th, 2006

Hello. This is a very very basic question about variables in range names.
I have quite a few ranges of varying lengths in multiple Excel documents. I want all the ranges standardized and set to the same length, but I don't know what that length will be yet, and it may change, so I'd like to make a variable for it.

This is currently the best I've come up with:

 Help with Code Tags 
 Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
Sub FixEverything()
Dim sh As Worksheet
For Each sh In Worksheets
    sh.Visible = True
Next
 
Dim OutLim As Integer
OutLim = 200
ActiveSheet.Names.Add Name:="RangeNameHere", RefersTo:="=$A$3:$B$OutLim" ' One of these lines per named Array 
 
    Worksheets(2).Visible = Hide
    Worksheets(4).Visible = Hide
    Worksheets(6).Visible = Hide
    Worksheets(8).Visible = Hide
End Sub
Sub FixEverything()
Dim sh As Worksheet
For Each sh In Worksheets
    sh.Visible = True
Next
 
Dim OutLim As Integer
OutLim = 200
ActiveSheet.Names.Add Name:="RangeNameHere", RefersTo:="=$A$3:$B$OutLim" ' One of these lines per named Array 

    Worksheets(2).Visible = Hide
    Worksheets(4).Visible = Hide
    Worksheets(6).Visible = Hide
    Worksheets(8).Visible = Hide
End Sub

•

Join Date: Jun 2005

Posts: 85

Reputation: williamrojas78 is an unknown quantity at this point

Solved Threads: 4

williamrojas78 williamrojas78 is offline

Offline

Junior Poster in Training

Re: Basic Variables Question

Jul 14th, 2006

Hi

Are you working with multiple excel documents or with multiple worksheets in the same document. I guess it does not really matter.

i noticed you have an error in your formula, if you want to set a range the worksheets of your workbook do something like this:

 Help with Code Tags 
 Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
 
For i = 1 To 3
OutLim = 200
 
    Worksheets(i).Activate
 
    ActiveSheet.Names.Add Name:="RangeNameHere", RefersTo:="=$A$3:$B$" & OutLim 
 
Next
For i = 1 To 3
OutLim = 200

    Worksheets(i).Activate

    ActiveSheet.Names.Add Name:="RangeNameHere", RefersTo:="=$A$3:$B$" & OutLim 

Next

that will set the range A3:B200 in the first three sheets.

If you want to do it in all the sheets, do as you did at the beginning: for each sh in workbooks

regards

•

Join Date: Jul 2006

Posts: 2

Reputation: DarkAlex is an unknown quantity at this point

Solved Threads: 0

DarkAlex

Offline

Newbie Poster

Re: Basic Variables Question

Jul 21st, 2006

Ah, so that's how you do it. Thanks, I didn't realize I could use the & operator in that way. As it turns out, someone decided we were going to do the program in a completely different way anyway, so my question was almost immediately invalidated.