Question

Global Variable Reset - Visual Basic

Asked by: simondopickup

  Hello,

I am trying to reset some global variables that are currently maintaining their values from the previous execution of the code. Does anyone have any simple solutions to this problem?
 

   Dim table_range As Range, rngDates As Range, rngMaterials As Range, rngCLSnew As Range
   Dim rngStdHours As Range, rngQtyGood As Range, rngCell As Range, rngDatesnew As Range
   Dim a As Long
   Dim b As Long
   Dim days As Long
   Dim i As Long, j As Long, k As Long
   Dim x As Date
   Dim wbkDipline As Workbook, wbkMaster As Workbook
   Dim wksDipline As Worksheet, wksDest As Worksheet
   Dim varHeaders, varData
   Dim varCLSHeaders
   Dim lngStartRow As Long

This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

  • Plus...
  • 30 Day FREE access, no risk, no obligation
  • Collaborate with the world's top tech experts
  • Unlimited access to our exclusive solution database
  • Never be left without tech help again

Subscribe Now

Asked On
2007-09-27 at 09:59:20ID22857220
Tags

global

,

variable

,

reset

,

basic

,

visual

Topics

Visual Basic v1.0.5.x

,

Microsoft Excel Spreadsheet Software

Participating Experts
2
Points
500
Comments
9

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

  • "The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
  • "Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
  • "Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Screenshot of Experts Exchange Knowledgebase

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Screenshot of an Article

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Screenshot of Experts Exchange Knowledgebase

Access the answers to your technology questions today.

Subscribe Now

30-day free trial. Register in 60 seconds.

What Makes Experts Exchange Unique?

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Trusted by the world's most respected brands.

image of each brand's logo

Faithfully serving IT professionals since 1996.

Experts Exchange Logo

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

Related Solutions

  1. How to select a worksheet in an Excel workbook for import …
    I presently have a macro using TransferSpreadsheet. It imports the excel worksheet ok but only the first worksheet in the workbook. If I use the import wizard under Get External Data I can select individual worksheets. I have 14 worksheets in the workbook and I would like t...
  2. Copy worksheet into the same workbook
    I am writing code in VB6 to populate a workbook with data from a loop. I want the result of each loop to go onto a new worksheet. The first worksheet is a template for all the other worksheets so instead of formatting each worksheet to the same as the template, I want to copy...
  3. Replace worksheet in multiple workbooks
    I have 500 + workbooks. Each of the workbooks has 3 worksheets. Each has the same second worksheet titled Chart. The data for Chart has changed. I have the new Chart in a workbook titled YER. I would like to find a way to replace the Chart worksheet in each of the 500+ wo...
  4. Copy multiple workbooks into a single worksheet
    I have six workbooks, each containing a single worksheet that have identical columns and formatting. The data is contained within columns A through to K. These workbooks are added to daily. What I am seeking is a macro that will copy the data from these workbooks and place th...
  5. merge values from separate worksheets in a workbook
    I have a workbook with 6 or more worksheets. The first worksheet contains in column A a list of identifying numbers (top row blank). This list contains ID numbers which are a subset of lists of ID numbers in column A of the other 5 or so worksheets. In other words, colA of w...
  6. How to combine workbook worksheets to one workbook
    Please don't laugh at my code. I have been studying the forum all day trying to combine WorkBook WorkSheets to a workbook through VBA in access. A special thanks to TextReport, jefftwilley and LSMConsulting on my studies today. Here is the history. I have a macro that expo...

Free Tech Articles

  1. WARNING: 5 Reasons why you should NEVER fix a computer for free.
    It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
  2. SCCM OSD Basic troubleshooting
    SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
  3. Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
    This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
  4. Create a Win7 Gadget
    This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
  5. Outlook continually prompting for username and password
    There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
  6. Backup Exchange 2010 Information Store using Windows Backup
    There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...

