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

Free Microsoft Excel 2013 Quick Reference

setting DSUM criteria in a VBA function

How is the criteria changed?
This cause an error:
Workbooks("Tracker.xls").Sheets("Purchase").Range("H6").Value = 1

Function getSetTotal(sSet As String) As Currency
' Return the total purchases for sSet
'
Dim wsPurchase As Worksheet
Dim rDB As Range ' Database
Dim rColumn ' Database column to sum the amount for sSet
Dim rCriteria As Range
Dim total As Currency

Set wsPurchase = Worksheets("Purchase")
Set rDB = wsPurchase.Range("dbPURCHASE")
rColumn = "Amount"
Set rCriteria = wsPurchase.Range("H5:H6")
'Workbooks("Tracker.xls").Sheets("Purchase").Range("H6").Value = 1

total = Application.WorksheetFunction.DSum(rDB, rColumn, rCriteria)
'MsgBox "total: " & total
getSetTotal = total
End Function


Post your answer or comment

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

How do I set two sets of criteria in a countif function? I want to count if BZ143:BZ200 equals BZ142 AND BY143:BY200 equals BY142.

I am trying to use a named variable for the criteria in a sumif function.
=sumif(A1:A4,"currentjobno",B1:B4)
currentjobno has a value of 38006

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

Hi,

I got some kind of a standard problem but could not find any solution for it up to now. Here the setting: I want to program a VBA function (not a macro!). The function should optimize some expression depending on its input parameters (a, b, and c) by using the Solver (e.g. I want to call Solver for getting a solution rather than programing some iterations myself). E.g. something like the following will be required:

Function SolverInVBA(a, b, c)
x = 0 'some dummy as starting point for the optimization
y = a * x^2 + b * x + c 'just some stupid example
SolverReset
-----------------Here comes the problem-----------------
SolverOk SetCell:="y"(???), MaxMinVal:=2, ByChange:="x"(???)
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1
SolverInVBA = "Solution of Solver"(???)
---------------------------------------------------------
End Function

The points where I have problems are marked with "(???)". In addition, how do I tell the function to update once one of its input parameters has changed? Has anybody an idea how to implement this?

Best,
Felix

I got a problem using Cell range as a variable in a VBA function

Code shows like this:

-------------------------------------------------------------------------------
Function AssignData(myRange As Range) As Double()
Dim ColNums As Integer, RowNums As Integer
ColNums = 1
RowNums = 1
If IsArray(myRange.Cells.Value) Then
ColNums = myRange.Columns.Count
RowNums = myRange.Rows.Count
End If

Dim tmp() As Double
ReDim tmp(1 To RowNums, 1 To ColNums)
Dim ColIdx As Integer, RowIdx As Integer
For RowIdx = 1 To RowNums
For ColIdx = 1 To ColNums
tmp(RowIdx, ColIdx) = myRange.Cells(RowIdx, ColIdx).Value
tmp(RowIdx, ColIdx) = myRange.Cells(RowIdx, ColIdx).Value
Next ColIdx
Next RowIdx
AssignData = tmp
End Function
-------------------------------------------------------------------------------

If the cells contain pure numerical numbers in selected cell range, it works fine. Problems come out when the data in selected cell range are obtained from formulas: Data won't assign to the array correctly during debugging.

It is really funny thing. If you use another subroutine to extract one element from this array and show it in a cell, it always give you the correct data. But if you watch the variable during debugging, it may give you zero for the same element.

for example, I want to store the cell range "B1:D25" in an array variable MyArray. If you output the element (20,3) in this array, you get it correctly. However, during debugging, the corresponding data may not assign to MyArray(20,3) when you watch it.

So I don't know what the problem is. Please help how i can pass the values in the cell range exactly to the array variable. Thanks

