Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

VBA Function for TAT

Hi expert,

I have a micro fucntion which name is Getworkday for calculate TAT but its not remove holiday list. Its working but only not remove holiday list.

Code

Function GetWorkdays(FirstDate As Date, LastDate As Date, _
Optional Hols As Variant) As Integer
Dim i As Integer, ii As Integer, wkdys As Integer
Dim dy As Date
Dim f As Boolean
wkdys = 0
For i = 0 To (LastDate - FirstDate)
dy = CDate(FirstDate + i)
If Weekday(dy) <> 1 Then
f = False
If Not IsMissing(Hols) Then
For ii = 1 To Hols.Count
If Len(Hols(ii)) = 0 Then Exit For
If CDate(Hols(ii)) = dy Then
f = True
Exit For
End If
Next
End If
If Not f Then wkdys = wkdys + 1
End If
Next
GetWorkdays = wkdys
End Function


Post your answer or comment

comments powered by Disqus
Does anyone know of any code for custom functions or VBA functions for Avg,
Std, Min, Max? I had been using the Excel functions (i.e.
application.worksheetfunction.) but have run into the 65326 contraint when
trying to apply these to an array populated by an Excel range. I need to
pass the VBA array to these VBA functions to get the Avg, Std, Min, Max.

Thanks

EM

Sorry I posted this in the wrong newsgroup originally.

Does anyone know of any code for custom functions or VBA functions for Avg,
Std, Min, Max? I had been using the Excel functions (i.e.
application.worksheetfunction.) but have run into the 65326 contraint when
trying to apply these to an array populated by an Excel range. I need to
pass the VBA array to these VBA functions to get the Avg, Std, Min, Max.

Thanks

EM

Does anyone know of any code for custom functions or VBA functions for Avg,
Std, Min, Max? I had been using the Excel functions (i.e.
application.worksheetfunction.) but have run into the 65326 contraint when
trying to apply these to an array populated by an Excel range. I need to
pass the VBA array to these VBA functions to get the Avg, Std, Min, Max.

Thanks

EM

I'm trying to create an identifier on data extracts outs of the accounting systems where the identifier field/cell could have many different descriptions for the same thing. See attached. I would like to use a vba function where i could create a series of if/ then statements to identify each line item by a grouping type. On the attached sample the identifier fields would be type and unit price.

Hello,

I need to concatenate invoice numbers and separate them with a comma and a space.

I don't want a trailing comma at the end.

I have repeated instances of the same invoice number but I only want the concatenation to pick up one instance of each.

For example, if I have the data:

30251111
30251111
30251111
30251112
30251112
30251112

I want it to appear as:

30251111, 30251112

Is anyone able to provide me with a VBA function to copy/paste into a module that will do this for me?

Many thanks.

I am trying to create a function in VBA (possibly an add-in in the future) to
calculate Relative Standard Deviation (RSD). I would like to be able to use
an equation such as: =RSD(values), where the values are cell references (a
variable quantity) chosen by the user.

To do this I've copied scripts for "Mean" and "StdDev", then wrote a small
function for RSD that calls those two. When I try to implement RSD in the
worksheet (or even just Mean), the cell returns an error: #VALUE! I think
the problem has something to do with the Arr() variable used as the argument,
as I've tried some simpler functions with array arguments and can't get them
to work, but using standard non-array arguments works fine. I can write a
subroutine that calls the functions and displays the correct result, but I
would like to be able to use RSD as a typical function in my worksheet.

If I could get Mean to work properly, I should be able to apply that
knowledge to StdDev and RSD. The text for Mean is listed below. I've tried
quite a few modifications to try to make it work, so if someone could get it
to work and reply with the correct function text (or with another function
that performs similarly), I'd appreciate it. Thanks.

Function Mean(Arr() As Single)
Dim Sum As Single
Dim i As Integer

Sum = 0
For i = 1 To UBound(Arr)
Sum = Sum + Arr(i)
Next i

