Free Microsoft Excel 2013 Quick Reference

Call workbook function

I am using Excel 2000.
I have created a command button on a worksheet. On clicking this button, I
want a procedure in ThisWorkbook to run. What is run with the following ?

Private Sub BtnRetrieve()


Post your answer or comment

comments powered by Disqus
the purpose is to call a function (name is repeat) from another workbook wth path('D:EXcel VBApracticetrial.xls'). I find the following is successful.

Private Sub CommandButton1_Click()

Workbooks.Open("D:EXcel VBApracticetrial.xls")
Application.Run "'D:EXcel VBApracticetrial.xls'!repeat"

End Sub

However, When I try to use a string "file" to replace the long name 'D:EXcel VBApracticetrial.xls' It fails as shown below. error is with the last statement Application.Run 'file!repeat'
Help needed. thanks!!!

Private Sub CommandButton1_Click()

Dim wb As Workbook
Dim file, As String

file = "D:EXcel VBApracticetrial.xls"
Workbooks.Open file
Application.Run 'file!repeat'

End Sub

Dear sir,
how can i call a function or procedure that contained in another workbook ?

thanks
Joe

Hi Lenny,

Here's an example of how this works:

-------------
In Book1.xls
-------------
Public Function MyFunc(ByVal lArg As Long, ByVal szArg As String) As String
MyFunc = "Arguments Passed:" & vbLf & _
"lArg = " & CStr(lArg) & vbLf & _
"szArg = " & szArg
End Function

