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

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

- Custom or VBA Function for Avg, Std, Min, Max
- Custom functions or VBA functions for Avg, Std, Min, Max?
- Custom or VBA Function for Avg, Std, Min, Max
- Vba Function For Multiple Comparisons
- Vba function for concatenating with comma and space, remove trailing comma
- VBA function for "Mean" using Array as argument
- Vba function for indirect cell references
- VBA function for "Mean" using Array as argument
- Custom VBA Function for Geometric mean
- Excel function vs VBA function dictionnary
- Making a VBA function "universal"
- Spell Check VBA Function
- Excel - delete.entirerow breaks vba function
- Setting a max/min value to use in a VBA function
- Visual Basic for Applications (VBA) functions break in a database with ...
- Visual Basic for Applications (VBA) functions break in a database with ...
- Is there a VBA function for finding a character in a text string?
- VBA function to look for a value inside a named range
- Visual Basic for Applications (VBA) functions break in a database with ...
- Writing a VBA code for Dsum function
- Alternative code for SumProduct as a VBA function
- Vba proc for a replace function using wildcard
- Essbase VBA Functions for Excel Add-In
- Call a VBA function for data validation?

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

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 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.

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

=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?

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

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

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

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.

Here it is:

Function SpellCheckTest(InputText)

SpellCheckTest = _ Application.CheckSpelling(InputText)

End Function

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.

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 ... Article ID : 283806: Last Review : November 27, 2007: Revision : 5.3 ... If the referenced file is in a new location, clear ...

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 ...

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 SubJared

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

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 ...

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.

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.Is there an alternative way to accomplish this? Thanks.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

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 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

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.