VBA passing variables through a function

I have a macro

Sub TestMacro(X,Y)
....calculations based on X and Y values
End Sub

By putting the "X,Y" in the macro, does this make it a function.  Because 
when I try to look for the macro using the macro button on the toolbar the 
function "TestMacro" does not show up.  It shows up when I remove the X,Y.  
Do you know what has changed?  Also i this a good way to program, I want to 
be able to input values for X and Y so I thought this would be good since I 
wouldn't have to keep changing the "TestMacro". 

Sub macro1()
X = 7
Y=5
Call TestMacro(X,Y)
End sub
0
Jeff1 (635)
11/3/2005 6:38:46 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
376 Views

Similar Articles

[PageSpeed] 29

I is not in the macro list because there is no way that you can pass the
arguments to a macro called from the macro list, Excel just does not provide
it, so it doesn't list.

It isn't a function. A function use Function instead of Sub.

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"Jeff" <Jeff@discussions.microsoft.com> wrote in message
news:4D831CCB-DC47-415E-8AAB-02BDE5B6773E@microsoft.com...
> I have a macro
>
> Sub TestMacro(X,Y)
> ...calculations based on X and Y values
> End Sub
>
> By putting the "X,Y" in the macro, does this make it a function.  Because
> when I try to look for the macro using the macro button on the toolbar the
> function "TestMacro" does not show up.  It shows up when I remove the X,Y.
> Do you know what has changed?  Also i this a good way to program, I want
to
> be able to input values for X and Y so I thought this would be good since
I
> wouldn't have to keep changing the "TestMacro".
>
> Sub macro1()
> X = 7
> Y=5
> Call TestMacro(X,Y)
> End sub


0
bob.phillips1 (6510)
11/3/2005 6:46:41 PM
I used this subroutine.

Option Explicit
Sub TestMacro(X As Long, Y As Long)
    MsgBox X + Y
End Sub

In excel, I hit tools|macro|macros and I typed:

'testmacro 3,5'

And I saw a message box with 8 in it.

But I think you may want to just ask for the values...

Option Explicit
Sub TestMacro()

    Dim X As Double
    Dim Y As Double
    
    X = Application.InputBox("Please enter 1st number", Type:=1)
    Y = Application.InputBox("please enter 2nd nubmer", Type:=1)
    
    MsgBox X + Y

End Sub

You could also use the builtin Inputbox to get the values, too.

And once you decide that you don't want to go through 45 inputboxes, you may
want to make a userform to collect the information.

Debra Dalgleish has some get started instructions at:
http://contextures.com/xlUserForm01.html

Jeff wrote:
> 
> I have a macro
> 
> Sub TestMacro(X,Y)
> ...calculations based on X and Y values
> End Sub
> 
> By putting the "X,Y" in the macro, does this make it a function.  Because
> when I try to look for the macro using the macro button on the toolbar the
> function "TestMacro" does not show up.  It shows up when I remove the X,Y.
> Do you know what has changed?  Also i this a good way to program, I want to
> be able to input values for X and Y so I thought this would be good since I
> wouldn't have to keep changing the "TestMacro".
> 
> Sub macro1()
> X = 7
> Y=5
> Call TestMacro(X,Y)
> End sub

-- 

Dave Peterson
0
petersod (12005)
11/3/2005 11:23:35 PM
Reply:

Similar Artilces:

Excel VBA CPU Usage
I have a workbook that does a bunch of statistical calculations through VBA macros. Runs can take a couple of hours on my (inferior) machine at work (1.4GHz P4). When I run the same macros on my 2.8GHz P4 at home, it actually ran slower! I checked out CPU usage in Task Manager, and Excel was only using (exactly) 50% of the available CPU. The rest was System Idle. If I run another program at the same time, they each use 50%. If I run other programs by themselves, they use up to 100%. Excel on my machine at work runs at 100%. Running the macros does not use up all available syst...

