Free Microsoft Excel 2013 Quick Reference

- How to refer to the value of a cell containing a VBA function?
- Example using DSUM worksheet function in a VBA function
- Using VBA Functions in a worksheet
- VBA function : How to search a string in another string?
- VBA Function that mimics the Access Not Matching Query
- Updating cells with VBA functions having a Range argument
- VBA Function to copy Row Array to destination Cell
- VBA Function that ignores dates in a Holiday Table
- Call a VBA function in an IF Statement
- VBA Function to replace an excel formula
- Custom VBA Function for Geometric mean
- Same VBA functions in different spreadsheets
- VBA Function Procedures Overreact to new entries
- Excel VBA Function: Rolling Period Compound Return
- Assign value to range in VBA function
- What VBA function to use for?
- VBA Functions - passing cell references into the VBA code
- Execute a vba function from a worksheet
- VBA Function Procedure for inverse sine
- VBA function with changing variable count

a cell of another worksheet. I haven't been able to figure out how to do

that.

Bob

WorksheetFunction.DSum(rDB, rColumn, rCriteria) in a VBA function?

TryString = Application.WorksheetFunction.Roman(i)

In the worksheet I can access worksheet functions and any UDFs I define.

How can I access VBA functions, like DIR(), in the worksheet?

--

Gary''s Student

I am a beginner in VBA, and I need a function that return 1 or 0

depending if my string contains one of the US states.

To explain a bit more :

If my cell = "East Syracuse, NY"

I want to return 1 because there is a US state in the string.

If my cell = Karlsruhe, Germany

I want to return 0

I have a list of all the US states (2 letter code).

For the function, I don't mind if it is a big "if then" with all the

50 conditions (number of US states).

I am aware of the FIND function in Excel, which does what I need, but

the formula is too big (I need to compare with the 50 US states), it's

why I need a macro. And FIND is not a vba function...

One more thing : it has to be case sensitive.

Any help would be appreciated.

Thanks in advance,

Yann

I've a spreadsheet in Excel and I have some data.

I want to be able to extract the data from set1 that are not in set2. In

Access, that will be a nonmatching query. However, in Excel, how do I do it

with VBA function? Any written functions already out there?

Thanks.

Herbert

when updating cells in a sheet. Here is the essence of the problem.

Suppose you have three functions fnA fnB and fnC (see below), each takes

a Range argument and returns a String "A", "B", and "C" respectively.

Suppose you put fnA(A1) in A2, fnB(A2) in A3 and fnC(A3) in A4

and then type in something to A1. The functions are called but the order

is unpredictable. It seems to depend on the order I typed things in.

Moreover, if I then rearrange the links, you do not get the order of

updates that you would expect from the order of linkage.

Here is some code to show this. You use the VBA Debug window to see the

execution order.

'*******************************************************************

Private Function fnA(r As Range) As String

Debug.Print "exec: a"

fnA = "A"

End Function

'*******************************************************************

Private Function fnB(r As Range) As String

Debug.Print "exec: b"

fnB = "B"

End Function

'*******************************************************************

Private Function fnC(r As Range) As String

Debug.Print "exec: c"

fnC = "C"

End Function

Now, the interesting thing is that if you modify these functions

so that the argument is a type like double or String, then Excel

finds the correct order. For example these functions will always update

correctly:

'*******************************************************************

Private Function fnA(r As Double) As Double

Debug.Print "exec: a"

fnA = 1

End Function

'*******************************************************************

Private Function fnB(r As Double) As Double

Debug.Print "exec: b"

fnB = 2

End Function

'*******************************************************************

Private Function fnC(r As Double) As Double

Debug.Print "exec: c"

fnC = 3

End Function

My questions are:

What are the rules for Excel updating when VDA functions have Range

parameters (and why are these rules not the same as when the arguments

are Double or String)?

Could I rewrite my first set of VBA functions so that they will update

correctly, given that I must use the same Range argument in the function

calls and return a String that doesn't change?

Oh, I have an old version of Excel (Office 97), but I don't think this

is part of the problem (running under XP).