Mean = Sum / UBound(Arr)
End Function

I want to program a custom function using VBA. The normal Excel function I would type in a worksheet cell is:

=INDIRECT(ADDRESS(ROW($M7),N$1627,1))

which would give the the result in the cell located at the cell address for the row and column numbers noted in cells M7 and N1627, respectively.

What would be the VBA code for a custom function to shorten this formula in order to derive the same result?

I am trying to create a function in VBA (possibly an add-in in the future) to
calculate Relative Standard Deviation (RSD). I would like to be able to use
an equation such as: =RSD(values), where the values are cell references (a
variable quantity) chosen by the user.

To do this I've copied scripts for "Mean" and "StdDev", then wrote a small
function for RSD that calls those two. When I try to implement RSD in the
worksheet (or even just Mean), the cell returns an error: #VALUE! I think
the problem has something to do with the Arr() variable used as the argument,
as I've tried some simpler functions with array arguments and can't get them
to work, but using standard non-array arguments works fine. I can write a
subroutine that calls the functions and displays the correct result, but I
would like to be able to use RSD as a typical function in my worksheet.

If I could get Mean to work properly, I should be able to apply that
knowledge to StdDev and RSD. The text for Mean is listed below. I've tried
quite a few modifications to try to make it work, so if someone could get it
to work and reply with the correct function text (or with another function
that performs similarly), I'd appreciate it. Thanks.

Function Mean(Arr() As Single)
Dim Sum As Single
Dim i As Integer

Sum = 0
For i = 1 To UBound(Arr)
Sum = Sum + Arr(i)
Next i

Mean = Sum / UBound(Arr)
End Function

My task is to create a custom VBA function that calculates the geometric mean. I know there is a worksheet function already but this is for a VBA course. The following assumptions are:
Write a custom function to calculate the geometric average of the returns listed in cells B6:B15. Below is the formula for a geometric average:
[(1+R1 ) * (1+R2) *...*(1+Rt)]^(1/t)-1
There are three requirements for your function:
i. Your function should have a single input – the range of cells that contain the return series.
ii. Use a For/Next loop to cycle through the returns in the range provided by the user.
iii. Use an If/Then statement to determine if the returns are entered in decimal or percentage point form. You may assume that values above 1 are in percentage point form.
ANY HELP PLEASE!!!
============================================================================================================================
Function MyGEO(Returns As Range)
Dim Varcount As Integer
Dim t As Integer
Dim mean As Double
Dim geomean As Double

If Returns >= 1 Then Returns = Returns / 100

Varcount = Returns.Count

mean = 1
For t = 1 To Varcount
mean = mean * (1 + Returns(t))

Next t
geomean = mean ^ (1 / Varcount) - 1
MyGEO = geomean
End Function


Hi,

Is there some kind of translation dictionnary between excel functions and vba function?

For example :

find in excel = instr in vba
lower in excel = Lcase in vba
etc...

Thank you for your help

Hi all,
I've written a VBA function for use in Excel, but have found that though it works in the original worksheet's tab, if I were to copy/move this tab into a new worksheet, the macro is undefined (which makes sense to me). Is there a declaration I must use for the macro to work on all worksheets it is copied to? I have been fixing the problem by copying my code into the new worksheet's modules, but I'm pretty sure there must be a better way. Obviously, I don't know much about the structure of VBA, so any help is appreciated.

Thank you very much in advance.

I'm trying to build a VBA Function for Excel 97 that will return TRUE if the word is valid, and FALSE if it isn't. I built the following, but the result is always FALSE. Any ideas?

Here it is:
Function SpellCheckTest(InputText)
SpellCheckTest = _ Application.CheckSpelling(InputText)
End Function

First off I am not expert in this area.

I am using excel 2000 on WinXP Pro SP2.

I have used a vba function for calculating like NetworkDays function except this custom function (NetworkDaysMisc) can calculate the number working week weekdays that you specify (e.g Mon to Sat). Code can be found here -> http://www.bettersolutions.com/excel...I327411311.htm