Cloud Class Webinars

  1. Avoiding Bugs in Microsoft Access
    Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
  2. Top 10 Best New Features in Visio 2010
    Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
  3. IT Consultant Business Secrets Revealed
    Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
  4. Disaster Recovery and Business Continuity
    Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
  5. Organize Your Visio Diagrams with Containers and Lists
    Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
  6. How to Us Objects, Properties, Events and Methods in Microsoft Access
    Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

Join the Community

Answers

 

by: zorvekPosted on 2007-09-27 at 10:36:18ID: 19973126

The easiest way to do this is to not make them global variables. Define them in the routine that uses them and pass them from routine to routine as needed.

Kevin

 

by: byundtPosted on 2007-09-27 at 10:42:49ID: 19973189

I prefer to limit the use of global variables because it makes the code harder to debug and maintain (you don't know where the value is being set). As a result, I pass the necessary variables to my subs and functions--like Kevin suggested.

If you stick with the global variables, then presumably you have a sub that is initializing them. Why not just run this sub each time you launch a macro from the worksheet?

Alternatively, you could run a de-initialization sub as the last step before returning to the worksheet. This sub would restore your global variables to their "blank" state:

Global a As Long
Global x As Date
Global wbkDipline As Workbook
Global wksDipline As Worksheet
Global varHeaders As Variant
Global table_range As Range

Sub Deinitialize()
'Variables lose their values
Set table_range = Nothing
a = 0
x = 0
Set wbkDipline = Nothing
Set wksDipline = Nothing
Set varHeaders = Nothing
End Sub

Sub Initializer()
'Variables gain persistent values
Set table_range = [A1:B10]
a = 3
x = Date
Set wbkDipline = ActiveWorkbook
Set wksDipline = ActiveSheet
varHeaders = table_range.Value
End Sub

 

by: zorvekPosted on 2007-09-27 at 14:28:01ID: 19974910

Here is a cool solution. Create a class that contains all of your globals as public class variables ("members") and then a single global class object to hold the class. All you need to do to reset the variables is reset the class object. This example illustrates how to use the class - run the macro MyMacro repeatedly to see that the globals are reset each time.

Option Explicit

Public Globals As clsGlobals

Public Sub MyMacro()

   ' Reset globals
   Set Globals = New clsGlobals
   
   With Globals
      MsgBox .a
      .a = 12
      MsgBox .a
   End With

End Sub

Here is the code for the class. To create the class, create a new class module, copy the code below into the module, and then name the class "clsGlobals".

Option Explicit

Public table_range As Range
Public rngDates As Range
Public rngMaterials As Range
Public rngCLSnew As Range
Public rngStdHours As Range
Public rngQtyGood As Range
Public rngCell As Range
Public rngDatesnew As Range
Public a As Long
Public b As Long
Public days As Long
Public i As Long
Public j As Long
Public k As Long
Public x As Date
Public wbkDipline As Workbook
Public wbkMaster As Workbook
Public wksDipline As Worksheet
Public wksDest As Worksheet
Public varHeaders
Public varData
Public varCLSHeaders
Public lngStartRow As Long

Kevin

 

by: simondopickupPosted on 2007-09-28 at 03:31:05ID: 19977561

zorvek,

But how do I rename my class? At the moment it is named as class 1 in the project browser but i assume i need to declare the class name in the code itself of the class.

I have never worked with class before. Could you explain why/when i would use a class rather than a subroutine (as simply as possible) and also - how i declare the class1 as "clsGlobals"?

ta

 

by: simondopickupPosted on 2007-09-28 at 07:38:11ID: 19978942

If i could offer more points then I would!! Please could someone ge back to me on this relatively simple - but frustrating problem.

zorvek you busy?

 

by: byundtPosted on 2007-09-28 at 08:37:56ID: 19979471

To rename a class:
1) While in the VBA Editor, use View...Properties to open the Properties pane
2) Select your class in the Project Explorer pane
3) Type in the new class name in the (Name) field of the Properties pane, then hit Enter

Once you have renamed your class module clsGlobals, then Kevin's code should be working for you.


