Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Manual Or Automatic Calculation Option Using VBA

Is there a way of using VBA to check which calculation option is on - "automatic" or "manual" ?

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Manual Calculation Warning?
Is there a way to make excel 2007 pop up a warning whenever calculation is set to manual by a macro or any other means? I have on several occasions noticed formulas not working, only to discover that calculation was set to manual without me noticing. And then I don't know how much of my work may have been afffected. This seems like a pretty vital piece of information, and I am surprised that it's not made more obvious.

View Replies!   View Related
Open In Calculation Manual
how does excel determine if it opens a file in manual or automatic?
how can i choose that excel opens every file in calculation manual?

View Replies!   View Related
Turn Calculation To Manual
I'm trying to turn calculation to manual, but there does not seem to be an Options button under tools on the mac I'm using. I've checked another mac and it is also missing. I'll probably end up using a pc for the calculations anyway, but I was wondering if anyone knew what was up?

View Replies!   View Related
Preferences Keep Defaulting To Manual Calculation
I am on a Mac running OS 10.4.11. Whenever I launch Excel 2008, I have to go to Preferences and set Calculation to automatic.

Then Excel calculates automatically until the next time I launch the program. Then I find it has defaulted back to manual.

View Replies!   View Related
Temporarily Change Calculation To Manual
I have a set of procedures that require auto recalc to be on to work correctly. I've tried application.volatile and Application.CalculateFull with no luck. I'm trying to put together code that makes sure recalc is on, but that first determines the current recalc status and then changes it back to Manual when necessary. I don't know how to determine the current status. The following code should work if the red sections are fixed.

Sub TempAuto()
Dim CurrentState As unknown
CurrentState = Application.Calculation status
Application.Calculation = xlAutomatic
If CurrentState = Manual Then
Application.Calculation = xlManual
End If
End Sub

View Replies!   View Related
Setting A Single Worksheet To Manual Calculation
Is there a way that I can set a single worksheet in a workbook to always be on manual calculation, but keep all other sheet in the workbook set to automatic?

I want to be able to open the workbook, any calculations to perform automatically and then i will select the 'manual' sheet and perform these calculations manually. I also need to these settings to always apply each time i open the workbook.

View Replies!   View Related
Visual Display When Manual Calculation Required
In as much as I would like to heed the advice of this site to avoid setting excel calculation to MANUAL, I think I have no choice this time. I have a file that uses a lot of SUMPRODUCT(--) and array formulas. DSUM would have been faster but this file I am working on will be sent to users who barely knows excel. When they need to insert rows, the DSUM criteria will have to be reestablished and I do not think they are capable of that. Anyway...when calculcation is set to manual, all excel does to warn users is the little test "Calculate" in the status bar. What I would have liked is that a red button with text "CLICK TO REFRESH" to appear when calculcation is needed i.e. status bars is showing "Calculate".

View Replies!   View Related
Change Manual Execution To Automatic Execution
I have this codes which will only trigger if I manually execute it. What do I need to do to trigger it automatically whenever the worksheet change.

Below is the codes:

Sub Risk_Color()

Dim c As Range, myFontCol As Integer, myCol As Integer

For Each c In ActiveSheet.Range("f7:g20000")
myFontCol = xlAutomatic
myCol = xlNone
Select Case c.Value
Case Is = 1, 2, 3
myCol = 34
Case Is = 4, 5, 10, 20: myCol = 43
Case Is = 30, 40, 50: myCol = 6
Case Is = 70, 100, 140, 150
myCol = 5
myFontCol = 2

View Replies!   View Related
Input Automatic Calculation
In attached file, I need to find a way for "y" to be automatically calculated. But for that, "y" needs to first find out which equation to use, depending upon the corresponding entry in first column.

would somebody know how to do it ?

I was trying vlookup, but couldn't.

View Replies!   View Related
Ensure Automatic Calculation
I have largish workbooks (10MB) with a variety of formulas and lookups to generate tables and charts. Calculation is ALWAYS set to Automatic, but sometimes (not always) the formulas fail to update when values are changed. Sometimes F9 will force calculation, sometimes Ctrl + Alt + F9, sometimes (especially with charts) I have to close the workbook and reopen before they will update. The workbooks contain macros but none are running when this happens. A search of your forum indicated that this question has arisen several times before, but I haven't seen a definitive answer.

View Replies!   View Related
Wont Recalculate With Automatic Calculation
I am working in a fairly large worksheet, and I realized that when I make changes to it, sometimes some of the cells do no recalculate. The only way I can get them to recalculate is by clicking into the cell, and then hitting return.

