Typing in macros

I have made a few macros and use them in word and excel 
2000.  When I see typed out macros on the message boards I 
am not quite sure how to use them.  Do I go into the 
editor, type them in and then link them with a button on 
the toolbar?  What module do I put them in and is there 
one for the personal worksheet.  I have been struggling 
with the syntax of Visual Basics for years.  Just need a 
step by step explanation.

Thank You  Sue
0
anonymous (74719)
5/4/2004 6:32:47 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
663 Views

Similar Articles

[PageSpeed] 0

In general, the macros you see in the groups are likely to be ordinary macros
that will go into a general module in the VBE, eg:-

Hit ALT+F11 and this will open the VBE (Visual Basic Editor)
Top left you will hopefully see an explorer style pane.  Within this pane you
need to search for
your workbook's name, and when you find it you may need to click on the + to
expand it.  Within
that you should see the following:-

VBAProject(Your_Filename)
   Microsoft Excel Objects
        Sheet1(Sheet1)
        Sheet2(Sheet2)
        Sheet3(Sheet3)
        ThisWorkbook
   Modules
        Module1
        Module2
        etc etc (You may have just 1 or even none of these)

If you have named your sheets then those names will appear in the brackets above
as opposed to
what you see at the moment in my note.  If you have no modules then rightclick
on the VBA Project bit and choose insert module.  Then copy the routine from the
post, doubleclick the module and paste it into it.  Now just hit File / Close
and return to Microsoft Excel and save the file.

You can now run the macro using Tools / Macro / Macros - Pick the name from the
list, OR by assigning it to a button as you said.

Event macros are slightly different and will go in either ThisWorkbook module or
one of the Sheet modules:-

http://cpearson.com/excel/events.htm

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
              Sys Spec - Win XP Pro /  XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"sue t" <anonymous@discussions.microsoft.com> wrote in message
news:843201c43206$32d3a420$a101280a@phx.gbl...
> I have made a few macros and use them in word and excel
> 2000.  When I see typed out macros on the message boards I
> am not quite sure how to use them.  Do I go into the
> editor, type them in and then link them with a button on
> the toolbar?  What module do I put them in and is there
> one for the personal worksheet.  I have been struggling
> with the syntax of Visual Basics for years.  Just need a
> step by step explanation.
>
> Thank You  Sue


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.676 / Virus Database: 438 - Release Date: 03/05/2004


0
ken.wright (2489)
5/4/2004 6:58:16 PM
Hi Sue
see
http://www.mvps.org/dmcritchie/excel/getstarted.htm

-- 
Regards
Frank Kabel
Frankfurt, Germany


sue t wrote:
> I have made a few macros and use them in word and excel
> 2000.  When I see typed out macros on the message boards I
> am not quite sure how to use them.  Do I go into the
> editor, type them in and then link them with a button on
> the toolbar?  What module do I put them in and is there
> one for the personal worksheet.  I have been struggling
> with the syntax of Visual Basics for years.  Just need a
> step by step explanation.
> 
> Thank You  Sue
0
frank.kabel (11126)
5/4/2004 7:00:03 PM
Sue

The macro code you see on the message board can most times be copied and
pasted to a module.

Which type of module depends upon the code.  General code, worksheet_event
code, workbook_open code are all treated differently and are used for
different purposes.

Yes.  You can copy/paste to your Personal.xls if the code is appropriate.

Visit David McRitchie's site for info on Getting Started with VBA and Macros.

Gord Dibben Excel MVP

On Tue, 4 May 2004 11:32:47 -0700, "sue t"
<anonymous@discussions.microsoft.com> wrote:

>I have made a few macros and use them in word and excel 
>2000.  When I see typed out macros on the message boards I 
>am not quite sure how to use them.  Do I go into the 
>editor, type them in and then link them with a button on 
>the toolbar?  What module do I put them in and is there 
>one for the personal worksheet.  I have been struggling 
>with the syntax of Visual Basics for years.  Just need a 
>step by step explanation.
>
>Thank You  Sue

0
Gord
5/4/2004 7:07:30 PM
Reply:

Similar Artilces:

Pie of Pie Chart type with smaller pie showing slices of main slic
Hi How can I do the following: Lets say I have the following labels and data For the main pie I have A,B,C labels with A =5, B =10, and C =20. So my main pie would show 3 slices with C being the largest slice. To do this I highlight the 3 labels and their data and choose Insert, Pie, Pie in a Pie to show a main pie on the left with 3 slices and a smaller solid pie to the right with series lines connectiing them. I want to show the solid smaller pie on the right in several slices to show how the C slice of 20 is broken down. lets say the main slice C of 20 is made up ...

delete all macro
Hi, How can I delete all macro of my excel spreadsheet? Thanks There are instructions here for finding and removing code: http://www.contextures.com/xlfaqMac.html#NoMacros arnold wrote: > Hi, > > How can I delete all macro of my excel spreadsheet? > > Thanks > > -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

Macro to generate sequentially named sheets
Hi All, I have a workbook containing a 'fixed' sheet titled 'Lists' containing my dynamic ranged lookup tables and another sheet titled 'Sheet A'. 'Sheet A' accepts input from my users and can be the first of many sheets sequentially named, ie, 'Sheet B', 'Sheet C' etc. Due to restrictions elsewhere, my sheets cannot be named numerically sequentially. I would like to generate a macro that can identify the latest instance of my sequential sheets, copy that sheet into the same workbook and index the name, ie. copy 'Sheet F' to 'S...

Typing in macros
I have made a few macros and use them in word and excel 2000. When I see typed out macros on the message boards I am not quite sure how to use them. Do I go into the editor, type them in and then link them with a button on the toolbar? What module do I put them in and is there one for the personal worksheet. I have been struggling with the syntax of Visual Basics for years. Just need a step by step explanation. Thank You Sue In general, the macros you see in the groups are likely to be ordinary macros that will go into a general module in the VBE, eg:- Hit ALT+F11 and this will...

Open File Dialog to fileter multiple file types at once...?
Hello, Here is another quick one for you all. The solution is probably simple, but I cannot find the answer anywhere. I have the following code: CFileDialog OpenDlg(TRUE, NULL, NULL, OFN_ALLOWMULTISELECT | OFN_HIDEREADONLY, ""); // Later must set this to retrieve a value from the registry OpenDlg.m_ofn.lpstrInitialDir = "c:\\"; OpenDlg.m_ofn.lpstrFilter = "txt Files\0*.txt\0Doc Files\0*.doc\0"; OpenDlg.DoModal(); I am trying to get the box to filter out everything but the *.doc and the *.txt files ... I have seen some boxes that have something that...

Enable Macros question at Startup
I am getting the dialog box right when I start Excel 2003 asking if I want to Enable Macros or Disable Macros. Problem is, I don't have any Macros defined. How can I get rid of this message? I'm running Windows XP. Thank you! Found the answer on a previous post! Sorry - should've checked first! "randys" wrote: > I am getting the dialog box right when I start Excel 2003 asking if I want to > Enable Macros or Disable Macros. Problem is, I don't have any Macros defined. > How can I get rid of this message? I'm running Windows XP. > > Thank you...

Rectification of wrong posting type
Hi There is one PL account say "Mic Expenses" has got wrong posting type "Balance sheet", when we closed the year 2005 ,System has brought forward the balance of that account into next year 2006, which otherwise should have been gone into the Retain Earning of 2006, is there any option to rectify this error, other then passing rectifying JV in 2005(coz in that case if I take last year TB then ending balance of this account would be Zero) or restoring the backup and doing closing procedure again. Thanks in Advance Shabnam There is a Article ID for this in partner s...

Some chart types cannot be combined with other chart types?
Hi, I am getting the above error message everytime I try to insert a chart or any type into a completely blank presentation using either the Insert, Chart command or the Insert Chart button in the Title and Content slide layout. I am using 2007. Any help or suggestions would be appreciated. TIA, Shane Devenshire Try running Office Diagnostics, Shane. Open PPT or Excel, hit the Office button, then <app> Options | Resources. I think that may resolve this issue. -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2007? http://www.echosvoice.com/2007...

macro security #5
I am unable to run a macro from my workbook as i continually get the message telling macros are discable due to security setting being high. I go in and change the security to Medium and even to Low and still the same response .. Any suggestions ? Hi G once you change the settings you need to close the workbook & reopen it (and choose enable macros if you are prompted) ... have you tried this? -- Cheers JulieD julied_ng at hctsReMoVeThIs dot net dot au "G" wrote: > I am unable to run a macro from my workbook as i continually get the message > telling macros ...