As for your second question <<why/when i would use a class rather than a subroutine>>, I'll defer to Kevin. As hinted in my opening comment, I suspect that there is a different overall approach that would solve your problem better. But since you haven't described what you are trying to do, both Kevin and I are trying to address your question rather than the problem.

 

by: zorvekPosted on 2007-09-28 at 09:01:00ID: 19979677

A class is a description or template for an object. What we are doing here is using one of the features of an object which is the ability to instantiate and destroy it with a single line of code:

   ' Instantiate a class
   Set Globals = New clsGlobals

   ' Destroy a class
   Set Globals = Nothing

Think of an object as an entity in and of itself. It has members and properties. It can even have code to do things specific to that class. When a class is instantiated all of its public and private variables are initialized the same way a VBA application's public and private variables are initialized when the application is first executed. We are using a class in its most simple form - just a set of public variables. So, in essence, all we are doing by using a class instead of module-level public variables is avoiding code that resets each and every variable. Instantiating the class does that for us.

One last note on instantiation. VBA keeps track of how many times an object is referenced and does not actually destroy it until that reference counter reaches zero. So this statement:

   Set Globals = New clsGlobals

automatically destroys the previous class and instantiates a new one with one simple statement. The only difference between a simple public (global) variable defined in a module versus one defined in a class is that, to reference the class variable, you have to qualify the reference with the object name. In this case I have called it "Globals".

So the choice is between code that resets each and every public variable, or using the object qualifier every time you use a public variable.

At this point I want to reiterate my initial statement. By using globals in this manner you are actually writing inefficient code. Good structured programming principles dictate that all variables be defined as locally to the code that uses them as possible, and passed from higher level routines to lower level routines as needed. The simple fact that you are needing to reinitialize them strongly suggests that you should be putting them inside one of the routines you are invoking as local variables and passing them to subordinate routines as needed. If they really needed to be globals then they would not have to be reset. When structured this way you don't have to worry about resetting the variables as they are reset for you every time the routine is run. And all of the other discussion above becomes moot.

Kevin

 

by: simondopickupPosted on 2007-09-28 at 09:58:49ID: 19980119

I am trying to get my file to open up 'dipline.xls' and then run a series of searches to tabulate a new array and then form a stacked chart from it.

I am still having trouble resetting the variables and consequently the programme flies out of range.
Additionally, I want to perform a search on the worksheets within the workbook such that a particular chart is deleted before it is replaced by the one created in the sub 'Analyse_Dipline'. I have posted the link for the files and it includes the class advice you have already given.

 http://www.ee-stuff.com/Expert/Upload/uploadFinal.php?qid=22857220&fid=4859

It is probably a simple error -- I hope.

Thanks a million.

 

by: byundtPosted on 2007-09-28 at 10:26:27ID: 19980318

I deleted your Module2 and class module sheet (using the File...Remove module menu item). I then replaced all the code in Module1 with the following. I was then able to run the Analyse_Dipline sub several times in succession.

Sub Analyse_Dipline()
Dim table_range As Range
Dim rngDates As Range
Dim rngMaterials As Range
Dim rngCLSnew As Range
Dim rngStdHours As Range
Dim rngQtyGood As Range
Dim rngCell As Range
Dim rngDatesnew As Range
Dim A As Long
Dim b As Long
Dim days As Long
Dim i As Long
Dim j As Long
Dim k As Long
Dim x As Date
Dim wbkDipline As Workbook
Dim wbkMaster As Workbook
Dim wksDipline As Worksheet
Dim wksDest As Worksheet
Dim varHeaders
Dim varData
Dim varCLSHeaders
Dim lngStartRow As Long
Const mcstrFILEPATH As String = "C:\Documents and Settings\PickupS\Desktop\Mould Monitor\Weekly Gate Throughput\dipline.xls"
'
' Subroutine to open and copy data from the SAP downloadable file, rearrange the data to assess the contribution
' of each csy for each of the days and then plot a througput chart for each day of operation.

'Initialise_Globals
Set wbkMaster = ActiveWorkbook

