Free Microsoft Excel 2013 Quick Reference

Vba function Results

I have a VBA function in a cell of one worksheet and want to use the value in
a cell of another worksheet. I haven't been able to figure out how to do
that.

Bob

Does anyone have sample code of using
WorksheetFunction.DSum(rDB, rColumn, rCriteria) in a VBA function?

Within VBA I can access worksheet functions, like ROMAN, as follows:

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

Hi all,
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

Hello,

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

I have a problem with the order in which Excel calls VBA functions
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).

Hi,

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

I am currently using the code below in an Access Module to query data
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

Hello,

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.

Hi Everyone

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

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


I have a spreadsheet that's updated every week. The name changes, as does the data, but the underlying structure, worksheets, VBA functions all remain the same.

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!

Hi All,

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

Can anyone help me write a VBA function in excel (as I think it does not exist in excel’s built in formulas) to measure compound returns for a specified period within a data range (I can send the spreadsheet as an example) and related function for the beginning and end dates of the data for the particular range.

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.

This code works in a VBA Sub but not in a VBA function
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?

Hi,

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.

Is it possible to pass a cell reference into a subroutine/function?
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.

Hi,

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.

I am a little lost with this function procedure that sounds pretty straightforward, but otherwise i am clueless. I need to create a VBA function procedure for the inverse sine of a function that unlike excel, actually converts it to degrees. It also needs to be able to check to determine if the function argument provided by the user is a legitimate value for the inverse sine function. If it is not, then the output of the function (NOT a message box) should be a statement informing the user of the problem.

Brandon

i am not quite sure as to how i would get a VBA function user defined input to alter the amount of varibale with a formula.

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