----------------
In VB6 Project
----------------
''' You must set a reference to the Excel object library to run this.
Sub Main()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim szResult As String
Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Open("E:Book1.xls")
szResult = xlApp.Run("Book1.xls!MyFunc", 10, "Some String")
MsgBox szResult
xlBook.Close False
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing
End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

"Leonard Jonas" > wrote in message
...
> I have found a lot of literature online about calling excel 2000 macros
> programmatically from VB6xlApp.Run "Personal.xls!MyMacro", Arg1, Arg2)
> but I haven't been able to find any information about calling FUNCTION
> MACROS which return a value. The above syntax (result =
> xlApp.Run"Personal.xls!MyFunctionMacro", Arg1, Arg2) doesn't work when I
> want to call a function macro and have a value returned to VB.
>
> I also tried to get around this (with no luck) by passing arguments
> ByRef to an Excel macro and updating them within the macro. This works
> when the sub is called from within VBA, but if I'm in VB, the variable
> does not reflect the changes that take place in VBA.
> For example:
> (In Excel -- Personal.xls)
> Sub ChangeX(x ByRef as integer)
> x = 50
> End Sub
> (In VB)
> Sub MyProgram()
> dim x as integer
> x = 0
> xlApp.Run "Personal.xls!ChangeX" x
> MsgBox x 'here x is still 0
> End Sub
>
> If you know how to make either of these techniques work, please let me
> know
>
> Thanks!
> Lenny
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

I have an add-in, which provides certain user defined functions to xl.

However, an argument of some of these functions is a text string
representing the name of a defined range in the "calling"
workbook/worksheet", which does not get resolved correctly on recalcs of
in-active workbooks/sheets. For this reason, it would be useful to be able
to get information (in vba) on from which workbook and worksheet a function
call is beeing made. Is this possible and if so how??? Bwetter programming
(passing the range) could avoid this problem, but we're stuck with what we
have a need the fix to be backwards compatible...

Is there a way to use solver for VBA functions? I am trying to optimize a function that is not contained in the Excel workbook. Only the input and output values to the function are stored in the worksheet. The rest takes place in VBA, and it can take 1-5 minutes to solve. I would like solver to call this function as it optimizes the output.

i.e solver reads in 4 input values from worksheet, calls reduce_model(), reduce_model() prints output to worksheet, solver evaluates whether it is getting closer or further from optimum, adjusts 4 input values, calls reduce_model(). etc.

When calling a function created in a different workbook, I get the #NAME! not
defined even thought I put down the spreadsheet name.
"=anotherbook!myfunction(A1)"
Am I doing something wrong?

Thanks

Hi,
I would like to make a custom function that is the equivalent to the offset function but with a reference range which is the equivalent to "This Cell" I know this could be done with Offset but it would be cleaner for a complicated function if I could have offsets that are always "Anchored" at the cell that calls the function.

Thank you,
Edgar

Question: How I can directly calling VBA Functions after Worksheet start without user interactions ? (like Autoexec and /cmd function in Access/VBA)

How to call Excel functions from within Access 2000

The following two sample procedures use Microsoft Excel statistical functions. NOTE : Excel does not automatically close after you open it through Automation.

I guess I just don't understand the basics of calling a function. I have the following function in a standard module:


	VB:
	
 Worksheets) 
    ws.Range("A2:P100").ClearContents 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I have this code in a worksheet module:

	VB:
	
 Trying_to_call_a_function() 
    CleanUp (ThisWorkbook.Worksheets("Month End")) 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
But when I run the routine that calls the "CleanUp" function, I get the message: "Object doesn't support this property or method." I have tried changing the Function to this: CleanUp (ws as Worksheet) [instead of (ws as WorksheetS)]. Yet, this does not change anything.

What am I doing wrong?

Any help will be appreciated.
Jim Stewart

I searched but i hardly get any hits for 'DLL call' in ozgrid.
I know its possible.
http://www.ozgrid.com/forum/showthre...light=call+dll
This provides a good problem, but there is no solution given for this either.

Could you guys direct me to a tutorial or explain how to call a function from a particular dll?

Thanks a lot guys

Hi Everyone,

Thank you in advance for your help with this, this forum always comes through for me.

Overall I am trying to set the value of 5 cells based on some calculations. The way I have it set up right now, I'm calculating those values in a function then assigning the value of that function to those cells.

I cannot for the life of me get it to call the function properly. Any help would be really appreciated!

	VB:
	
 PatternMaker() 
     
    Dim Flow 
    Dim Total_Flow 
    Dim Month 
    Dim overflow 
    Dim Storage 
    Dim Oct_Q, May_Q, June_Q, July_Q, Aug_Q, Sep_Q 
     
     '~lots more code~
     
     'Run the pattern maker on May-Sep for the flat pattern
    Sheets("Calsim Converter").Select 
    Flat(Total_Flow, Oct_Q, Flat) 
    var = Range("MAY").Value = Flat 
     'var = Flat
    Range(Cells(iRow2 - 9, iCol + 115)).Value = Flat 'doesn't work
    Range(Cells(iRow2 - 8, iCol + 115)).Value = Flat 'doesn't work
    Range(Cells(iRow2 - 7, iCol + 115)).Value = Flat 'doesn't work
    Range(Cells(iRow2 - 6, iCol + 115)).Value = Flat 'doesn't work
     
End Sub 
Public Function Flat(Total_Flow As Double, Oct_Q As Double, Flat As Double) 
    Flat = (Total_Flow - Oct_Q) / 5 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thanks again!

I'm trying to call a function named AssignMonths. The code for this is:
Code:
Sub AssignMonths()
'takes the current month and places the name in Finanace.Range("J2") and places the next
'month name in L2 and two months name in M2 and three months name in N2
Dim CurMonth As Integer
CurMonth = Sheets("Workdays").Range("B19")
Select Case CurMonth
    Case 1
        Sheets("Finanace").Range("J2") = "Jan"
        Sheets("Finanace").Range("K2") = "Jan"
        Sheets("Finanace").Range("L2") = "Feb"
        Sheets("Finanace").Range("M2") = "March"
        Sheets("Finanace").Range("N2") = "April"
    Case 2
        Sheets("Finanace").Range("J2") = "Feb"
        Sheets("Finanace").Range("K2") = "Feb"
        Sheets("Finanace").Range("L2") = "March"
        Sheets("Finanace").Range("M2") = "April"
        Sheets("Finanace").Range("N2") = "May"
    Case 3
        Sheets("Finanace").Range("J2") = "March"
        Sheets("Finanace").Range("K2") = "March"
        Sheets("Finanace").Range("L2") = "April"
        Sheets("Finanace").Range("M2") = "May"
        Sheets("Finanace").Range("N2") = "June"
    Case 4
        Sheets("Finanace").Range("J2") = "April"
        Sheets("Finanace").Range("K2") = "April"
        Sheets("Finanace").Range("L2") = "May"
        Sheets("Finanace").Range("M2") = "June"
        Sheets("Finanace").Range("N2") = "July"
    Case 5
        Sheets("Finanace").Range("J2") = "May"
        Sheets("Finanace").Range("K2") = "May"
        Sheets("Finanace").Range("L2") = "June"
        Sheets("Finanace").Range("M2") = "July"
        Sheets("Finanace").Range("N2") = "Aug"
    Case 6
        Sheets("Finanace").Range("J2") = "June"
        Sheets("Finanace").Range("K2") = "June"
        Sheets("Finanace").Range("L2") = "July"
        Sheets("Finanace").Range("M2") = "Aug"
        Sheets("Finanace").Range("N2") = "Sept"
    Case 7
        Sheets("Finanace").Range("J2") = "July"
        Sheets("Finanace").Range("K2") = "July"
        Sheets("Finanace").Range("L2") = "Aug"
        Sheets("Finanace").Range("M2") = "Sept"
        Sheets("Finanace").Range("N2") = "Oct"
    Case 8
        Sheets("Finanace").Range("J2") = "Aug"
        Sheets("Finanace").Range("K2") = "Aug"
        Sheets("Finanace").Range("L2") = "Sept"
        Sheets("Finanace").Range("M2") = "Oct"
        Sheets("Finanace").Range("N2") = "Nov"
    Case 9
        Sheets("Finanace").Range("J2") = "Sept"
        Sheets("Finanace").Range("K2") = "Sept"
        Sheets("Finanace").Range("L2") = "Oct"
        Sheets("Finanace").Range("M2") = "Nov"
        Sheets("Finanace").Range("N2") = "Dec"
    Case 10
        Sheets("Finanace").Range("J2") = "Oct"
        Sheets("Finanace").Range("K2") = "Oct"
        Sheets("Finanace").Range("L2") = "Nov"
        Sheets("Finanace").Range("M2") = "Dec"
        Sheets("Finanace").Range("N2") = "Jan"
    Case 11
        Sheets("Finanace").Range("J2") = "Nov"
        Sheets("Finanace").Range("K2") = "Nov"
        Sheets("Finanace").Range("L2") = "Dec"
        Sheets("Finanace").Range("M2") = "Jan"
        Sheets("Finanace").Range("N2") = "Feb"
    Case 12
        Sheets("Finanace").Range("J2") = "Dec"
        Sheets("Finanace").Range("K2") = "Dec"
        Sheets("Finanace").Range("L2") = "Jan"
        Sheets("Finanace").Range("M2") = "Feb"
        Sheets("Finanace").Range("N2") = "March"
End Select
End Sub
I dont know what to put in the (), if anything. And when I call it, do I just write:
Code:
Any suggestions??

Hi,

I am having two sheets, sheet1 and sheet2.

In sheet1 i have a function which checks for a condition. If the condition is false, it exits as Exit sub.
In sheet2, I am calling the function of sheet1. But an Exit sub from sheet1, is not exiting the sheet2 also.
I want the sheet2 to exit when Exit sub happens in sheet1.

How to go about this ??

sheet1

Sub OutputFiles()
If Inputsheet.range("B12").Value > 16 Then
MsgBox " Maximum # of Farms allowed is 16", vbOKOnly
Exit Sub
End If

sheet2
OutputFiles

On calling OutputFiles from sheet2, I want the whole application to exit, but not close. The same behaviour as it would happen when executing sheet1 alone

how can i put this formula into the cells in range p9 : p48

Code:
is it a workbook function - whats happening im running a macro copying and pasting this data in the cells but the absolute
lookup range is changing smaller e.g. its currently at $f$50000 after i run the macro it'll be at something like $f$4380  
!!!! 

i dont know why but i guess if its in the workbook then the formula cannot change in these cells

THANK YOU

Hi
How do I CALL the function below.
Where do I place the code shown below
And how do I go about calling the function
Thanks

Sub FindLastCell()
Dim LastCell As Range
With ActiveSheet
Set LastCell = .Cells(.Rows.Count, "A").End(xlUp)
If IsEmpty(LastCell) Then
'do nothing
Else
Set LastCell = LastCell.Offset(1, 0)
End If
End With
MsgBox LastCell.Row
End Sub

--
Norton Virus checker 2003 says this email is clean

Hi .... i have a problem that how i can call a function in Excel VBA the
problem is.

public function price(item)

if(item = "apple") then
price =10
tax=0.2
elseif(item="orange") then
price =8
tax=0.5
end if
end function

But now how to get the exact tax number of apple which is 0.2
thanks in advance

I have made this function in my SQL-DB:

ALTER FUNCTION dbo.KSO_EXL_DKKLandAar
/* Bruges til integration med EXCEL */
(
@Land nvarchar(20),
@Aar int
)

RETURNS money AS

BEGIN

DECLARE @DKK money
SET @DKK =(
SELECT SUM(dbo.CUSTTRANS.AMOUNTCUR * dbo.CUSTTRANS.EXCHRATE
- dbo.CUSTTRANS.VATAMOUNT) AS Oms
FROM dbo.CUSTTRANS INNER JOIN
dbo.CUSTTABLE ON dbo.CUSTTRANS.ACCOUNT =
dbo.CUSTTABLE.ACCOUNT INNER JOIN
dbo.COUNTRY ON dbo.CUSTTABLE.COUNTRY
= dbo.COUNTRY.COUNTRY
WHERE (dbo.CUSTTRANS.DATASET = 'DAT')
AND (dbo.CUSTTABLE.DATASET = 'DAT')
AND (dbo.CUSTTABLE.COUNTRY = @Land)
AND (YEAR(dbo.CUSTTRANS.DATE_) = @Aar)
AND (dbo.CUSTTRANS.TRANSTYPE = 1 OR
dbo.CUSTTRANS.TRANSTYPE = 2)
AND (LEFT(RTRIM(LTRIM(dbo.CUSTTRANS.ACCOUNT)), 1) '0')

GROUP BY dbo.COUNTRY.DATASET
HAVING (dbo.COUNTRY.DATASET = 'DAT')
)
RETURN @DKK
END

And I want to call this function from Excel VBA - I tried this:

Function C5_LandAar(Land As String, Aar As Integer) As Double
Dim rs As ADODB.Recordset
Dim strSql As String
strSql = "Select dbo.KSO_EXL_DKKLandAar(" & Land & ", " & Aar & ")"
rs.Open strSql, XXXXXXXXXXXXX
If rs.EOF = False Then
C5_LandAar = rs(0)
End If
rs.Close
Set rs = Nothing
End Function

but nothing comes back to the cell.

What is wrong here ?

Hi,

I used to be able easily to call Excel functions from outside Excel, like MS
Access with a simple statement as follow:

var = Excel.Application.NormInv(....)

With newer versions of Excel, these functions were grouped in an object
called WorksheetFunction, so I assume the line to call now would be:

var = Excel.Application.WorksheetFunction.NormInv(...)

But this always return me an error 1004, saying it can't read the property
'NormInv' from object 'WorksheetFunction'.

Anybody figured that one out?

Thanks

how to call a function on control click event and put the function result on
another control. in userform.

--
Message posted via http://www.officekb.com

ACC: How to Call Excel Functions from Within Microsoft Access

The following two sample subroutines use Microsoft Excel statistical functions. NOTE: Microsoft Excel does not automatically close after it has been opened by using Automation.

how to call a function on control click event and put the function result on
another control. in userform.

--
Message posted via http://www.officekb.com

Hi!

I've got a problem. I'm using an activex combobox in excel. It display 4 posible choices, and one of them is "Other". When someone select "other" there is a textbox named "tb" that should be filled. Otherwise tb should be invisible. Combobox change() function looks like that:

Public Sub ComboBox7_Change()
If IsNull(Sheet1.tb) Then
    'nothing'
Else
    If ComboBox7.ListIndex = 0 Then
        Sheet1.tb.Visible = True
    Else
        Sheet1.tb.Visible = False
    End If
End If

End Sub
Apears when I try to close excel - there is a question if I want to save changes and when I answers "Yes" it - somehow - call combobox7_change() function. In that moment textbox "tb" doesn'y exist, so there is an error message

Compile error:

Method or data member not found
Could anyone help me, please? What I'm doing wrong?

Hi,

Does anyone know how to call and pass values to a function or sub in Matlab?
I can currently pass values directly to the workspace and execute a .m file
which references these varaibles (thus haing a similar effect) using a DDE
connection, but I can't seem to call a function as I would if I were in
Matlab itself.

I have tried the following code in VBA:

Application.DDEExecute channel, "valueSwap_vanilla(" & variable & ")"

But it doesn't work! Any suggestions...Thanks very much in advance.
Noel

How do you call a function and then return to the previous location from
which you called it.

Thanks
Clamar


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