' If there is an existing sheet already from a previous works - this function finds and
' deletes it - before selecting the appropriate sheet for activity (NOT WORKING!!!)
If SheetExists("Dipline Chart") Then
    Application.DisplayAlerts = False
    Sheets("Dipline Chart").Delete
    Application.DisplayAlerts = True
End If
 
   
   Sheets("Dipline").Select
   Set wksDest = ActiveSheet
   Application.ScreenUpdating = False
   wksDest.UsedRange.Clear
   wksDest.Name = "Dipline"
   Cells.EntireRow.Hidden = False
   

   ' this is header row
   lngStartRow = 4

' Open the dipline SAP downloadable WHICH MUST BE SAVED AS 'dipline.xls'
' Copy the values from the SAP downloadable into the Master Spreadsheet
      Workbooks.OpenText Filename:=mcstrFILEPATH _
        , Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), TrailingMinusNumbers:=True
'This next statement is how I opened the workbook for testing. I had a different path.
      'Workbooks.OpenText Filename:="C:\VBA\Sample '07\dipline.xls" _
        , Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), TrailingMinusNumbers:=True
      Set wbkDipline = ActiveWorkbook
      Set wksDipline = wbkDipline.Sheets(1)
      wksDipline.UsedRange.Copy wksDest.Range("A1")
      wbkDipline.Close False
   
   
  ' Actively sort and format the data from the SAP download into a form that
  ' can be worked with within excel
 
    With wksDest
      .Columns("A:D").AutoFit
      .Rows("1:1").Font.Bold = True
      .Range("B1,F1").Font.ColorIndex = 3
      .Rows("4:5").Delete Shift:=xlUp
      .Rows("3:3").Font.Bold = True
      .Range("H3").Cut .Range("G3")
      .Range("J3").Cut .Range("I3")
      .Range("L3").Cut .Range("K3")
      .Range("M3").Cut .Range("N3")
      .Columns("L:M").Delete Shift:=xlToLeft
      .Columns("J").Delete Shift:=xlToLeft
      .Columns("H").Delete Shift:=xlToLeft
      .Columns("H:J").AutoFit
      .Columns("F").AutoFit
   
   
' Decipher the size of the table to position the subsequent analysis table data
     
      With .UsedRange
         A = .Rows.Count
         b = .Columns.Count
      End With