this works very well and does what I want. Then under the doubleclick subroutine of the worksheet I have code to add and remove rows depending on what the cells contents is. Here is my code;
 
Sub InsertRow()
    Dim DeleteRow As Boolean
    
    DeleteRow = False
    
    If ActiveCell = "Double click here to add SR" Then
        Selection.EntireRow.Insert
        ActiveCell = "New SR Entry"
        Application.SendKeys "{F2}"
        Application.SendKeys "{HOME}"
        Application.SendKeys "+{END}"
        'ActiveCell.Offset(1, 0).Select
        'ActiveCell.Font.Italic = True
        'ActiveCell.Font.ColorIndex = 15
    Else
        DeleteRow = True
    End If
    If ActiveCell = "New SR Entry" And DeleteRow Then
        'ActiveCell.Offset(-1, 0).Select
        ActiveCell.EntireRow.Delete 'shift:=xlShiftToLeft
        Application.SendKeys "{F2}"
        Application.SendKeys "{HOME}"
        Application.SendKeys "+{END}"
    End If
End Sub

Ok. Now on my spreadsheet I have a cell to calculate my number of working days of a date range using the vba function like I first stated. The cell formula looks like this

=module1.networkdaysmisc(K2,K3,I34:I48,J18:J24)

So now the problem I am experiencing that I hope someone can help me with please. When I double click my cell with contents of "Double click here to add SR" and new row is inserted above that cell. And the cell that uses the vba function to calculate the workdays displays correct value. But then if I double click the cell with contents "New SR Entry" that is in the newly created row, the row deletes as it should but now the cell calculating the networkdaysmisc becomes #VALID!. And I cant work out why? If I go to the cell using the networkdaysmisc function I find the reference cells and ranges are correct and so all I need to do on this cell is enter edit mode (F2 key) then press enter and is now display correctly.

I have tried moving/relocating the cell reporting the networkdays up above where the rows can be added and deleted but this make no difference. So it make me think that something like vba code needs to be restarted. I dont know really.

I figure I need to add some code, or the sheet is not refreshing/updating. Can anyone figure this out and help me?

I have though of vba code to move to this problem cell (ActiveCell.Offset(2,0).Select) and simulate the F2 and enter keys. But there must be a better way and a reason why this happen and how to avoid this problem.

Thanks in advance,

Paul.

Hi,

I have a set of four raw values that I want to put in a calculation - Debt/Ebitda, OAM/TA, OpProfit and QuickRatio. Now, each value must fall between the cap (max possible value) and the floor (min possible value) for that combination of value (four types in total, as above) and industry (ten types in total). Say OpProfit for Agriculture has a range from 0 to 0.4, so if I have a ratio of 0.5 I want to use 0.4 in my calculations. I have to do a VLOOKUP-esque VBA function for this currently.

Also for Debt/Ebitda it's slightly different - if the raw value falls below the floor, the cap value is used.

Is there a way of capping / flooring my raw values using VBA functions before I put them in my equation? I know you can do it using a series of IF() worksheet functions but I'm already running the calculations using a VBA function.

Thanks loads

Visual Basic for Applications (VBA) functions break in a database with ...

Visual Basic for Applications (VBA) functions break in a ... Article ID : 283806: Last Review : November 27, 2007: Revision : 5.3 ... If the referenced file is in a new location, clear ...

Visual Basic for Applications (VBA) functions break in a database with ...

Describes the error messages that you may receive when you compile modules or run a procedure. Problem occurs if the procedure contains a VBA function and if your database contains ...

Is there a function in VBA that is similar to either the FIND function or SEARCH function in Excel? The arguments for the FIND function in Excel are FIND(find_text, within_text, [position]).

I have a text string in VBA ("$A1:$D$13") that I want to be able to identify the first "$" and then later the ":". I'm getting tripped up on the 3rd line of code. Thanks a million.