The issue is that I don't know which ones are failing to recalculate unless I click into each individual cell and hit return to see if it changes. This would be logistically impossible given the size of my sheet.

Has this ever happened to anyone else? Does anyone have any suggestions as to how to fix this issue?

View Replies!   View Related
Failure Of Automatic Calculation When Worksheet Moved To Another File
I have workbooks in which summary tables are generated by lookup formulas. Tables are on different sheets but all use the same lookup value by referring to a cell on the 'master' sheet. The lookup value appears in cells on all sheets, by reference to the master sheet (e.g. [formula] = mastersheet!$B$2).

The master sheet contains the main summary table and is copied and detached for distribution, using a macro. For practical reasons, this is done in two stages, first copying the sheet within the workbook (to make minor alterations), then moving it to a new book to save and distribute.

This is where the problem arises. After the sheet has been detached, we find that if we now change the lookup value on the master sheet in the original file, the tables on that sheet will update normally, but the cells on other sheets remain frozen at the previous value and the tables on those sheets do not update.

The only way round the problem is by Shift + Ctrl + Alt + F9. (Maybe I should add that all my workbooks are always set to automatic calculation.)

The macro itself is not the cause of the problem; if we follow the same procedure manually, the result is the same. However, if we move the sheet in one step, eliminating the intermediate copying stage, the problem does not arise. But this is evading the problem, not solving it, and I would be reluctant to have to resort to this.

The original problem remains as stated, viz. failure of automatic calculation.

View Replies!   View Related
Formula Not Updating: Using Tools, Options, Calculation, Automatic
I have a table with rows that keep growing. But I have place formula in the whole of column F, i.e. F2:F66565. When I import information from MS Access into column A to E, the formula in F does not work, until I copy from F2 to the end manually. I have tried using Tools, Options, Calculation, Automatic. That doesn't work, I have also tried F9, that doesn't work and I have also tried checking Precision as Displayed under the calculation tax in Tools-Options, that doesn't work either.

View Replies!   View Related
Option Button (active Control) Code To Perform A Calculation
is it possible to choose an option button which will then perform a calculation on a cell reference? Iím trying to deduct a percentage from a total when an option button is highlighted.

View Replies!   View Related
Spell Check - VBA Vs. Manual
I have initiated a spell-check in VBA using:

View Replies!   View Related
VBA Find Does Not Work But Manual FindAll Works
I'm having trouble using VBA's Find function.

I have a worksheet which holds a concatenation of AppID's and App Names in Column U. There are approximately 12,000 rows and each cell in Column U holds one of either of the following value formats:

242 - Application 1
242 - Application 1; 1845 - Application 2
242 - Application 1, 1845 - Application 2; 34678 - Application 3

I need to find all instances of a chosen App ID and then copy any row in which the App ID appears to a new sheet (to obtain the chosen AppID I am presenting a list of those to the user in a form Listbox, and I know the selection ofthe AppID is functioning as I am currently presenting it in a MsgBox prior to running this part of the code).

When I run a manual FindAll on a given AppID it returns all the cells in Column U which that AppID appears, but when I use the following code to achieve the same it does not seem to find the AppID's.

