From: Frank Isaacs isaacsf@ix.netcom.com
=x^y
Or
=POWER(x,y)
Return to Table of Contents
Q. Wanted to write a calculation that finds a
value in a table, figures out what cell it came from and then
offsets the referenced cell by one row down.
A. Sounds like you could use the INDEX and MATCH
functions. (From John Green, Sydney, Australia)
=MATCH("Apples",A10:A100,0)
would return the row in A10:A100 which contains the
reference to "Apples".(It could also find the position
of numeric values)
=INDEX(A10:A100,MATCH(A1,A10:A100,0)+1)
would return the contents of the cell one row down from the
cell which matches the contents of A1.
This can be extended to find the location of the minimum value in a list (Bob Reimer reimerb@istar.ca ) :
=INDEX($A$1:$D$1,1,MATCH(MIN(A2:D2),A2:D2,0))
Return to Table of Contents
(Stephen Bullen
http://ourworld.compuserve.com/homepages/Stephen_Bullen)
There is obviously a trade-off between managing the hassle of
deleting existing menus and creating your own. I find in my applications
that there are relatively few menu items that I need to duplicate
- some of the items on the File and Edit menus. These items can
easily be reproduced with one or two-line procedures, utilizing
the built-in Excel dialogs:
Sub Foo()
Application.Dialogs(xlDialog.....).Show
End Sub
The main disadvantage to this method is that you lose
Excel's menu context-sensitivity (i.e. items go grey when not applicable/available).
Individual menu items can be deleted off of the existing menus by
using the menu editor (available from a module
sheet), or using the code:
To delete a whole menu:
MenuBars(xlWorksheet).Menus("Format").Delete
Or just one item:
MenuBars(xlWorksheet).Menus("Edit").MenuItems("Delete
Sheet").Delete
Note that if you use this method, you will need to delete the
items from each menu bar that you use (worksheet, chart, module
etc), and that other spreadsheets or addins could have placed
extra items on these menus. If your application will be a case of
open Excel, open your application, use your application, close
Excel, then you should
be OK. If you want to use your application along with other
spreadsheets (which may need the full menu bars), or if you need
to leave Excel open when your application has finished, you will
probably have problems.
Return to Table of Contents
Q. Want to calculate and
display total elapsed hours between two dates
A. (From: Morten Jensen mjensen@sn.no ) You have to use the right format.
To get over 24 hours, just use the format [tt]:mm.
Return to Table of Contents
Q. For a large database in an
Excel worksheet: Trying to do with this is add the contents of a
field where a number of other fields are subject to constraints.
The Excel DSUM function is ideal for this. My problem, however,
is that I want to perform a very large number of such DSUM's.
Consequently, I have ended up with a whole worksheet full of
constraint tables, one for each DSUM. Isn't there a way of using
DSUM within a macro so that I don't actually have to specify the
constraints within a worksheet. In other words, considering the DSUM
syntax: DSUM(database, field, criteria), is there a way for me to
express <criteria> as a string in a macro instead of as a
range of cells in a worksheet ?
A. (From: Stuart Zussman szussman@pacbell.net )
You can use expressions like
={sum((bank="CITI")*(currency="USD")*(month<10)*(amount))}
which will sum the amount column for all rows where bank is CITI,
currency is USD, and month is less than 10. To do this, you must
first define the variables bank, currency, month, and amount, in
this example. The way to define these is :
(1) highlight your entire database, including the titles in the
top row
(2) do Insert, Names, Create
(3) put an x next to "Top Row" (only, no other x's)
You will now have each column defined as a range, with the range name equal to the respective column title. Then enter a formula like above. You must enter it as an array i.e. don't just hit enter after typing it, but hit Ctrl-Shift-Enter.
Return to Table of Contents
Q. Have a macro which deletes a sheet out of
a workbook, but when the macro is run a dialog box comes up
asking if you would like to permanently delete the sheet. Is
there anyway to bypass this dialog box so that the user would not
have to push the yes button all the time.
A. (From Jim Sproull sproullj@cplabs.edm.ab.doe.ca )
Try something like the following code
Application.DisplayAlerts = False
ActiveWorkbook.Sheets("OldSheetName").Delete
Application.DisplayAlerts = True
Return to Table of
Contents
(From am@microsoft.com (Andy Miller) Organization: Microsoft)
Take a look at this article from www.microsoft.com/kb:
The information in this article applies to:
- Microsoft Excel for Windows, versions 5.0, 5.0c
- Microsoft Excel for Windows NT, version 5.0
- Microsoft Excel for Windows 95, version 7.0
- Microsoft Excel for the Macintosh, version 5.0, 5.0a
- Microsoft Excel for the Power Macintosh, versions 5.0, 5.0a
----------------------------------------------------------------------
SUMMARY
When you use Microsoft Excel, you may want to display the
Open or the Save As dialog boxes using Visual Basic for Applications
procedures. This article explains several ways to accomplish this
task.
Note that the Open and Close methods do not display these dialog
boxes.
MORE INFORMATION
Microsoft provides examples of Visual Basic for applications
procedures for illustration only, without warranty either expressed
or implied, including but not limited to the implied warranties
of merchantability and/or fitness for a particular purpose. This
Visual Basic procedure is provided 'as is' and Microsoft does not
guarantee that it can be used in all situations. Microsoft does
not support modifications of this procedure to suit customer
Method 1: This Visual Basic for applications
code uses the xlDialogOpen and xlDialogSaveAs constants to
display the dialog.
To open a file:
Application.Dialogs(xlDialogOpen).Show
To open a file named "myacct.xls" in the
"c:\personal" directory:
Application.Dialogs(xlDialogOpen).Show
("c:\personal\myacct.xls")
To save a file:
Application.Dialogs(xlDialogSaveAs).Show
To save a file in the "c:\public" directory named
"acct.xls":
Application.Dialogs(xlDialogSaveAs).Show
("c:\public\acct.xls")
Method 2: Use the GetOpenFilename and
GetSaveAsFilename methods to retrieve the file name in
conjunction with the Open or Close methods.
To open a file:
Workbooks.Open filename:=Application.GetOpenFilename
To save the active workbook:
Activeworkbook.SaveAs filename:=Application.GetSaveAsFilename
Method 3: Use the Microsoft Excel 4.0 macro
language equivalents to display the dialog boxes.
To open a file:
Application.ExecuteExcel4Macro("Open?()")
To save a file:
Application.ExecuteExcel4Macro("Save.As?()")
For additional information, please see the following
articles in the Microsoft Knowledge Base:
ARTICLE ID : Q122507 - TITLE : MXL5: Extension Overrides Default
Directory Setting
ARTICLE ID : Q134260 - TITLE : OPP: Cannot Specify a Directory
Using xlDialogOpen
ARTICLE ID : Q104581 - TITLE : Macro to Change Default *.XL* to
*.* in Open Dialog Box
REFERENCES
Microsoft Press: "Excel 5 Visual Basic for Applications
Reference," version 5.0, pages 452, 576
Return to Table of Contents
Procedure to sort sheets within the active book
Sub Sort_Active_Book1()
Dim i As Integer, j As Integer
For i = 1 To Sheets.Count
For j = 1 To Sheets.Count - 1
If UCase$(Sheets(j).Name) > UCase$(Sheets(j + 1).Name) Then
Sheets(j).Move After:=Sheets(j + 1)
End If
Next j
Next i
End Sub
Return to Table of Contents
Q. Want to set the cursor to an hour glass in a macro
A. (From: Krzysztof Maciejko unipack@unipack.com.pl ) Try this
Application.Cursor=xlWait
To reset the cursor to normal use:
Application.Cursor=False
Return to Table of Contents
Q. If a spreadsheet range of cells is already selected is there a direct way to name the range (Excel 5.0 VBA) without resorting to code like the following that requires saving and using separate values for the start and end of the range?
ActiveWorkbook.Names.Add Name:="LaborData", RefersTo:="='Source
Data'!" _
& RangeStart & ":" & RangeEnd
A. (From Jim Sproull sproullj@cplabs.edm.ab.doe.ca) Try the following code
Sub NameRange()
'Adds a name to the active sheet,
'giving the name to the selected range.
Dim thisSelection As String, thisSheet As String
Dim thisReference As String
thisSelection = Selection.Address
thisSheet = ActiveSheet.Name
thisReference = "=" & thisSheet & "!"
& thisSelection
ActiveWorkbook.Names.Add Name:="thisrange",
RefersTo:=thisReference
End Sub
Return to Table of Contents
Q. Have a spreadsheet with
dates that were entered as text (starting with an aphostrophe). Need
to convert the text to dates.
A. (From Frank Isaacs isaacsf@ix.netcom.com)
The apostrophe disappears when you request the Value property of
the cell.
Sub RemoveApostrophe3()
Dim C As Range
Selection.NumberFormat = "m/d/yy"
For Each C In Selection.Cells
C.Value = C.Value
Next
End Sub
Return to Table of Contents
Q. Have a spreadsheet that has a column name
field in the form "last name,first name". Needs to
split last name and first name into two separate fields. The
final goal is importing it into an Access data base.
A. (From Jim Sproull sproullj@cplabs.edm.ab.doe.ca)
Try the following procedure
Sub Split()
Dim MySelection As Range
'Assuming the column of names to be split is surrounded
'by blank cells and a cell in the column is selected.
'If not MySelection will have to be set accordingly.
Set MySelection = Selection.CurrentRegion
MySelection.TextToColumns Destination:=MySelection, DataType:= _
xlDelimited, TextQualifier:=xlNone, ConsecutiveDelimiter:=False,
_
Tab:=False, Semicolon:=False, Comma:=True, Space:=False, _
Other:=False
End Sub
Return to Table of Contents
If you have any comments, suggestions or gripes about the way the data has been presented please contact us at waa@juno.com . We welcome your input.