I need a VBA function to copy the Row Array and paste it from given cell at offset value

Suppose

SourceArray= Range("A1:G1")

DestCell = Range("D4")

OffSetValue= Range("A3")

Need a Function like

CopyArray(SourceArray,DestCell,OffsetValue)

So the Source Array should get printed from Dest Cell at offset value which is 3 ( as the value varies the array should move)

----------------------------------------------------------

X A B C D E F G

----------------------------------------------------------

1 2 4 6 0 0 0 0

2 0 0 0 0 0 0 0

3 3 0 0 0 0 0 0

4 0 0 0 0 0 0 0

5 0 0 0 2 4 6 0

----------------------------------------------------------

for the previous day and not have to change to Date()-3 each Monday and

then back to Date()-1 after running all my Monday reports. It works

great, both with a Holidays table or without it. The code also works as

an Excel VBA function to call a date that does not include Saturdays

and Sundays. However, I cannot make it call the holiday table in

Access. Is there a way to do this? Or would it be simpler to somehow

name a table in Excel (on a separate spreadsheet) and somehow call that

range to make the function ignore an additional day; if the previous

day turns out to be a holiday? Anybody know?

Public Function AdjWorkDays(dteStart As Date, _

intNumDays As Long, _

Optional blnAdd As Boolean = True) As Date

AdjWorkDays = dteStart

Do While intNumDays > 0

If blnAdd Then

'-- Adding WorkDays

AdjWorkDays = AdjWorkDays + 1

Else

'-- Subtracting WorkDays

AdjWorkDays = AdjWorkDays - 1

End If

If Weekday(AdjWorkDays, vbMonday) <= 5 Then

'-- Use the following code if you have a "Holiday" table

' If Weekday(dteCurrDate, vbMonday) <= 5 And

IsNull(DLookup("[Holiday]", "tblHolidays", "[HolDate] = #" &

dteCurrDate & "#")) Then

intNumDays = intNumDays - 1

End If

Loop

End Function

Does anyone know, how I might call an VBA function from an Excel Worksheet =IF function?

I'm attempting the following.... If J7 = matches a particular cell on the Config sheet, I want the VBA function Username to run. I think the IF statement is sort of correct, however I'm not sure how/or whether I can run the VBA function from this particular formula.

=IF(J7=Config!C6:C7,"=username()")

Thanks in advance.

I am struggling to convert the following excel formula to a VBA function -

The formula refers to the same cell 7 times, so I wanted this to simplify it and replace with a VBA function. Basically the formula works on a string to find whether it has a space or hyphen. If the string has neither a space or hyphen then the function should return the string unchanged. If the string has either a space or hyphen, the characters left-side to the first space or hyphen should be returned. That's it!

The excel formula above does this correctly. I just want this to convert to a VBA Custom Function. The attached excel file has a couple of failed attempts to get the function to work.

Appreciate your help.

Rakesh

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

I noticed that if I open this week's and last week's files in the same excel program I get odd behavior. I call a the function I defined price() in a worksheet, when I open another excel file the already opened excel file will update all the price() function calls with the information from the 2nd file!! Likewise when I hit F2 and enter to "refresh" the cells in the first worksheet, they update to their correct values, but the values in the 2nd spreadsheet now become those from the first!

Any pointers? Many thanks!

I have a problem with the overreaction of my VBA function procedures. I appreciate if anyone could give me any hints.

Basically, I have an input section with 1000 rows and 10 columns and an output section with 1000 rows and 40 columns. Each cell in the output section is with fomula:

where C_A is a function I defined.

The issue is whenever I try to input a new entry, the VBA will do calculations on all cells which take quite a long time. Is there any way to control the program so that new calculation is called upon only on the new input instead of the old ones?

For example, if I input new entries on Row 5, I would like VBA only calculates on this new row, instread of from row 1 to row 5.

Thanks a lot guys. One of the solutions is simply turn off the auto calculation in Options then press F9 whenever you want to calculate. Link