Code:
Sub page_set_print_area()
    ActiveSheet.PageSetup.PrintArea = "$A$1:$D$13"
    x = ActiveSheet.PageSetup.PrintArea
    Position$ = Search("$", x, 0)
End Sub
Jared

Hi,
This is probably moronicly simple, but I've been staring at it for hours ....

The task appears simple, I have a named range "ListOfReps" in B2:B4 with the names Tom, Peter and David respectively listed. I am trying to create a Boolean function that I can use in the spreadsheet to check to see if a name is on the list, i.e., =IsRep("David") or =IsRep(C2). My code is simple:

Function IsRep(RepName As String)
Dim C As Range

Set C = Sheets("Sheet1").Range("ListOfReps").Find(RepName)

If C Is Nothing Then
IsRep = False
Else
IsRep = True
End If

End Function

If I call IsRep from a VBA function, it works correctly; however, if I enter a formula in a spreadsheet, "=IsRep("Tom") and singlestep through the code, C is never non-empty. Any idea what I am doing wrong?

Thanks,
David

Visual Basic for Applications (VBA) functions break in a database with ...

Describes the error messages that you may receive when you compile modules or run a procedure. Problem occurs if the procedure contains a VBA function and if your database contains ...

Dear All,
Can someone help me with writing a VBA code for the Dsum function. I basically want to have a VBA formula which can help me write the criteria in the VBA formula itself rather than as reference to another range as it is currently in the Dsum function. I have attached an excel file where I am showing the Dsum function, however, I want to do the same thing, but using a VBA code. The reason is that I have a huge amount of data and I want to avoid writing the Criteria in a separate range of cells and then refer to the range in the Dsum function. Many thanks for your help.

I am trying to come up with some code to replace a sumproduct function. I have been doing some research and it appears that sumproduct is only a worksheet function and not a VBA function.

Below is the formula I am using as a worksheet formula. I am using 3 defined names within the formula called “Publisher”, “Expenses”, and “ExpenseDate”.

Below is the VBA code I tried to use.

 Sub Macro2()

    With Sheet2
        For i = 3 To 29
            Range("C" & i).Value = Application.WorksheetFunction.SumProduct((Publisher = Range("A"
& i)) * (Month(ExpenseDate) = Month(Range("B" & i))) * Expense).Value
        Next i
    End With
    
End Sub
Is there an alternative way to accomplish this? Thanks.

I require vba proc for a replace function to be able to specify a find string using a known prefix & a known suffix with a wildcard for the unknown portion (wildcard will always be between the prefix and suffix).

And replace all instances of find criteria within text and replace with replace text (in this example replace text = nothing)
The find string may exist 1, many or no time within the text string.

Ideally I would like the macro to accommodate eith * or ? wildcards.

Text = “abc 1234 def xyz abc 1234 def”
Replace = “”

Eg 1
Find = “bc*de”
Result = “abcdef xyz abcdef”

Eg 2
Find = “bc 1*4 de”
Result = “abc 14 def xyz abc 14 def”

Eg 3
Find = “bc 1?34 de”
Result = “abc 134 def xyz abc 134 def”

Eg 4
Find = “bc 1??? de”
Result = “abc 1 def xyz abc 1 def”

Eg 5
Find = “bc ?? de”
Result = “abc 1234 def xyz abc 1234 def” (match not found)

Any help would be appreciated

I am using the Essbase Excel Add-In to interface with an Essbase cube.

I need a list of Essbase functions (like EssVRetrieve() or
EssVSetSheetOption () - inlcuding syntax and examples - that can be used
inside VBA code.

Can anyone point me in the right direction?

Thanks for your help ...

bill morgan

I'm trying use Excel's Data Validation with a custom validation formula, but the formula length is limited to 255 characters.

In order to use a longer formula, I've implemented a validation function in VBA. I can call this function from a cell, but when I try using it in Excel's custom validation formula, it thinks the function is a named range.

Is there any fix for this short of implementing validation completely on my own?

-David


No luck finding an answer? You could always try Google.