'Sort the date column in ascending order for ease of analysis
   
      .Rows("3:3").AutoFilter
      .Range(.Cells(3, 3), .Cells(A, 3)).Sort Key1:=.Range("C3"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=True, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
      .Rows("3:3").AutoFilter
   
    Set rngDates = .Range(.Cells(lngStartRow, "C"), .Cells(A, "C"))
    Set rngMaterials = .Range(.Cells(lngStartRow, "E"), .Cells(A, "E"))
    Set rngStdHours = .Range(.Cells(lngStartRow, "H"), .Cells(A, "H"))
    Set rngQtyGood = .Range(.Cells(lngStartRow, "G"), .Cells(A, "G"))
   
'Sort out the data in the table to make it date compatible for the imminent search.
   For Each rngCell In Union(.Cells(1, 2), .Cells(1, 6), rngDates)
      If rngCell <> "" Then
        With rngCell
           varData = Split(.Value, ".")
           .Value = DateSerial(varData(2), varData(1), varData(0))
           .NumberFormat = "dd/mm/yyyy;@"
        End With
      End If
   Next rngCell
       
      ' Produce a table of cumulative values per day, such that a graph can be
      ' plotted. This section of code utilises Sumif and Vlookup functions from
      ' the excel toolbox to recover information and plot a chart.
     
      ' Firstly issue analysis table headings
      varHeaders = Array("Date", "Total Standard Hours", "Total No of Moulds", "CLS1363", _
                        "CLS1364", "CLS1574", "CLS1575", "CLS1832", "CLS1912", "CLS1913")
      .Range(.Cells(A + 2, 1), .Cells(A + 2, 10)).Value = varHeaders


   ' Loop through a SUMIF function to establish first 3 columns of the table
      i = A + 3
      For x = .Cells(1, 2).Value To .Cells(1, 6).Value
         .Cells(i, 1).NumberFormat = "dd/mm/yyyy;@"
         .Cells(i, 1).Value = x
         .Cells(i, 2).Value = Application.SumIf(rngDates, .Cells(i, 1).Value, rngStdHours)
         .Cells(i, 3).Value = Application.SumIf(rngDates, .Cells(i, 1).Value, rngQtyGood)
         ' Add QtyGood totals for each CLS
         For j = 4 To 10
            .Cells(i, j).FormulaR1C1 = _
               "=Sumproduct((" & rngDates.Address(ReferenceStyle:=xlR1C1) & "=RC1)*(" _
               & rngMaterials.Address(ReferenceStyle:=xlR1C1) & "=R" & A + 2 & "C)*" _
               & rngQtyGood.Address(ReferenceStyle:=xlR1C1) & ")"
         Next j
         i = i + 1
      Next x


' Code to make the heading element of the analysis table Bold

      With .Rows(A + 2)
         With .Font
            .Name = "Arial"
            .FontStyle = "Bold"
            .Size = 10
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ColorIndex = xlAutomatic
         End With
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
      End With
      .Columns("B:C").AutoFit
     
'Hide the original copied spreadseet
     rngDates.EntireRow.Hidden = True
    .Rows(3).EntireRow.Hidden = True
     
     
   End With


'Set Ranges for plotting the charts

    Set rngDatesnew = Range(Cells(A + 2, "A"), Cells(i - 1, "A"))
    Set rngCLSnew = Range(Cells(A + 2, "D"), Cells(i - 1, "J"))
   
    Dim date1 As String, date2 As String
   
    date1 = Cells(1, 2).Value
    date2 = Cells(1, 6).Value
   
   

 ' Plot the throughput chart for that designated period of time on a stacked bar graph
   
    Charts.Add
    ActiveChart.ChartType = xlColumnStacked
    ActiveChart.SetSourceData Source:=Sheets("Dipline").Range(rngDatesnew, rngCLSnew _
        ), PlotBy:=xlColumns
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Dipline Chart"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Dipline Throughput Between " & date1 & _
        " and " & date2 & "."
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "No of Moulds"
    End With
    ActiveChart.Legend.LegendEntries(2).Select
    Selection.Delete
    ActiveChart.Legend.Select
    ActiveChart.Legend.LegendEntries(1).Select
    Selection.Delete
    ActiveChart.Legend.Select
    Selection.Left = 582
    Selection.Width = 145
    Selection.Width = 71
    Selection.Left = 656
    ActiveChart.ChartArea.Select
    ActiveChart.Legend.Select
    ActiveChart.PlotArea.Select
    ActiveChart.ChartArea.Select
    ActiveChart.Legend.Select
    Selection.Left = 655
    Selection.Top = 2
    ActiveChart.ChartArea.Select
    ActiveChart.Deselect
    ActiveChart.PlotArea.Select
    Selection.Top = 32
    Selection.Height = 387
    ActiveChart.ChartArea.Select
    ActiveChart.Axes(xlCategory).Select
    With Selection.TickLabels
        .Alignment = xlCenter
        .Offset = 100
        .ReadingOrder = xlContext
        .Orientation = xlUpward
    End With
       

' Update the screen View
   Application.ScreenUpdating = True
   Sheets("Dipline").Select
   
End Sub
Private Function SheetExists(ByVal sname As String) As Boolean
'Returns TRUE if sheet exists in the active workbook
    Dim try As Variant
    On Error Resume Next
    Set try = ActiveWorkbook.Sheets(sname)
    On Error GoTo 0
    SheetExists = Not try Is Nothing
End Function



Brad

20120131-EE-VQP-002

3 Ways to Join

30-Day Free Trial

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Develoment.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Mircosoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...and excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans

Loading Advertisement...