Time functions #2
Hi, I have to do some time calculations and I have a formula that outputs in minutes and I have to subtract a standard time to this number of minutes. For example, I have 8:00 AM in one cell and 20.68 minutes on an other cell. The output that I am expecting is 7:39 AM. Does anybody know how to bring an output like that by subtracting the minutes? Please help. Thanks With 8:00 AM in A1 and 20.68 in B1, use this formula =A1-B1/(24*60) Why does it work? Because Excel stores time as a fraction of a day. We convert minutes to days by dividing by 24*60 (24 hr/day and 60 min/hr) You could just u...

Trapping SQL errors in VBA
ok... making progress on this relinking SQL tables routine. I was getting wierd results before apparently because I was not passing a UID; it was using my Windows logon (by default I guess) so automatically using a trusted connection. Now I've got another problem! When I intentionally use an incorrect password, I get a popup window with Connection Failed: SQL State '01S00' SQL Server Error: 0 <more lines of similar stuff> but VBA does NOT detect an error. Any idea how I can trap this error? Or is there some other way to test whether a SQL uid/pwd is valid before I start...

Adding functionality to MS Outlook Express
Hi, I am looking for help to integrate anti-spam open source programs with MS Outlook Express :- - SpamPal - http://www.spampal.org/ - Spamato - http://www.spamato.net/ The problem is to provide a library that will facilitate this. I have read the MSDN Messaging and Collaboration documentation which gives the ability to get notifications of new messages and to access message folders :- http://msdn2.microsoft.com/en-gb/library/ms709546.aspx The next need is to add a toolbar (with buttons for SPAM/NOSPAM) and to detect what messages are selected in the message window. The MSOE...

Inserted rows, now need Counta function
Hi I've got a data dump. I've figured out how to insert a blank row after a change in name in column A and insert "Total" - so... bill.... bill.... bill Total - bob.... bob.... bob.... bob Total - What I need in column C next to total is to insert the COUNTA function for each person. Any ideas? cheers You say your code inserts a blank row after a change in name and inserts "Total", but your example doesn't show this. Can you post the code you are using to do your "insert"? It will probably be easier to handle the ...