I am trying to create a formula that can measure the compound return for a period ending in a certain date (dates are in column A). For example, (using a formula created in VBA) the compound return (ie (1+monthly return 1) * (1 + monthly return 2) …. * (1+ monthly return 12). So, the inputs would be range (C2:C98) and ending date (in this case 12/31/08, which is in column A86) and number of months (in this case 12). So it might look like: =rollingreturn(range,date,months,0) or =rollingreturn(C$2:C$98,39813,12,0) where the data range is c2:c98, the date is 39813, 12 refers to the previous 12 months (the cell in column C in the row corresponding to the date and the previous 11 rows) and 0 / 1 (binary reference to tell the formula whether to return blank or return a calculation if all of the numbers are not there (ie if some of the 12 cells in this case are blank cells). So in this case, the answer is -23.70% as the monthly performance that it refers to in column C is: -2.40%, 1.10%, 0.30%, 3.40%, 0.80%, -1.70%, -1.80%, -1.50%, -4.70%, -8.70%, -13.20%, 3.00%. Note that the math is that each of the months of performance must be added to 1 and then all multiplied (and then subtract one at the end) in order to get the compound return (ie in this case: = (1+C75) * (1+C76) *(1+C77) * (1+C78) * (1+C79) * (1+C80) * (1+C81) * (1+C82) * (1+C83) * (1+C84) * (1+C85) * (1+C86) -1.

As part of this request, I also need (this part should be REALLY simple) functions to tell the date where the data starts and ends. In column C, that would be =enddate(c$2:c$98) which would return 39964 (May 31, 2009, the date corresponding to the last non-blank cell in the range) and a similar function begindate(c$2:c$98) which would return 39325 (August 31, 2007, the date corresponding to the first non-blank cell in the range) .

My data looks like this: Column A: range of monthly dates (ie. A2: 12/31/2001, A3: 1/31/2002…A98: 12/31/2009). Column B: thru Column O: monthly percentage returns (performance for funds that I am analyzing). The fund names are in row 1 (ie B1, C1 etc.). However, the inception date for each fund differs. So, for example, the fund in column C started in August 2007 and I have data through May 2009. So, cells C2:C69 are blank as are rows C92:C98. Rows C70:C91 have monthly % returns in each row (ie: 1.50%, 1.10%, 0.70%, -0.80%, 1.00%, -2.40%, 1.10%, 0.30%, 3.40%, 0.80%, -1.70%, -1.80%, -1.50%, -4.70%, -8.70%, -13.20%, 3.00%, -1.20%, 0.85%, -0.10%, 2.90%, 2.90%). As mentioned previously, I can send the spreadsheet if it helps.

Dim wsPurchase As Worksheet

Set wsPurchase = Worksheets("Purchase")

Dim rCriteria As Range

Set rCriteria = wsPurchase.Range("A1:A2")

rCriteria.Offset(1, 0).Value = sSet

Is there a way to change a worksheet cell from within a VBA function?

I have sells which contains states codes, such as NY, CA, IA, etc., and

would like to find those cells containing these codes. I was wondering if

there is a VBA function to test whether the text in a given cell contains,

let say "NY". I just could not find it.

Thanks.

For example if i create a VBA function, with an argument X, in the code for that function can I determine what the cell location is for X? I've been looking at the online help at the byval and byref options for function arguments, but I don't think they do what I need.

As an (overly simplified) example of what I might want to do with this, I'd like a function that has one variable, that adds the value in the cell passed to it to the cell above. I could do this by passing the two values to the function, but I'd like to be able to do it by passing just the lower cell reference, and it working out which the cell is above.

Pete.

I try to run a vba function (myfunction (txt)) from a worksheet located in a workbook (my workbook.xlsm) different from the one where I have stored the function.

The workbook name contains a space. I tried using the same convention used to call a cell from that workbook, i.e. =[my workbook]!myfunction(A1) without any success.

Any idea if there is a simple way to do this or if it is just impossible to use spaces in such case ?

Thanks,

n.b. if I remove the space from the filename to myworkbook, then the line myworkbook!myfunction(A1) works perfectly fine, hence no problem with the function itself.

Brandon

for example if the input was 3 then the formula will use 3 variables