(NB - I've "borrowed" this code from a posting on Ozgrid, but I have also compared it to the many other FindAll methods available on the web and they all apppear to be pretty similar).

(The changing of the cells interior colour is just a way of identifying whether it's working prior to writing the code to copy the row ino a new sheet).

Dim temp2WS as Worksheet
Set temp2WS = ThisWorkBook.Worksheets("AppID")
Dim lCount As Long
Dim rFoundCell As Range
Set rFoundCell = temp2WS.Range("U1")

View Replies!   View Related
VBA Code Quicker Than Just Writing Out The Calculation In VBA
Does Excel handle formulas written into the VBA code quicker than just writing out the calculation in VBA?

I have a section where I use the following formulas, sumif, countif and a combo if iserror sumproduct in the VBA code...runs rather slow at this point and was looking at a way to speed things up.

View Replies!   View Related
VBA Images And Option Buttons
I have a spreadsheet with a set of 5 option buttons within a group box. What i want to be able to do is select an option and depending on the option an image above changes to reflect the option selected.


Option 1 selected shows diagram 1

Option 2 selected shows diagram 2


View Replies!   View Related
Automatic Calendar Using VBA
provide an input (1,12, or 123 would be the only input selections), then the vba needs to hide all rows that are not equal to the input

I have developed a type of calendar in excel with the following setup. There are five key headers and each header has a code it pertains to, either 1,12, or 123. The spreadsheet is set-up like so....

Key Project

12 A
1 B
123 C
12 D
12 E
123 F
1 G

So basically vba code that will produce a pop up and ask for a key then after the key is entered it will display rows that have an equal key in column A and hide all other rows.

Fromt he above ex. if "12" was entered the spreadsheet would show:

12 A
12 D
12 E

View Replies!   View Related
Automatic Numbering In VBA Etc
I'm trying to create a bug reporting tool (with a bunch of text boxes and drop down lists) and have the following problems...

1. I would like to get a unique number inserted automatically in a textbox (it's supposed to be the bugs id (1001). How do I do this? And when I click OK after inserting all info I want this number to become +1 so the next defect can be added immediately.

2. Why are my drop down lists empty as default and their values only appear if I enter a value. Why aren't the lists displayed when i just click on them?

3. I have a multipel row text box. How do I get the text to jump to the next row automatically instead of using crtl + enter?

View Replies!   View Related
Using VBA To Change A Picture When An On Option Is Selected
I have a group box with 5 little round option buttons in, what i wanna do is connect the options to an image and when an option is selected the image that corresponds to it shows, then when a different option is picked another image relating to that option shows.

Select option 1, image 1 is shown

select option 2, image 2 is shown

View Replies!   View Related
Using VBA To Select An Option In A System Msg Box
I have a button in a template file (.xlt) that will save the file as the customer name in cell C4. That template file feeds an Access database and when you save the template file you are prompted with the following system message:

If record DOES NOT exist in DB:

If record DOES exist in DB:

My code is as follows: ....

View Replies!   View Related
Multiple Input Box And Option Button Using VBA
I would like a VBA code that will display a large input box with multiple inputs boxes inside.

I have shown below the variables required (column C) and the default values (Column E). There is a combination of dates, percentage and numeric values....

View Replies!   View Related
Disable Save Query Definition Option Vba
I want to be able to disable the save query definition option for a worksheet using VBA. Right now I have to right click a cell and then go to Data Range Properties and then uncheck the save query definition box. If there is a way to automate this that would be great.

View Replies!   View Related
VBA Code For Automatic Deletion Of Rows
My data is in the following format,

Company Name Bill Status

I need a vba code if the in Bill status column rejected is there all those rows to be deleted.

View Replies!   View Related
LINEST Function Calculation Using Only VBA
I can do least squares fitting utilizing LINEST and a worksheet. (See attachment.)

I would like to do the same calculations entirely within VBA.

I can specify the values as arrays within VBA :

Sub linest2VBA()
' linest2VBA Macro
' Keyboard Shortcut: Ctrl+Shift+A
Dim dXvalSqd(4), dXval(4), dYval(4), dFirstRegStat As Double
Dim i As Integer
Second Question:

Is there a way to get the remaining regression statistics within VBA by referencing only the three arrays: dXvalSqd, dXval, and dYval?

View Replies!   View Related
VBA Average Calculation Of The Values In Cells
In my Case Else, I have it set to take the Average of the values in cells M8 for all sheets other than the ones listed in my other Cases, same for M9, etc. for each worksheet I have.

The change I want to make is as follows: I'd like it say say if the value in Cell M8 is the same on all sheets then place x value in cell M8 on the active sheet, same for M9, on so on and so forth. Only if the values in Cell M8 for each sheet are not the same then take the average.

Private Sub CommandButton1_Click()

Dim sh As Worksheet, sum As Double, n As Long
n = 0
sum = 0
Dim arr() As Variant
Dim nbrOfAvgs As Long, i As Long
nbrOfAvgs = 19
i = 0 ....................

View Replies!   View Related
Multiple VBA Calculation Events To Just One Worksheet
I use VBA's WorkSheet_Calculate event to detect when a cell of particular interest calculates to a specified value. It works well as long as there is only one worksheet (tab). However, when I duplicate that tab (and the final app could have up to 10 copies) to run a different set of data concurrently, I get bad results. The data is online, real time trading data, with each selected stock being tracked in a different tab.

Apparently both (or all) tabs in the workbook react to the same event, whereas I would assume that the event routine in each tab would react only to a calculation in that tab. If this is in fact the case, is there a way to a) make the event routine in each tab respond only to a calculation in that tab, or b) upon a calc event anywhere, determine which tab it occurred in?

Here is the present event code; right now it reads exactly the same in every tab. As long as nothing is happening in any other tab, it does its job correctly.

View Replies!   View Related
Run VBA Macros In Specific Order, Turn Off Calculations & Force Calculation Of Range
I have a VBA method that goes thru and calculates a bunch of stuff and puts this calculated info into a static data structure. This method is called 'setProjectInfo()'. This method is reliant on values on my worksheets, So as the user makes changes to the worksheet, I want setProjectInfo() to get called so it refreshes the data structure with updated information. I then have a bunch of helper methods to access different parts of this data structure. These helper methods are used in the formulae of a bunch of cells on a worksheet (ie '=getPercentComplete(period)', etc, etc).

This is all working well except for one thing. Sometimes the helper methods get called BEFORE setProjectInfo() gets called.. So, the helper methods return stale information. I can only get around this by manually recalculating the spreadsheet again. setProjectInfo() does enough stuff and takes a long enough time that I don't want to have to call it at the top of each helper method.

I've put the call to setProjectInfo() in a cell formula at the top of the my spreadsheet in hopes that Excel would know enough to call it first.. But that does not seem to work. Net, how can I get VBA to always call setProjectInfo() BEFORE it calls any other VBA methods?

View Replies!   View Related
Manual Rank Update
I have what I'd thought would be a simple problem, but I haven't yet been able to track down an answer. I am trying to manually rank a list (column) of players' names. What I'd like to be accomplish is something to the effect of being able to enter a value for a single player, then have the cells resort themselves AND update the list.


1 Jon
2 Jim
3 Joe
4 Jack
5 Jane

I'd like to be able to manually change A5, for example, to 2 and then have the list update itself to read:

1 Jon
2 Jack
3 Jim
4 Joe
5 Jane

I've toyed with macros that took care of the sorting piece but I'm still left with duplicate numbers (ranks) that I must then manually change and/or fill down. There are over 500 entries (rows) so this can become a bit tiresome.

View Replies!   View Related
Manual/Information On ADO
where is possible to get more detailed manual - reference - for using ADO in excel? Help in Excel doesnt content much information of ADO, I have tried to find other manual, but no results.

View Replies!   View Related
Manual Delete Causing Troubles
I have an action that deletes a row, and decrements rows counter by one. But, if the user manually deletes one row, I don't get that information (decrementation). Workbook pretection is out of the option.

What are my choices?

I was thinking, is it possible to capture a delete event in general, and add some code to it? Or maybe even completely disable it?

View Replies!   View Related
Open Workbook With Calc As Manual
Is there any way of opening a workbook via VBA that will set the calculation to xlCalculationManual BEFORE the workbook calculates anything on opening.

The purpose: I'm opening the workbook remotely from an Access document and there are quite a few formulas in the workbook that take a few seconds to calculate. I have a modeless UserForm that displays from the Workbook_Open event which I want to get displayed before all the calculations take place so the user has something pretty to look at while he/she is waiting. But it seems that the workbook performs a calculation before running the Open event code.

View Replies!   View Related
Protect From Changes & Manual Input
I have a macro that transfer data from one sheet to another (I have sheets called Form and Sent). Basically, the users enters data in the Form Sheet. After they're done, the data gets transferred to the Sent Sheet. I don't want users to be able to modify the data in the Sent sheet. I just want them to see the records. Also, i want the sheet protected from having users manually inputting datas. I can't accomplish this when I protect the sheet, since it is giving me an error while running the macro to transfer data. Is there a workaround?

View Replies!   View Related
Formulas: Calculate Without Manual Calclation
I am thinking that I must have something set up wrong some place, but have not done anything that should have changed my formulas

Its not that they are gone but just are not auto calculating.
I tried just a basic sample invoice and the figures do not calculate without manual calclation

View Replies!   View Related
Pivot Table Manual Manipulation
My pivot table is not auto summing each column properly, and I can't manually enter a formula to do so. I tried adjusting the settings of the rows and columns through the pivot table wizard, but no luck. Is there a way around this limitation?

View Replies!   View Related
Disable Manual Entry In Combobox
I would like to use a combobox and I want to force the user to selected one item from the list, and not to be able to type anything in it.

My other solution would be to use a listbox but I like the combobox design better...

I found this code in a MSDN Forum but somehow, it does not seem to work.
Maybe it is because I do not know what to do with a "public class", or my Excel 97 does not support this.
I tried pasting the "private sub" in a sheet code (where my combobox is), but it did not work.

Public Class Form1
Private Sub ComboBox1_KeyPress(ByVal sender As System.Object, _

ByVal e As System.Windows.Forms.KeyPressEventArgs) _

Handles ComboBox1.KeyPress

e.Handled = True

End Sub

Is this code actually working under excel 97 and if so, where do I have to put it?

View Replies!   View Related
Hyperlinks Only Work After Manual Edit
I have received from reading previous posts in this forum. Now I have a problem that I have struggled with for weeks. Ref: Excel 2000. I create (Purchase Order) Workbooks with 50 (POs) Worksheets, and 1 summary (Index) worksheet. The Index worksheet has hyperlinks to each PO (created via VBA), and works as expected.
Subsequently, each worksheet has a hyperlink back to the Index worksheet also created from VBA

Sheets(Counter + 2). Cells(2, 2).Select
ActiveSheet.Hyperlinks.Add Anchor:=Sheets(Counter + 2).Cells(2, 2), _
Address:="", SubAddress:=Sheets(PONumber & " THRU " & PONumber + 49) _
.Cells(Counter + 3, 1).Address

Before the hyperlink in each worksheet will work I must (with the cell containing the hyperlink selected) either right-click|Hyperlink|Edit Hyperlink then press "Enter", or pres Ctrl-K (which brings up the edit dialog) and then press "Enter." If I don't manually edit each hyperlink on each worksheet these hyperlinks select the targeted cell on the same worksheet as the selected cell, not the targeted cell on the Index worksheet. Note, when I manually edit each hyperlink I am not actually making any changes to the hyperlink (thought something is obviously happening that I am unaware of), I am only accessing the edit dialog box.

View Replies!   View Related
Manual Time Clock For My Employees To Punch In And Out
I currently use a manual time clock for my employees to punch in and out. I then use Excel to tally their times. This is very tedious and error prone.

Is it possible to use Excel as the timeclock itself? I know that the employee can enter the time in a spreadsheet manually. But this also opens the door for error and potential dishonesty.

What I am looking for is, if the employee can punch in and out by doing a keystroke and Excel uses the computer's clock to timestamp.

View Replies!   View Related
Macros Effected By Manual Calculations On Run?
I always use manual calculation with Excel, does it affect the calculation within a Macro when I run it?

View Replies!   View Related
Preventing Manual Data Entry Errors
how to protect my spreadsheet from data entry errors.

I have a column of data that must be entered manually (column B). Sometimes, the entry in column B could have been calculated by running a formula run upon the data in column A. Sometimes it has to be entered manually.

Is there a way to use data validation or conditional formatting to make sure that the proper figure is entered into column B when it could have been calculated by a formula to avoid data errors?

View Replies!   View Related
Using Manual Filename Variable To Import Data
referencing a variable during a data import. I have searched the forums here and the web for a couple weeks and attempted enclosing the variable in many different symbols.

Nothing I have tried works. I have also read chip pearsons guide but I was still not able to get it to work.

I am trying to provide a way for the user to input the filename and location in an input box and then use that variable to import the data. It is only one file that is needed.

The message box filename is only included to allow me to verify the text input.

Dim Filename As String

Sheets.Add. Name = "All Data"
Filename = InputBox("Enter Filename: ", "Enter Filename Location")

MsgBox Filename
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;Filename", Destination:=Range("A1"))
.Name = "SHOAlarmsJune2-9"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False

View Replies!   View Related
Coding The Difference Between A Manual Carriage Return And VbCR
I'm trying to use the 'speaking cells' utility. Unfortunately, it seems to require a manual carriage return - I have tried offsetting and I have tried this (below), but to no avail:

Sub speak()
Range("A1") = "YES"
ActiveCell = vbCr
End Sub

View Replies!   View Related
Automated Entry In Cell Instead Of Manual (without Causing Circular Reference)
I need to make cell A1 = cell D1.

Cell A1 is calculated by entering a number in cell E1[COLOR=blue ! important][COLOR=blue ! important][/COLOR][/COLOR]. Due to the various formulas used, when cell E1 goes up, the value in cell A1 goes down (and vice-versa - When E1 goes down, value in A1 goes up). Cell D1 is calculated using formulas UNASSOCIATED with A1 or E1.

I can't enter a formula for cell E1 to do the calculating due to the circular reference created.

I need some type of code that will automatically figure out what number needs to be in cell E1 to make cell A1 equal Cell D1 without creating the circular reference.

View Replies!   View Related
Auto Fill Doesnít Work, But Trying To Avoid Manual Entry Of Rows.

Above is the formula I am working with. I am inserting it into row 4 thru row 996 in a number of different columns. The auto fill function works great for this part of the formulaÖ.


However, this part Z29:Z39 I have to enter manually row by row until I can figure out a better way. Do you know an easier way?

To put this formulaÖ
Into any column row 4 thru row 996, without having to change Z29:Z39 for every row, since I cannot rely on autofill?

View Replies!   View Related
Make A Calculation(addition) And Use The Answer To Multiply Against Another Addition Calculation
make a calculation(addition) and use the answer to multiply against another addition calculation....

The sum of (Monday!A1:A4) multiplied by the sum of (Monday!B1:B4) plus (Tuesday!A1:A4) multiplied by the sum of (Tuesday!B1:B4) and so on.

View Replies!   View Related
That Takes From Manual Sheet The Number In Specific Column And Multiply It By The Percentage In Sheet
I'm trying to create a formula that takes from "Manual" Sheet the number from colume G2 and multiply it by the percentage in sheet "AllocationRule".

My formula currently is =Manual!$G$2*AllocationRule!$B5.

What needs to happen is that the total number in "Manual" needs to be distributed evenly in 4 rows by the percentages allocated in "AllocationRule".

Right now I can't copy my formula over to the sheet because the "AllocationRule" should stop at B5 and not go further and the G2 from "Manual" should not change for the percentage allocation but should change to the next row for the next month.

And then after I've done the calculation I want the LOB in "AllocationRule" to be displayed in the LOB in "H1913_H1914" but I'm not sure what formula to use.

View Replies!   View Related
Calculations To Manual Before My Code Runs And Set It Back To Auto When My Code Is Done Running
I am dealing with several very large spreadsheets using VBA to do various things. I found that my code worked well, but was taking a long time to run. The biggest time consumer was my use of the AutoFilter features. I have since turned calculations to manual before my code runs and set it back to auto when my code is done running. What are the potential consequences of my turning calculations to manual and then back to auto?

View Replies!   View Related
Print The Worksheet On Different Paper Which Requires To Go Into The Properties And Change The Paper Source From Automatically Select To Manual Feed
When I hit the print button the worksheet prints on the paper in the bin. However, there are times when I need to print the worksheet on different paper which requires me to go into the properties and change the paper source from Automatically Select to Manual Feed. I have been trying to created a macro what will switch to Manual Feed, print the worksheet and then switch back to Automatically Select but have been unsucessfull.

View Replies!   View Related
ParamArray Option
My purpose is:

1) I have a macro workbook with multiple procedures (6 main tasks)