I am trying to use the sumif statement in excel and want to set the criteria
to a "less than" date. The only way I can get the formula to work is by
hardcoding the date into the sumif statement i.e
=sumif(A1:II1,"

I am trying to use the sumif statement in excel and want to set the criteria
to a "less than" date. The only way I can get the formula to work is by
hardcoding the date into the sumif statement i.e
=sumif(A1:II1,"<31-OCT-05",A2:II2). However I want to copy this formula using
a number of different dates and do not want to individually hard code the
date. I want to replace the hard coded date with a cell reference that
contains the date. When I do this I get the microsoft excel pop up box
telling me there is an error in my formula.

I am running Excel 2007 and am just starting to learn and get into using VBA or macros. I am currently working on creating my own function and want the formatting of the result to be formatted in a certain way. While I can get the formatting to work, I can't get excel to recognize the solution as a number it will add with other 'normal' numbers. I figure that I am missing something in my code since I'm new at VBA. I tried "numberformat" instead of "format" but the vba editor tells me the sub or function is not defined.

Can anyone point out what I've missed? My code is below.

Function DNF(Fleet, RaceCompetitors, Penalty)
Dim Fleet_Count
Dim Race_Count

' Count Number In Fleet
Fleet_Count = WorksheetFunction.CountA(Fleet)

' Count Number DNS
Race_Count = WorksheetFunction.CountIf(RaceCompetitor… "DNS")

' Determine DNF Score and Label

DNF = Format(((Fleet_Count - Race_Count) + Penalty), "percent")

End Function

Hi,

I am not able to set the criteria in a autofilter to be dynamic (i.e as the user is selecting it.) in VBA. The code is as below:
Sub filt2()
'
' filt2 Macro
' Macro recorded 4/23/2007 by Abhijit
'

'
    Dim engcode As Range, product As Range, quotetype As Range
    Set engcode = ThisWorkbook.Worksheets("a").Range("a1")
    Set product = ThisWorkbook.Worksheets("a").Range("a2")
    Set quotetype = ThisWorkbook.Worksheets("a").Range("a3")
    Sheets("Raw").Select
    Selection.AutoFilter
    Selection.AutoFilter
    Selection.AutoFilter Field:=13, Criteria1:=engcode.Value
    Selection.AutoFilter Field:=2, Criteria1:=product.Value
    Selection.AutoFilter Field:=4, Criteria1:=quotetype.Value
    Selection.AutoFilter Field:=8, Criteria1:="Jan"
    Sheets("Raw").Select
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUBTOTAL(102,R[1]C:RC)"
    Range("A1").Select
    Selection.Copy
    Sheets("a").Select
    Range("F14").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.ClearContents
    Sheets("Raw").Select
    Selection.Copy
    Sheets("a").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub
Any suggestions.

Please help

Abhi

Hi everyone,

I have been struggling for over a week now, trying to make my DLL call a
function written in VBA (version 6) when parameters are involved.

I am able to call my DLL functions from VBA without any problems. The DLL
can even call a VBA procedure (sub or function) without parameters
successfully (using the callback technique) but as soon as parameters are
passed with the call, an exception is raised in the VBA host application which
makes it crash.

I have done my homework by reading the available help files on the
subject and searching on the web without success.

Here the project I have built to demonstrate my problem:

My TestDLL.dll has been developed with Microsoft Visual C++ .NET

(1) Here is the content of DllTest.cpp:

#include "stdafx.h"

#define EXTERNC extern "C"
#define DLLAPI __declspec(dllexport)
#define WINAPI __stdcall

typedef long (CALLBACK *PCB1) (char cVal);

EXTERNC DLLAPI DWORD WINAPI DllCBInit (PCB1 pCallback);
EXTERNC DLLAPI void WINAPI DllCBTest1(char cVal);

static PCB1 theCallback = NULL;

// Defines the entry point for the DLL application.
BOOL APIENTRY DllMain(HANDLE hModule,
DWORD ul_reason_for_call,
LPVOID lpReserved)
{
return TRUE;
}

// Exported functions of the DLL
EXTERNC DLLAPI DWORD WINAPI DllCBInit(PCB1 pCallback)
{
theCallback = pCallback;
return (DWORD)theCallback;
}

EXTERNC DLLAPI void WINAPI DllCBTest1(char cVal)
{
(*theCallback)(cVal);
}

(2) Due to C++ name mangling, I have defines the function names in the
DLLTest.def file as follows:

LIBRARY DllTest

EXPORTS
DllCBInit
DllCBTest1

(3) I compiled the project DllTest with the Calling Convention __stdcall (/Gz)
as suggested everywere in the documentation. Then I copied the DllTest.dll
file
in c:windowssystem32

(4) In a VBA script (written under Excel 2003 for convenience purposes) I have
created a Main module with the following code:

' Force explicit variable definition
Option Explicit

Public theByte As Byte

Public Declare Function DllCBInit Lib "DllTest" (ByVal pCallback As
Long) As Long

Public Declare Sub DllCBTest1 Lib "DllTest" (ByVal cVal As Byte)

Public Sub Callback1(cVal As Byte)
On Error Resume Next 'to prevent error being propagated back to the caller
theByte = cVal
End Sub

Public Sub TestDLLCallback()
Dim lStatus As Long
'Initialize the callback
lStatus = DllCBInit(AddressOf Callback1)
Debug.Print lStatus
'Test the Dll callback functionality
Call DllCBTest1(128)
Debug.Print theByte
End Sub

(5) Here when I step through the TestDLLCallback() procedure, the first debug
print displayed 59111236 (0x0385f744) as the address of the VBA callback
procedure Callback1.

(6) Then if I step through the Call DllCBTest1 the cursor goes to the
Callback1() procedure as expected. The next step causes the application to
crash.

(7) I used the Visual Studio.NET IDE to attach to the EXCEL application and
set a breakpoint in the DLL at the DllCBTest1 function entry point. I stepped
through the assembly code to finally realize that the application crashes
when trying to access the content of an invalid memory location:

651FAF5E 8B 00 mov eax,dword ptr [eax] ---> where eax = 0xCCCCCC80

The content of eax seems to be the actual value of the passed parameter (128)
stored as one byte in the register eax and pushed onto stack. For some unknown
reason, this value comes back from the stack to haunt us. The assembly code
for DllCBTest1 is as follows:

EXTERNC DLLAPI void WINAPI DllCBTest1(char cVal)
{
05741FF0 55 push ebp
05741FF1 8B EC mov ebp,esp
05741FF3 81 EC C0 00 00 00 sub esp,0C0h
05741FF9 53 push ebx
05741FFA 56 push esi
05741FFB 57 push edi
05741FFC 8D BD 40 FF FF FF lea edi,[ebp-0C0h]
05742002 B9 30 00 00 00 mov ecx,30h
05742007 B8 CC CC CC CC mov eax,0CCCCCCCCh
0574200C F3 AB rep stos dword ptr [edi]
(*theCallback)(cVal);
0574200E 8B F4 mov esi,esp
05742010 8A 45 08 mov al,byte ptr [cVal]
05742013 50 push eax ---> 0xCCCCCC80
05742014 FF 15 40 6B 76 05 call dword ptr [theCallback (5766B40h)]
0574201A 3B F4 cmp esi,esp
0574201C E8 61 F5 FF FF call @ILT+1405(__RTC_CheckEsp) (5741582h)
}
05742021 5F pop edi
05742022 5E pop esi
05742023 5B pop ebx
05742024 81 C4 C0 00 00 00 add esp,0C0h
0574202A 3B EC cmp ebp,esp
0574202C E8 51 F5 FF FF call @ILT+1405(__RTC_CheckEsp) (5741582h)
05742031 8B E5 mov esp,ebp
05742033 5D pop ebp
05742034 C2 04 00 ret 4

We can see that the parameter cVal is saved as a byte in al (byte portion of
eax).

From what I have read on the subject, there seems to be a calling convention
clash. Since I only have control in my DllTest library, I even tried to
recompile it with either __cdecl and __fastcall. I obtain same results.

The only explanation must be something silly I just can seem to figure out.

I hope that someone would be able to see what my problem is and provide
me with the solution.

I have to appologize for the lenghty description but I assumed that
the more you have to work with the easier it will be to find a solution.

I am running under WinXP Pro 2002 with service pack 1.

Thanks in advance,
Michel

Hi,

I'm trying to figure out how to use a set of data as criteria in a sumif formula.

Say I have 2 worksheets, Sheet1 and Sheet2.

Sheet1 has the range I want to sum (A:A) and sumrange (E:E)

Sheet 2 has large number of unique IDs (which would correspond to the range in Sheet1). I thought I would be able to do a sumif(A:A,[Sheet2]A1:Z24,E:E) and it would sum any of the numbers in my range that appear in [Sheet2]A1:Z24, but I'm getting a 0.

I also tried using an array {=SUM((A1:A1000=[Sheet2]A1:Z24)*(E1:E1000))} and I'm getting N/A for that.

Any ideas on how to make this work? I'm not familiar at all with VB and would need to do it strictly through formula.

Thanks in advance for the help

-Justin

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?

I'm aware of the SUMIF function that sums up a range based on one criteria. But I'm wondering if there's a function that does the same thing but allows multiple criteria in a SINGLE column.

The SUMIFS function only applies if you have multiple criteria spread across multiple columns *not* in the same single column.

So, any ideas on this? Thanks in advance.

Hey all.
Once again, a weird query.
How can I use the "FIND" function in a vba procedure?
What i want is:
I have a variable that comes up with email address I have in a sheet.
I´d like to use these email addresses to retrieve the string after the "@" character.
I came up with the formula mid(variable, find(variable, "@"), 100) and it works fine in the spreadsheet but wont work if writen in the code.
Does anyone knows how to code this function properly?
thanks.

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.

Hi Everyone,

I am working on a filtering algorithm for timestamped data. I have two variants, CellLocationPointer(i) and CellLocationExit1(i), which have the addresses for beginning cell and last cell of the range. I also have the row and column data saved in myrow(i), mycolumn(i), myrow1(i), and mycol1(i) for the cells. I am trying to use this info in setting the ranges for a LinEst function. I want to place them into something similiar to this: Selection.FormulaArray" =LINEST(R[-1]C[-2]:R[1]C[-2],R[-1]C[-3]:RC[-3],R[-1]C[-5],R[-1]C[-6])"

I have tried everything from
"=LINEST(" & Range(Cells(myrow1(i), mycol1(i)), Cells(myrow(i), mycol(i))).Offset(0, 1) & "," & Range(Cells(myrow1(i), mycol1(i)), Cells(myrow(i), mycol(i) + 1)).Offset(0, 2).Select & ",TRUE,TRUE)"
and
ActiceShee.ActiveCell.Value = Range(Cells(myrow1(i), mycol1(i)), Cells(myrow(i), mycol(i))).Offset(0, 1).Select
but I can't anything to work. I think that I need to take that column and row data and somehow translate into a string for the LinEst function.

Hi there

I am trying to learn how to loop so am starting with a simple example (see attached), hopefully to apply to a larger spreadsheet. I would like to loop through a named range called "Buildings" and use each result as criteria in a filter on another sheet within the workbook.

E.g. The named range has values B1, B2 and B3. I would like to use each of the values in the range to filter the data on the sheet called "data". Each result will be copied and pasted to another workbook and called B1.xls, B2.xls etc. In this case I will have 3 new workbooks with the information for each of the Buildings.

I would like to do this all in VBA.

If somebody can help me I would be grateful.

Thanks

I am trying to do a custom lookup function where the range remains constant
a1:b365 and I am trying to lookup a long list where some references are blank
and do not want #NA throughout the document so I tried:

Function Lookupdate(datehere)
'
If datehere = 0 Then
Lookupdate = ""
Else
If datehere 0 Then {is not equal to?}
Lookupdate = Application.Lookup(datehere, Sheet2!Range(A1:b366))

End If
End If
End Function

where datehere is a date on a calendar and I am trying to lookup that date
in column a on sheet 2 and return column b (importance of date - holiday,
birthday, appointment,etc)from sheet 2.
lookup(e4,sheet2!a1:b366) works in excel but when trying to do this in a
custom function with vba I cannot get it to work.
I need someway to do this for a long list with out getting #N/A and also
when I try to autofill the lookup function in excel it changes the range also.

Hi Everybody,

I am trying to define a function that calculates a forward price, this should be an iterative process and unfortunately I am not able to properly define the required loop.

The function shown below performs the required calculation but it should be repeated until the LOAN_AMOUNT + FPA_COST equals the SETTLEMENT_AMOUNT. I have made the calculation manually in Excel and the price is normally found in 3 to 5 steps. (see enclosed spreadsheet)

Would anyone be able to define the required loop?

Many thanks in advance!,

Paul

---------------------------------
Function FPA_price(fraction, loan_amount, spot_price, coupon, risk_free_rate, commision)

Dim bond_amount As Double
Dim fpa_cost As Double
Dim settlement_amount As Double

bond_amount = Round(loan_amount / (spot_price / 100), 0)
fpa_cost = (bond_amount * fraction * coupon / 4) - (loan_amount * fraction * risk_free_rate / 4) + commision
settlement_amount = loan_amount - fpa_cost

FPA_price = Round(settlement_amount / bond_amount * 100, 6)

End Function


I hardly ever use VLOOKUP anymore in favour of INDEX(MATCH) but I can never get the order of all the arguments right!

So I want to create a VBA function that combines the two of them - a kind of SuperVLOOKUP that lets you search leftwards and/or start from beyond the first column, with the following arguments:
Source data rangeIndexColumn within source data range containing index valueColumn within source data range containing return valueBut I haven't got the foggiest idea how to start - in particular how to pass arguments that could be values, or ranges, or whatever, and values that could be in any format.

Can anyone suggest a starting point - or even better, share a function they've already written to do the job?

Thank you

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

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.

Hi,
I am trying to write some code for a command button that will clear the data out of several (16)
different ranges. The ranges are not contiguous. If I have more than 2 sets of ranges in my code I get a
run-time error 450. Wrong number of arguments or invalid property assignment. How many arguments are allowed?
My code is below without all of the ranges that I need to have cleared.

Private Sub Button13_Click()
Dim resp

resp = MsgBox("Are you sure?", vbOKCancel)
If resp = vbOK Then
    ActiveSheet.Range("C8:K10", "C14:K16", "C27:K29").ClearContents
End If
End Sub



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