Where best to place an instruction in a VBA script
Hi All I'm using this code when I open my workbook. Private Sub Workbook_Open() Dim mysheets As Sheets Set mysheets = Worksheets(Array(1)) For Each Sheet In mysheets Sheets("ShareSheet").ScrollArea = "A1:J27" Next End Sub I'd like cell A200 to be selected and have the cursor box on it on opening the worksheet. I'm trying to place this line in the code : Application.Goto Reference:="R200C1" but can't get it to work. Can someone advise please? Grateful for any assistance. Best Wishes hi Range("A2...

If Function Possibilities
Is there a way to set up in a cell A1, something like... If the formula used in cell B1 is "here I would type the actual formula in," then display the words "works," else display "mistake" ? -- rheller ------------------------------------------------------------------------ rheller's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24081 View this thread: http://www.excelforum.com/showthread.php?threadid=377045 That's a pretty cryptic post. Do you mean: In A1: =IF(B1=n,"Works","Mistake") where 'n...

Writing Access functions
I am an old FoxPro programmer (but have been out of programming for many years) and I have been through the Access basics that I have been able to find on line. So I can create tables, forms enter data the basics. I am learning VBA no problem there. Now I want to write some functions. I would like to write one function that is passed an ID and returns the last name of the person having that ID. I would like to write this function in two ways: 1. Connect to a table in my DB find the ID and return the name 2. Have the function perform a query that returns a record set from which t...

reading file name from folder function
I am using Access 97. Just wonder if there is a fuction which can read all the file name from a folder and put it to a column in a table. Please let me know. Thanks. Hi. >I am using Access 97. Just wonder if there is a fuction which can read all >the file name from a folder and put it to a column in a table. Please see the getFileInfo( ) function in the tip, "How to compare the contents of two directories," on the following Web page for an example: http://www.access.qbuilt.com/html/vba1.html HTH. Gunny See http://www.QBuilt.com for all your database needs. See http...

Function Keys in Excel
Can someone please tell me what the F2 key does in Excel 2000? I have a column of data which has been copied from Access 2000 into an Excel 2000 sheet. It is numeric data and when I use the "Countif" function on this data, it doesn't calculate. However, if I press the "F2" function key in any of the cells, something happens to the data so that the "countif" function recoginises it. Can anyone explain what is happening here? Thank you. -- LPS F2 allows you to edit directly in the cell, do you have calculations set to automatic under tools>calcul...

Conditional Formatting VBA with formula to find string
Hi, Please can I get some help.... I need to create a conditional format VBA in Excel 2007 (because I have many conditions to include) and I don't know how to do it... The Action Required: If Product A appears in any text string in range($C$95:$C$300) then colour that cell RED, if Product B appears anywhere in a text string within range($C$95:$C$300) then colour that cell BLUE, if Product B appears anywhere in a text string within range($C$95:$C$300) then colour that cell GREEN, and so on through 41 products... Data - I have a list of about 41 Product names in range Z...

faq: type of CArray as function parameter create errors
Hi, class CMyPoint { public: long x; long y; CArray<int,int&> m_intSegmentNo; CMyPoint(); virtual ~CMyPoint(); CMyPoint(const long &xx, const long &yy); CMyPoint(const CMyPoint &cmpt); CMyPoint(const CPoint &cpt); CMyPoint& operator = (const CMyPoint &assmypt); CMyPoint& operator = (const CPoint &asspt); bool operator == (const CMyPoint &rmypt);//const CMyPoint &lmypt, bool operator != (const CMyPoint &rmypt);//const CMyPoint &lmypt, }; .... class CSegment { public: CList<CMyPoint,CMyPoint&> m_PointList; CSeg...

Function for convertion of leters doesn't work.
I'm using function LOWER to convert string i my table on small size but Access gives me a message that the function is not defined.Please, do you know another function? MS Access does not have a LOWER function. Try LCase() instead. -- Cheryl Fischer Law/Sys Associates Houston, TX "Stenli" <svet2000@seznam.cz> wrote in message news:10b201c3626e$b613a050$a301280a@phx.gbl... > I'm using function LOWER to convert string i my table on > small size but Access gives me a message that the function > is not defined.Please, do you know another function? ...

Report Writer Functions #2
Where can I find instructions and examples on the various report writer functions? I've installed the SDK but I don't see anything there. The Report Writer On-line manual (Help - Printable Manuals - Reporting - Report Writer) has a fairly good description of the functions in Chapter 6. Do you look there or is there a specific function that you did not find there for which you are searching? "Elaine" wrote: > Where can I find instructions and examples on the various report writer > functions? I've installed the SDK but I don't see anything there. Hi El...

Public Function not running
Hey, I have a public function that I call from a cell. The function is: Public Function QuitTime(strQuitTime As String) As Date Dim strHour, strMin As String Dim intHour, intMin As Integer Dim dtTime As Date strHour = Left(strQuitTime, 2) strMin = Right(strQuitTime, 2) dtTime = strHour & ":" & strMin dtTime = dtTime + Range("MainSheet!B2") ' this cell contains the value today() QuitTime = dtTime End Function Anyway, I run a vlookup on this array from another sheet and when ever i run that vlookup, it makes all the cells in th...

Question on "Value" function
In trying to use Excel to help solve a mathematical puzzle, I would like to calculate the result of applying the mathematical operator in one cell to the numbers in two other cells. Example: A1 contains 2, B1 contains +, C1 contains 3. In D1, create a formula that will give the result of 2 + 3, i.e. 5. I have tried (among many other attempts) =VALUE(CONCATENATE(A1, B1, C1)), but it gives me a #VALUE! error. What am I missing? Hi this is not possible without using VBA. try the following UDF: Public Function my_calculate(op1 As Range, operand As Range, op2 As Range) my_calculate = Ap...

A SQL statement in VBA
On the main form is a subform in datasheet view and 5 command buttons. On the click of one button I have an event procedure that should run this query : SELECT DISTINCT [Client Extended].ID_client, [Client Extended].date_ouverture, [Client Extended].nom_client, [Client Extended].date_naissance, [Client Extended].couriel FROM [Client Extended] RIGHT JOIN Produit ON [Client Extended].ID_client = Produit.REF_client WHERE ((([La première date :])<=[date_livraison]) AND ((Produit.date_livraison)<=[ La seconde date])) GROUP BY [Client Extended].ID_client, [Client Extend...

VBA in .mdb vs .adp
Hi, I just start reading about projects in Access, I have learned a lot of VBA in a .mdb application, my question is, the VBA code in a access project is the same that in a .mdb application? or I need to learn a new way of programing. Thanks Orlando <Orlando@discussions.microsoft.com> wrote: >Hi, I just start reading about projects in Access, I have learned a lot of >VBA in a .mdb application, my question is, the VBA code in a access project >is the same that in a .mdb application? or I need to learn a new way of >programing. > >Thanks The VBA programming lang...

Excel VBA
Hi, Is there any way to freeze (prevent) a number of worksheets from recalculating when a VBA macro is running? I have a spreadsheet with about 10 worksheets, most of which are linked to each other in some way. One of my macros only needs to use the formulas (and related results) from about 7 of these, so the other 3 are updating all the time but since the results from these are not needed then they are slowing down my code. Is there some VBA function for temporarily disabling a worksheets calculation function? I would need my code to look something like this: Code: -------------------- ...

Setting x-axis crosses with VBA
I have a chart that at the moment has a vertical range from -70 to -92. I'm trying to set the horizontal axis to cross at the minimum value, in this case -92. Using With ActiveChart.Axes(xlValue) .Crosses = xlMinimum It crosses at -70. If I use xlMaximum I get the same thing. Using .Crosses = xlCustom .CrossesAt = -92 it crosses at -92. however, I need it to be dynamic so the x-axis labels stay below the data field. Am I using xlMinimum in a place it cannot be used? Any help will be appreciated. In the format axis dialog, there is no way to specify tha...

Missing engineering functions
In an earlier version of Excel, I had "engineering functions" like =dec2hex and =hex2dec. Not, in Excel 2003, I see them in the "help files' but not in my list of available formulas. How do I get them to work in Excel 2003? Thanks in advance, Geoff Waters Glendale, CA Geoff, I don't have Excel 2003, but you should be able to go to Tools | Add-ins and check mark "Analysis Took Pak" in the list that appears. Regards, Jim Cone San Francisco, CA "Geoff" <grw888@hotmail.com> wrote in message news:zwfIc.299$Qu5.238@newsread2.news.pas.earthli...

Read Receipt Function in outlook
The read receipt option is no longer working in Outlook Small Business Edition for XP 2002. I have not made any recent changes to my outlook setup. Does anyone know how I can trouble shoot this? ...

Outlook today function
When I click on the customize outlook today button nothing happens. What should I do. OL2000: You Cannot Customize Outlook Today After You Install Critical Update 813489 for Internet Explorer: http://support.microsoft.com/default.aspx?scid=kb;EN-US;820575 You can edit the registry to make changes to Outlook Today. http://www.outlook-tips.net/howto/oltoday.htm -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Tips: http://ww...

apply function to multiple cells
Hi, So I want to write a function which divides every value in column A b the corresponding value in column B and I don't feel like monkey typin the formula in every cell. How can I apply this formula to all cells? I want column C to look like this. Do I need to write a loop o smthing? A1/B1 A2/B2 A3/B3 Thank -- MrXX ----------------------------------------------------------------------- MrXXX's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3124 View this thread: http://www.excelforum.com/showthread.php?threadid=50916 In cell C1 enter the formula: =A1/...

Auto delete stopped functioning in Outlook 2000
Hi, I have a single install on a user PC which although the option to auto empty the waste bin is checked it is not deleting or even asking whether she is sure she wants to delete. Advice appreciated, Sam ...