Formulae
and Other VBA Issues.
This page contains help on Excel
Formulae and other related items.
|
|
Formulae
and Other VBA Issues. |
Array Formulae/Formulas Explained:
[Back to Menu List]
Need help with array formulae? MVP Bob Umlas has an excellent site for array
formulas and lots of tips and tricks..
http://www.emailoffice.com/excel/arrays-bobumlas.html
Formula for counting number of fortnights.
[Back to Menu List]
Array enter (control + shift + enter):
"=SUM((DAY(ROW(INDIRECT(VALUE
Where your start and end dates are in A1 & B1 respectively for Sum of the Days
of the month between the two dates equals one. ( i.e. the first or fifteenth of
the month ) * Thanks to Wyatt Lemmons
Help with SUMPRODUCT. [Back to Menu List]
SUMPRODUCT is one of the most versatile functions provided in Excel. In its most basic form, SUMPRODUCT multiplies corresponding members in given arrays, and returns the sum of those products. The link discusses the classic use of SUMPRODUCT, how creativity and inbuilt flexibility has enabled it to evolve into a far more useful function. A great introduction to SUMPRODUCT.
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Why do some SUMPRODUCT formulae contain "--" in the formula?
For example have a look at the following two formula, which product the same result, both using SUMPRODUCT
=SUMPRODUCT(--(Jobs!$E$2:$E$1215="AUCK"),--(Jobs!$C$2:$C $1215=S3))
=SUMproduct((Jobs!$E$2:$E$1215="AUCK")*(Jobs!$C$2:$C$1215=S3 ))
So what does the use of the double negative "--" in the formula accomplish and what is the difference between these two formulas and how/why
do they behave differently? Bob Umlus (MVP Excel L developers list) explains they don't behave differently, and they don't produce different answers.
SUMPRODUCT syntax is a list of numeric arrays. Jobs!$E$2:$E$1215="AUCK" is an array of TRUE;FALSE, which is not numeric.
In the post with the multiplication, this winds up being numeric arrays because TRUE * TRUE = 1, and any other combination is 0, so it winds up
being a numeric array.
But 1,214 items are being multiplied together. The one with the --(...) changes the TRUE;FALSE to --{1;0 ...} which is {1;0,...}, a numeric array.
But without the multiplication, it simply executes more quickly. If this were your only SUMPRODUCT formula, you can't notice the difference,
but if you have 10,000 of these formulas, you would.
Or put simply, SUMPRODUCT requires a list of numeric arrays. The arrays shown product arrays of True/False (not numeric). Putting a "-" negative in front of true/false makes it -1/0. We don't want -1, so a 2nd negative changes this to an array of 1/0.
thanks to Bob Umlas - Excel-L Mailing list
How to get Excel to work with numbers greater than 15 Characters : [Back to Menu List]
It is possible to make Excel work with more significant figures
using an add-in. The one I like best is at
http://digilander.libero.it/foxes/SoftwareDownload.htm, which allows you up
to 200 sf. The key limitation to this is that every user of the spreadsheet
with high-precision numbers would need to have the add-in installed. It is
also non XL2007 compatible.
Before you do anything, consider if you really need the value to
be a number. If you are not going to do any maths on it, than it can be
formatted as
text instead. Credit card numbers would be a good example of this.
If you absolutely have to use really large numbers then consider the following
(with thanks to Doug McNutt). The reason for this limit is it's not Excel's
design and it's not magic.
It's the IEEE 64 bit floating point standard that chips, Intel and others, have
been using for a long time. Excel uses floating point because nobody would
be happy with the speed of base 10 arithmetic. The IEEE standard uses 52 bits
for the significant digits of a number, 1 for sign and the remaining 11
for a power of two that the 52 bit value is multiplied by.
That translates to numbers between 10^-307 to 10^307 with 15, or so, decimal
digits of precision. The actual precision depends on the number being
represented. Numbers like 1/2 or 1/2048 come out exact. Numbers like 1/10
do not in the same way that 1/3 can't be expressed exactly in base 10.
Almost always, 15 digits is quite enough.
The problems occur with things that are not really numbers.
Telephone numbers with country codes and no internal punctuation, for instance.
Excel should probably recognize impossible input and generate an error or
automatically prepend a ' (single quote) character automatically. That will
ask Excel to treat such numbers as TEXT. But you can do that yourself or you can
format the cell as TEXT before
you enter the number, for example '937205992294200102.
IF Statements and IF Functions explained : [Back to Menu List] Need help with Excel's IF functions, looking at one of these links below may help you: Introduction to IF Statements in Excel: http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=225 Excel IF Statements, Part II: Nesting Functions: http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=242 Demystifying the Use of the IIF Function in VBA: http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=799 Other links on using the IF Function: http://spreadsheets.about.com/od/excelfunctions/a/if_funct_hub.htm http://www.techonthenet.com/excel/formulas/if.php http://www.ozgrid.com/News/ExcelLogicalFunctionsIfAndOrVBARangeNames.htm#excel
Sum by Colour / Sum by Color
[Back to Menu List]
I am often asked "Can I Sum by Colour/Color in Excel" or "How do I Sum by Colour/Color in Excel". I have attached a workbook for download that demonstrates how you can sum by colour/color using either the cell's interior colour, or the font colour. This requires VBA coding to work though as it is not a standard Excel feature. This workbook will allow the user to colour / color a cell and then sum all values that match that colour.
Download Sample Excel File Here
or view code without downloading sample file:
=SUMBYCOLOR(H1:H289,6,FALSE) as an example of the actual
formula.
Where the criteria are "=sumbycolor(Range,ColorIndexID,False =
Use Interior Color OR True = Use Font Color)
Put the UDF below in a code module in the VBE.
' --------- START CODE------------------------------
Function SumByColor(InRange As Range, WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Double '
' This function return the SUM of the values of cells in ' InRange with
a background color, or if OfText is True a ' font color, equal to
WhatColorIndex.
'
Dim Rng As Range
Dim OK As Boolean
Application.Volatile True
For Each Rng In InRange.Cells
If OfText = True Then
OK = (Rng.Font.ColorIndex = WhatColorIndex)
Else
OK = (Rng.Interior.ColorIndex = WhatColorIndex)
End If
If OK And IsNumeric(Rng.Value) Then
SumByColor = SumByColor + Rng.Value
End If
Next Rng
End Function
' --------- END CODE------------------------------
Formula to calculate
months in a quarter.
[Back to Menu List]
"=N(MONTH(A1)-INT((MONTH(A1)-1)
Thanks to Bob Phillips (Excel-L List)
How do I transfer
data between MS Access and MS Excel?
[Back to Menu List]
I am also often asked "How do I move data from Access to Excel, or Excel to Access". There are numerous ways of doing this, however I like to use the recordset method as it is far more reliable than the 'transferspreadsheet' method, which is version dependent and often buggy (well in my experience at least). I have attached a workbook for download that demonstrates how you can move data between Access and Excel. In reality I use both, but for ease of understanding I have put the techniques into two seperate zip file. One will download data from Access to Excel, based on a set of parameters choosen by the user. The other zip file will populate an Access table with data from Excel.
Download example of Excel to
Access data transfer Here
Download example of
Access to Excel data transfer Here
There are many ways of doing this, this is just
one method that has proven to be very reliable for me in a professional /
corporate environment. Also see the
Access Page for a code sample
of pulling the data from Excel using Access as the master.
If none of these samples help you out I suggest you try the link below which
details the pros and cons of all methods of using code to transfer data between
Access and Excel:
http://www.zmey.1977.ru/
Prevent users from
opening another workbook in the same application of Excel.
[Back to Menu List]
Put this code in the ThisWorkbook Sheet Module. In practice I
have found it doesn't work 100% of the time and sometimes you can open workbooks
in the same app, but it works often enough to prevent too many foul ups.
'=========================================================
Option Explicit
Private WithEvents objXLApp As Excel.Application
'-----------------------------
Private Sub Workbook_Open()
On Error Resume Next
Set objXLApp = ThisWorkbook.Application
''' Do your other stuff on open here
End Sub
'-----------------------------
Private Sub objXLApp_NewWorkbook(ByVal Wb As Workbook)
Dim objNewApp As Excel.Application
Dim lngVisCount As Long
Wb.Close False
If MsgBox("You can't open a new workbook in this instance of Excel." _
& vbLf & vbLf & _
"Open a new instance of Excel?" & vbLf & vbLf & _
"* Choose 'Yes' to open a new instance of Excel" _
& vbLf & "* Choose 'No' to NOT open a new workbook", _
vbYesNo Or vbQuestion) = vbYes Then
Set objNewApp = New Excel.Application
objNewApp.Visible = True
objNewApp.Workbooks.Add
objNewApp.UserControl = True
End If
Application.EnableEvents = True
End Sub
'-----------------------------
Private Sub objXLApp_WorkbookOpen(ByVal Wb As Workbook)
Dim objNewApp As Excel.Application
Dim strName As String
strName = Wb.FullName
If ThisWorkbook.FullName <> strName Then
Wb.Close False
If MsgBox("You can't open the workbook in this instance of Excel." _
& vbLf & vbLf & _
"Open in a new instance of Excel?" & vbLf & vbLf & _
"* Choose 'Yes' to open a new instance of Excel and load the
workbook" _
& vbLf & "* Choose 'No' to NOT open the workbook", _
vbYesNo Or vbQuestion) = vbYes Then
Set objNewApp = New Excel.Application
With objNewApp
.Visible = True
.Workbooks.Open strName
.UserControl = True
End With
End If
Else
Application.EnableEvents = True
' This workbook, so OK to open
Exit Sub
End If
Application.EnableEvents = True
End Sub
'====================================================================
VBA Dev Handbook PDF link (Searching and
Sorting in VBA).
[Back to Menu List]
Great Link for sorting and search in Excel and VBA.
http://www.developershandbook.
If you get this message "A required installation file SKU011.cab could not be found" here is how you can fix this issue and repair office 2003
Firstly: Open up the registry and modify the entry as per the instructions below
Goto "Start" > Choose "Run" > Type "regedit" to
open the registry editor. Then use the navigation pane to locate the
following:
regedit -> [HKEY_LOCAL_MACHINE] -> [SOFTWARE] -> [Microsoft] -> [Office] ->
[11.0] -> [Delivery] -> {90000409-6000-11D3-8CFE-0150048383C9} -> [CDCache]
change to [0] …. Done!
Now
download the following zip file
here
(which contains PA561401.cab, YT561401.cab, ZS561401.cab) Unzip the file and
save the .cab files (as they are) in the following folder location: "C:\MSOCache\All
Users\90000409-6000-11D3-8CFE-0150048383C9 file"
If you cannot open zipped files then get the individual files here:
PA561401.cab
YT561401.cab
ZS561401.cab
Also see:
http://hkmicc.wordpress.com/2006/09/25/office-2003-sku011cab-problem/ if
you need more help/advice
If the download
from this site is not available then try this alternative download location:
http://www.knights-templar.com/FRONTPAGE2003/ZS561401.CAB
http://www.knights-templar.com/FRONTPAGE2003/PA561401.CAB
http://www.knights-templar.com/FRONTPAGE2003/YT561401.CAB