|i am trying for a function for excel which is to sum up excel's data sheet|
Below is the module I have come up with. Not much of VBA coder, But did the
best I could, Some hardcoding is done like Sheet name i have assumed to be
"Sheet1" and "Sheet2", You can change those as per your need, and neater
nested IF can be used:
Dim hold_last_value As String 'To hold the value of code of
previous cell for a given id
Dim counter As Integer 'To hold the value of days count for
which a given event was held continuously for a given id
Dim from_date As Variant 'To hold the start date of the event
Dim to_date As Variant 'To hold the end date of an event
Dim row_writer_index As Integer 'To hold the value of the row
to which the next output is to be written, Is passed as a ByRef argument to
|Excel equivalent Google Docs function to "query()" custom VBA function|
Based on @chuff's approach, give your data array labels (I chose A>Z from
A14:Z14) and name the array (A14:Zn) - I chose GParry.
DATA > Get External Data - From Other Sources, From Microsoft Query,
Databases, Excel Files*, OK. Select your workbook name, OK, Table: GParry,
In the list under GParry, double-click on A, J and Z in turn, then click on
the SQL button. In SQL statement: change SELECT to SELECT TOP 9 and append:
WHERE J = 'WH'
ORDER BY Z DESC
If you want the results back into Excel just select the extract (top left),
Edit, Copy and Paste where it suits you.
|Sub function Excel VBA|
Its the function-like parentheses it dislikes so either;
or less advisably:
|Excel file created in Excel 2007 is not opening in Excel 2003 using c#|
If you make an Excel file programmatically and name it with the .xls
extension from Excel 2007 or 2010, you may be making an Open XML (.xlsx)
file unknowingly, as this is the default save format for those versions of
Excel. Essentially, if you are hard coding the file name, you may be
saving an .xlsx file with the .xls extension, which would explain why you
can't open the files. Try renaming the files generated from Excel 2007 to
the .xlsx extension and see if you can open them.
|Display Excel inside VB6 / VB.NET Form & work with Excel without excel menu|
As far as I can tell - this is not possible. The reason being that you
cannot subclass the excel window in VBA.
If you program outside of excel with VB.NET or VB6 it would be sort of
possible, but would probably not work really good.
If you only want to display some data from a sheet and perform some small
actions on them, you could probably read the Data via the EPPLUS Library,
display them as a datatable (EPPLUS can convert between datatable and excel
files), perform your actions and save them to the file afterwards. But this
would only work in VB.net
|Excel VBA Function Recalculation|
You could try to add additionally
at the very beginning, in the first row inside you function, like:
Public Function MyTableResults(Code As String) As Variant
'...your code here
However, it could help in some situation like inserting rows/columns but
will not help when deleting rows (which I've tried).
|Using the "Cells" function in excel vba|
You're using the Range type like a function; the Cells call is actually
returning you a Range object already. Instead of
Range(Cells(1, (21 + itemcnt))).Select
Cells(1, (21 + itemcnt)).Select
If you want to be more explicit:
Dim target As Range
Set target = Cells(1, (21 + itemcnt))
|How to execute this Excel to XML function in a sub?|
To convert to a Sub that could be run from a button you would change it to:
Public Sub ExportToXML()
This will automatically change the last line to End Sub.
FullPath and RowName will no longer be passed as function-arguments, so
would, presumably, need to be read from cells on a worksheet, or perhaps
from two InputBoxes.
The Sub would no longer return a Boolean value, so whatever happens with
this value would have to be converted to code within the same Sub (or
possibly passed to another Sub).
|Using the IF function in Excel 2010|
Assuming a data setup similar to this:
In cell F2 and copied down is this formula:
You could then create a chart from the data in columns D:F. Does that work
|excel function INDIRECT()|
The formula A:A returns the value in column A that's on the same row as the
formula. So, if your formula is in B6, then INDIRECT($D$2&"!A:A") will
return A6 from the sheet referenced in D2
|Excel Nested IF Function with AND/OR|
Doing a custom format for cells in column B should do the trick, with no
coding / functions needed. Choose the cells you want to change, right
click, go to format cells. From there choose the Number tab, and choose the
Custom category. You could do something like "I"000000 in the box under
|Excel 2007 VBA VLookup function|
Dim rngNums As Range
Dim cmpny As Range
Dim v As Long
Dim ws As Worksheet
Dim result As Variant
Dim c As Range, nm As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error GoTo haveError
Set cmpny = Sheets("CustomerNumberList").Range("A1").CurrentRegion
For Each ws In Sheets
If ws.Name Like "*Sheet*" Then
v = ws.Range("A" & Rows.Count).End(xlUp).Row 'set v =
number of rows
Set nm = ws.Rows(1).Find("cust_num", LookAt:=xlPart)
If Not nm Is Nothing Then
Set rngNums = ws.Range(nm.Offset(1, 0), ws.Cells(v,
For Each c In rngNums.Cells
If c.Value <> "" Then
|Excel function: "Not Isblank" with "Or" and if date is|
I can’t really test this due to how Excel localizes its function names
(which is so stupid…), but you should be able to combine multiple
booleans with AND or OR, depending on which behavior you want.
If you want to check if two cells are empty you can use AND(ISBLANK(H4),
ISBLANK(H5)) as the condition; if you want to check if both are not empty,
you put all that in another NOT: NOT(AND(ISBLANK(H4), ISBLANK(H5)).
Similarly, if you want to check if at least one of the cells is empty, you
can use OR: OR(ISBLANK(H4), ISBLANK(H5)). And if you want to check if none
of the cells is empty, you can again again put that in an AND.
|Excel Function Difference Between Two Dates|
Change time() with timeserial()
Function DIFERENCAHORASMINUTOS(ByVal EntryTime As Date, ByVal ExitTime As
Dim hexit, hentry, mexit, mentry, dminutes, dhours As Integer
hexit = Int(Hour(ExitTime))
hentry = Int(Hour(EntryTime))
mentry = Int(Minute(EntryTime))
mexit = Int(Minute(ExitTime))
If ExitTime < EntryTime Then
If mexit < mentry Then
dhours = (hexit + 24) - hentry
dminutes = (mexit + 60) - mentry
DIFERENCAHORASMINUTOS = TimeSerial(dhours, dminutes, 0)
dhours = (hexit + 24) - hentry
dminutes = mexit - mentry
DIFERENCAHORASMINUTOS = TimeSerial(dhours, dminutes, 0)
If mexit < mentry Then
dhours = hexit - hentry
dminutes = (mexit + 60) - mentry
DIFERENCAHORASMINUTOS = Ti
|Turning an excel formula into a VBA function|
Here is a simple solution...
Building_name = ???
Date = ???
Last_Row = Range("A65536").End(xlUp).Row
For i = 1 To Last_Row
if cells(i,1).value = Building_Name Then
if date >= cells(i,4).value Then
if date <= cells(i,5).value Then
first instance = cells(i,6).value
you should add a test at the end to avoid the case where there is no first
instance in the table
|Convert Excel 'if' function to SQL query|
You can use IIF function as well... Please check the link for description.
So for you it would be something like SELECT IIF (a > b, IIF ( B > C, A, B
), C ) AS Result;
Sytax is not tested but would work.
|Excel calling a function of a C++ program|
You can call DLL functions from excel.
With that, you've got 2 possibilities:
Convert your program into a DLL.
If that's not possible, create a DLL which communicates with your program
(over some IPC mechanism, like sockets for example)
For details, search the Web or ask again :-)
how would the COM/DLL "know" that excel has invoked it and thus
provide some sort of "acknowledgement" and in turn invoke the
doSomething() in program.exe
Excel doesn't invoke the DLL, it invokes a function in the DLL (by calling
it from a VBA macro or a spreadsheet formula). When this function is
executing, it must have been called by something (probably Excel, unless
you call it from somewhere else) so it kind of "knows" that it's been
called. And it can provide feedback to excel through it's ret
|Can I add my own function to the customized status bar in Excel?|
Try something like this (Not the best way to do it though but solves your
Explanation: What this code does is checks if the user has selected a valid
range (of minimum 2 cells) and then uses the Application.Evaluate to
calculate your formula and then display it in the status bar. Also note
that I have not done any error handling. I am sure you will take care of it
Paste this in the relevant sheet code area.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If TypeName(Target) = "Range" Then
If Target.Cells.Count > 1 Then
Application.StatusBar = "My Function: " & _
|Excel VBA - using left function with find|
In order to use the worksheet function Find you need to fully qualify it as
Using Find on its on and VBA will assume that it is a VBA function. Same
goes for match.
|PHP equivalent for Excel's COMBIN(N,K) function?|
Your calculation is failing because you're quickly overflowing the Integer
data type, which is probably just 32-bits on your system. You could use the
arbitrary precision math functions to get around that problem:
If gmp_fact(...) seems to be returning errors or bad results, you're
probably passing it a bad value or assuming it's result is a basic numeric
type. You'll want to use something like gmp_strval to convert the returned
GMP resource into something readable when you're done performing
$a = gmp_fact(1500);
$b = gmp_fact(5);
$c = gmp_fact(1500-5);
$result = gmp_div($a,(gmp_mul($b,$c)));
|Excel QFD (Quality Function Deployment)|
This is mainly created using diagonal borders.
Increase the Zoom of the worksheet
Using the Draw Borders button it is possible to repeatedly draw diagonals -
but it takes precision, this is why you need to zoom
Merged cells may also be used
Once you've got a selection of different borders and merged cells, click in
one of these and double-click the Format Painter so that you can repeatedly
apply its formatting to other cells
When you insert a Shape, and as you drag it, hold the Alt key so that it
will fit neatly in the cell
If you are inserting an Oval hold the Shift key as you drag it so that it
will be a perfect circle, the same for other shapes. (You can hold both Alt
and Shift at the same time, but it's probably not necessary)
Double or right-click a shape to locate the Format option
|In Excel VBA creating a wordwrap function|
Function WordWrap(ByVal Rng As Range, Optional ByVal MaxLength As Long =
100) As String()
Dim rCell As Range
Dim arrOutput() As String
Dim sTemp As String
Dim OutputIndex As Long
Dim i As Long
ReDim arrOutput(1 To Evaluate("CEILING(SUM(LEN(" &
Rng.Address(External:=True) & "))," & MaxLength & ")/" &
MaxLength) * 2)
For Each rCell In Rng.Cells
If Len(Trim(sTemp & " " & rCell.Text)) > MaxLength Then
OutputIndex = OutputIndex + 1
arrOutput(OutputIndex) = Trim(Left(sTemp & " " &
rCell.Text, InStrRev(Left(sTemp & " " & rCell.Text, MaxLength), "
sTemp = Trim(Mid(sTemp & " " & rCell.Text,
Len(arrOutput(OutputIndex)) + 2))
For i = 1 To Len
|Excel VBA StrCmp sub or function not defined|
The functions name is StrComp and to call it you would say :
StrComp( StringArg1, StringArg2, compareMethod) = 0 for a match.
It seems like in your case you would use
if StrComp(Cstr(Worksheets(1).Range("I" & x).Value), "Critical",
vbTextCompare) = 0 then
' you now have found a match
' do something
More on the StrComp(), also here and also see this here on SO
|Trying to use the if function in Excel with a date and a number|
step 1 - create the IF statement so the recipient cell contains X or O
depending of the condition being met or not
step 2 - create a conditional formatting depending of the content of the
cell (X or O)
let me know if I need to elaborate with the actual formula.
Hint: check the AND() Excel function
|Excel macro to insert function|
Dim s As String
s = Range(ActiveCell, ActiveCell.End(xlDown)).Address
ActiveCell.End(xlDown).Offset(1, 0).Formula = "=SUM(" & s & ")"
|VBA Excel Function Error Runtimeerror 13|
You are getting an error on the line calweeks = fillcalweeks(weeks) because
you are assigning a String array (the result of the function fillcalweeks
to a Variant.
You'll notice you declared every variable except calweeks. Since VBA
doesn't have an explicit declaration for this variable it assigns it to a
To fix the problem, start by putting Option Explicit at the top of every
module. When you compile the project, it'll alert you to errors such as
these (Debug -> Compile VBA Project). Then all you need to do is declare
calweeks as a String().
There is a second problem you have and that is that you are trying to store
a Date data type in your String() array within the fillcalweeks. You
either need to convert the datestart, dateend and calweek variables to
Strings (you can us
|Excel rounddown function equivalent in php|
Use Round - float round ( float $val [, int $precision = 0 [, int $mode =
PHP_ROUND_HALF_UP ]] ).
You need to pass PHP_ROUND_HALF_DOWN as 3rd parameter(Mode).
round($value, 4, PHP_ROUND_HALF_DOWN);
If mode parameter is not available in your version of PHP(as it didn't work
for me) then use this:
floor($value * 10000) / 10000;
|Excel Drop Down + search function|
You can use:
Cascading dropdown (such as xls cascading lookup based on pivot table style
datasource? and http://www.contextures.com/xlDataVal02.html)
Use a formula in the Named Range for your List Validation that will
simulate an autocomplete
Create a named range Test with this kind of formula :
your list of data is in column A (values should be sorted alphabetically)
the current cell where you are applying the validation on is C1
Then you can:
start typing the text you want in cell A1, for instance Wash
then click on the drop-down list
the dropdown list will begin at Whashington and so on
I've just translated it from one of my old workbook so tell me if anything
[EDIT] Just a quick test seem to make
|Excel VBA function not working for Class|
SumUp(result = sumValue, number = numValue)
This is invalid on many levels.
If you meant to call SumUp with named parameters, you should have written
SumUp result:= sumValue, number:= numValue
You don't have to use named parameters though:
SumUp sumValue, numValue
|Excel Function within SSRS 2012|
Short answer is no.
As you've probably seen in your research, there was limited support for
this in SSRS 2005 but not in subsequent versions.
See Breaking Changes in SSRS 2008.
In earlier versions of Reporting Services, there was limited support
for translating expressions in RDL to Microsoft Excel formulas. In
this release, when you export a report to Excel, RDL expressions are
not translated to Excel formulas.
Needless to say, this is a popular request, see this Connect item:
SSRS 2008 export formulas to Excel.
While refreshing my memory about all of this, I did see a few notes that
suggested if you use expressions which directly reference the ReportItems
=ReportItems!val1.Value + ReportItems!val2.Value
Then this would work, but I ran a simple test now
|Replicate Excel's mode function using SQL?|
Below statement return mode value if it is single, mode value of the first
(alphabetically) Vendor if set is multimodal, and return NULL if all values
unique (no mode exist).
;with m as (
aCnt = count(1) over (partition by amount),
tCnt = count(1) over ()
select top (1)
when acnt = 1 and tCnt > 1 then NULL
end as mode
order by acnt desc, vendor;
To find mode within (inputfile, code, period) tuple you may try:
;with r1 as (
select inputfile, code, period, vendor, amount,
acnt = count(1) over (partition by inputfile, code, period,
tcnt = count(1) over (partition by inputfile, code, period)
r2 as (
|"Overload" F9 (or shift+F9) in Excel spreadsheet with VBA function?|
According to MSDN Application.Volatile :
Marks a user-defined function as volatile. A volatile function must be
recalculated whenever calculation occurs in any cells on the
worksheet. A nonvolatile function is recalculated only when the input
variables change. This method has no effect if it's not inside a
user-defined function used to calculate a worksheet cell.
This (and other) mean that:
instruction works only with functions, not Module
it will work with all User Defined functions also as a result of pressing
it's required to place the instruction at the beginning of UDF.
See example of UDF with Application.Volatile:
ReturnCurrentTimer = Timer
|What exactly is the function of Application.CutCopyMode property in Excel|
link the answer is as below:
Application.CutCopyMode=False is seen in macro recorder-generated code when
you do a copy/cut cells and paste . The macro recorder does the copy/cut
and paste in separate statements and uses the clipboard as an intermediate
buffer. I think Application.CutCopyMode = False clears the clipboard.
Without that line you will get the warning 'There is a large amount of
information on the Clipboard....' when you close the workbook with a large
amount of data on the clipboard.
With optimised VBA code you can usually do the copy/cut and paste
operations in one statement, so the clipboard isn't used and
Application.CutCopyMode = False isn't needed and you
|Excel - VBA : Make the "replace" function more specific|
sMain.Range("J3").Replace " VA ", "V. A."
then handle the cases where the original string starts or ends with VA
also, handle all cases of separators which could be (for example) tab,
space or comma.
To do that:
const nSep As Integer = 3
Dim sep(nSep) As String
sep(1) = " "
sep(2) = vbTab
sep(3) = ","
for i=1 to nSep
for j=1 to nSep
sMain.Range("J3").Replace sep(i) & "VA" & sep(j), "V. A."
|Need guidance with a VBA function to paste values in Excel|
You can just ''transfer'' the value(displayed) over like this
Function PASTEVALUE(rng As Range)
PASTEVALUE = rng.Text
or use the Evaluate() function to evaluate the formula in that range
Function PASTEVALUE(rng As Range)
PASTEVALUE = [rng]
|Substitute Function in Excel VBA for cell range|
Try this. Note that it uses the VBA Replace function, so you need to
rename your 'Replace` subroutine.
For Each c In Sheets("Sheet1").Range("A1:A629").Cells
c = Replace(c.Value, ",", ";")
Note: This will only work if you have values in the cells, no Formulas.
Because Excel has a formula length limit of 1024 characters. But given you
have this specific error, your cells must not be formulas.
|excel database function in combination with vba, what if there are no records?|
This will check the number of visible cells after the AutoFilter is
Selection.AutoFilter Field:=2, Criteria1:="yes"
ActiveSheet.AutoFilter.Range.Columns.Count > 0 Then
The - ActiveSheet.AutoFilter.Range.Columns.Count part is to subtract the
header cells from the count.
FWIW, when I walked through your original code, I got the 1004 because the
Copy area was from B7 to the bottom of the sheet (the effect of xlDown in
an empty selection).
|I need helping writing an excel vba Splat function|
Try something like this, using the Worksheet_Change event:
Sub Worksheet_Change(ByVal Target As Range)
'Assume the workbook has a named range 'lookuptable' at G3:H6
'Assume the workbook has a named range 'lookupColumn' at D3:D6
If Target.Cells.Count <> 1 Then Exit Sub
If Intersect(Target, Range("lookupColumn")) Is Nothing Then Exit Sub
If Not Left(Target, 1) = "/" Then Exit Sub
Sub UpdateLookupTable(cl As Range)
Dim r As Long
Dim c As Long
Dim splatVal As String
splatVal = Replace(cl.Value, "/", 0, , 1)
r = Application.Match(cl.Offset(0, -1),
Range("lookuptable").Cells(r, 2).Value = splatVal
cl.Value = splatVal
I was not sure whether you intend
|Excel VBA macro for a complicated Copy Function|
Change the workbook and sheet values to the applicable workbook and sheets
Dim c, Rng1 As Range
Dim i As Integer
i = 1
'ask for input
Set Rng1 = Application.InputBox("Please Select Cells", Type:=8)
For Each c In Rng1.Cells
Cells(i, 1).Value = c.Value
i = i + 1
'Prompt for more input
continue = MsgBox("Are there more cells to select?", vbYesNo)
If continue = vbYes Then GoTo userselect
ActiveWorkbook.SaveAs Filename:="pathBook1.txt", FileFormat _
|Replicating Excel's CHIDIST function in Oracle 11g|
Right tail probability... nice...
In oracle you can embedded java code into stored procedures and functions.
The best way is to
use proper java class and call it from oracle's function. It is not that
That is all I can do for you:
l_value_to_evaluate NUMBER (10, 3) := 18.307; /* X; Value at which you
want to evaluate the distribution */
l_degrees_freedom NUMBER := 10; /* Degrees of freedom */
FUNCTION is_number(str_in IN VARCHAR2) RETURN NUMBER
n := TO_NUMBER(str_in);
WHEN VALUE_ERROR THEN