Help Required with Macro to Manipulate Data
I have a spreadsheet(s) with the following data on it (this is imported from a text file) A B C D 1 Branch Date Total 2 4501 030204 29 3 4 4501 030204 14 5 6 4502 030204 331 7 8 4502 030204 52 9 10 4503 030204 54 11 12 4503 030204 85 Hi Alay and what do you want to achieve :-) -- Regards Frank Kabel Frankfurt, Germany "Alan T >" <<Alan.T.12jthe@excelforum-nospam.com> schrieb im Newsbeitrag news:Alan.T.12jthe@excelforum-nospam.com... > I have a spreadsheet(s) with the following data on it (this is imported ...

Split data Macro
Hi! Can anyone tell me whats wrong with these two macros? Or help me with a better one. Im trying to split the data in all cells in one column, both these macros only work on the first row. /Marcus The data im trying to split: Q1517A#ABB C5686B#ABB C5687C#ABB 350544-B21 344257-B21 Sub Split1() Dim selectie As Range Dim cel As Range On Error Resume Next Set selectie = Range(ActiveCell.Address & "," _ & Selection.Address).SpecialCells(xlCellTypeConstants, xlTextValues) If selectie Is Nothing Then Exit Sub Application.ScreenUpdating = False Application.Calculatio...

need help to declard type=IntType instead type="xsd:int" with xml schema
Hi, I am writing C# code to produce xml schema. //Create attribute XmlSchemaAttribute att2 = new XmlSchemaAttribute(); att2.Name = "CustomerID"; att2.SchemaTypeName = new XmlQualifiedName("int",schemaNS); att2.Use = XmlSchemaUse.Required; ct.Attributes.Add(att2); I need to produce schema with custom data type instead of the xsd:string or xsd:int The code above will produce the following schema <?xml version="1.0" encoding="utf-8"?> <xsd:schema attributeFormDefault="unqualified" version="1.0" xmlns:xsd=...

Combine columns from seperate sheets into new sheet- macro improve
I've managed to create a macro that will take up to ten columns from one sheet and combine them with up to ten columns from another sheet. It's working as well as I could have hoped, but I'm positive that there are ways to make the macro far more effecient than it currently is. For instance, for each column I have an individual process to handle picking the column from sheet1 and combining it with the column from sheet2 10 times - I'm sure this process could be written to loop 10 times off one bit of code, but I'm uncertain how to do it. Ideally, however, I...

Scatterplots with different colors based on Type of Data
Is it possible to plot a scatterplot with different colors for 2 sets of data? E.g Type of Agreement MRP Discount% Limited 10 5% UnLimited 20 10% I want to show different colors for Limited & Unlimited. Is it possible to plot scatterplots on both axis(Primary & Secondary) Please let me know. Thanks, Satish If I use this range to make a chart Limited 10 5% Unlimited ...

outlook type interface
I have an SDI application and want to change it so I have a split window, on the left I want a TreeControl above a 'PropertyGrid' control. Then my exisiting SDI Client in the space on the right Can anyone point in the right direction to achieve this Any help will be much appreciated Thanks Phil You need to use two CSplitterWnds (as members of your CMainFrame). Override CMainFrame::OnCreateClient and create a vertical splitter as the main child (i.e. 1 row, 2 cols). Then you want to create a horz splitter as a child of the left-hand pane of the main splitter (which has 2 rows, 1 c...

QAT Reference to Macros in Another Workbook Don't Stick
Running Excel 2007 under Vista. I have one workbook which will contain all my data, and a second which contains a set of macros. By adding the second workbook as a reference, I am able to access all of the macros from the first. (I did have to add a dummy macro to the first workbook to get the reference to stick.) I can add these macros to the Quick Access Toolbar in the first workbook, and they work until I close the file; however, they are gone when the file is re-opened. I can add my dummy macro from the first file to the QAT, and that sticks. However, if I then add a macro fro...

Macro Issue
My VBA skills are limited to using recorded macros, not writing them- so when something won't execute... well, I'm lost. I painstakingly recorded my actions importing 38 small text files, including a clear contents command so that the workbook can be re-used. I have done this successfully in the past. The macro worked after creation once, but not after a save/close workbook. It now stalls at ' .Refresh BackgroundQuery:=False' of the first import as displayed below, with 'run time error 1004 - Application Defined or object - defined error Sub DataImport() ' &...

VSTO Excel 2003 to VB 6.0 Type mismatch
Hi, I have an Addin 2003 Application. In this Application I'm calling an VB 6.0 Dll and I send Excel Application Object as Parameter. In VB Project I'm using Workbook_Open event with WithEvents. I have a messagebox there. Unfortunately when i call VB dll , I get this Error Message -- > Type mismatch. Is there any Idea? Code in VS2008 : private Project1.Class1 vbapp; private void ThisAddIn_Startup(object sender, System.EventArgs e) { #region VSTO generated code this.Application = (Excel.Application)Microsoft....

Set up Macro *PLEASE HELP IMPORTANT*
I'm trying to set up a macro so when i open up my bank file i could separate all of my different accounts "pools" and i did the filter which worked well I did the filter and it worked > >> but one problem is when i tried it for a previous day it looked for the > >> rows that i highlighted and this messed it up. there are times that i > >> might have 50 rows and otehr times i might have 100 rows so i need to set > >> it up where after i do the filter it will catch all the lines that are > >> there. > > your help is greatly ...

Excel Macros, PLEASE HELP!
Hello all, i'm new to this forum, so could someone please help me! i'm doing a project for university which involves me having to create macros in excel. Here is the problem, i have 10 links on the left side of my worksheet. which i need to assign macros to, these macros have to link to various charts, objects etc which are kept in additional worksheets in the same file. anyway i can easily assign the macro to go to another worksheet to display that chart, but how do i assign the macro so that once clicked the chart APPEARS in the current worksheet, so the chart sits to the right of t...

predictive typing of addressee
I am frequently sending sensitive email to a person who has a very common name. More than once I have almost sent the email to the wrong party because of the Outlook feature that predicts the addressee name. Does anyone know how to eliminate names from the dropdown list that appears as you type tha addressee name? Or is there a way to stop addressee names from appearing automatically as you type in the "To" field. Any help will be appreciated. joanne Use arrow keys to highlight error address, then hit Delete Btn Do you use Contacts?, hitting the 'To' Btn will bri...

"Can't change data type" error
I'm trying to reduce the field size of text fields in my database. They defaulted to 255 characters when I created my database but in many cases I only need a size of around 20. When I enter the reduced size for one field in the table design view and try to save the change to that field I get the following errors: "Microsoft Access can't change the data type. There isn't enough diskspace or memory." When I click OK to the above I then get the following message: "Errors were encountered durnig the save operation. Data types were not changed. Properties w...

How to convert CString type to LPCSTR type
Hi, I have a CString type variable, and I need to convert it's to LPCSTR type ,what I can do! example: CString m_strExecFile(_T("C:\\\\aa.exe")); next I want to call WinExec Command to execute this file LPCSTR lpcstr ; lpcstr = m_strExecFile.GetBuffer(); WinExec(lpcstr,SW_SHOW); BUT the compilier tells me it can not convert w_char * to LPCSTR type! Why? ps. I use vs2005 program in UNICODE mode. Any helpful ideas would be highly appreciated! -joseph Why use a CString in this case at all, if all you are doing is using it in WinExec. Something ...

Inserting a blank row with a macro?
Hello everyone. Is there any way I can create a macro that will compare two cells such as B1 and B2 and if they dont' match to insert a blank row after B1? Thanks in advance. Aaron Sub CompareEm() If [B1].Value <> [B2].Value Then [B2].EntireRow.Insert End If End Sub You can usually figure out things like this by recording a macro while you do it manually, then modify the code. Jerry Aaron Russell wrote: > Hello everyone. Is there any way I can create a macro that will compare two > cells such as B1 and B2 and if they dont' match to insert a bl...

Aggregate data types mismatch
Using reporting services 2008. Getting a really weird error when calculating a variance between a budget and actual amount in the report. Both the underlying fields are of data type 'money', but when performing the calc in the report writer, i am getting the following error: [rsAggregateOfMixedDataTypes] The Value expression for the textrun 'Variance_MTD_ActualvsBudget1.Paragraphs[0].TextRuns[0]' uses an aggregate function on data of varying data types. Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a si...