2) Some workbooks will use all procedures, and some will not use all procedures (most use either 4 or 6 of the procedures)

3) To hide the unwanted procedures as required, the menus will not appear for those choices (in code that creates custom toolbars on activate)

Solution (proposed):

1) send a paramarray argument with string elements that identify menus to use

2) send a ďshow allĒ true or false, to simplify identification of workbooks that use all procedures

Reason solution is chosen:

1) I can basically have one macro workbook this way Ė so simple implementation even where some workbooks wonít need all the macros (and should definitely not be making them menu options).

2) If I add more routines, I can just extend the ParamArray as needed and continue to identify specific toolbar options to use or not use.


1) That paramarray is interesting! It canít be passed and its initialized as an array with bounds 0 to -1. Also, it canít be tested directly to see if its Empty. So I came up with checking for the -1 UBound, combined with loading the elements into another array (seems stilted to me).

Iím not sure what Iím asking, butÖI need to determine if the paramarray is being used, and be able to pass it. Is there a simple way to do that? As Iím sitting here writing Iím realizing I could just pass a regular array and dispense with the ParamArray option -- well, Iíll post my thoughts anyway. Sorry this is so ramblingÖ

Hereís the code Iím using (basically). Note that DoSomething actually checks each element of the array to see if it matches a potential menu item (by name), and if so, marks that menu item to be added - since there's 6 menu options, I placed it in a separate method to avoid have it in the routine 6 times.

Maybe I'm just curious if anyone ever successfully uses the ParamArray option - it seems to me to be somewhat of a bother that you can't easily tell if it's been passed in or not, or use it like a regular array (check if its empty, etc.).

Sub MyToolbar(ByVal blnShowAll, ParamArray args() As Variant)
Dim a() As Variant
Dim x As Variant

View Replies!   View Related
Copyright © 2005-08, All rights reserved