To save time on tasks you do often, you can record the steps of those tasks as a macro. Then, when you want to perform one of those recorded tasks, you can activate the macro to perform the steps for you.
That must a macro you've been dragging around quite some time. My suggestion, would to run ClamXav a mac friendly freeware app that is very stable with Tiger. Read the 'read me' before installing and do not enable 'quarintine feature'. Hi - I have Office 365 installed on 2 mac's. On the one I created and edited macros with no issues. However when I opened the same file on the other mac and attempted to edit one of the macros within the VB editor, the editing toolbar is missing and I cannot find any option to show it.
Word
In Word 2016 for Mac, you can create a macro that has a keyboard shortcut, run a macro, and delete a macro that you no longer need.
Create a macro with a keyboard shortcut
Click View > Macros > Record Macro.
Type a name for the macro, or accept the default that Word provides.
To use this macro in any new documents you create, verify that All Documents (Normal) is selected in the Store macro in list.
Click Keyboard to assign a key combination to your macro.
Wysiwyg editor download. This article outlines the best WYSIWYG (”What You See Is What You Get”,) web editors for Mac OS X to create web sites without CSS, HTML, coding experiences. There are both Free and Paid WYSIWYG HTML Editors For Mac (OS X), here is a list of WYSIWYG HTML editors app for any level of user.We said before about WYSIWYG – What You See is What You Get and free Text Editors for Mac including Lime Text.The purpose of these WYSIWYG HTML Editors For Mac is widely variable, but usually today they are useful for hosting website on free cloud storage.
Word displays the Customize Keyboard dialog box.
Type a combination of keys in the Press new keyboard shortcut box.
When you press a key combination, Word displays the command or action currently assigned to that key combination, if any.
If the key combination that you chose is already assigned, delete it from the Press new keyboard shortcut box, and then choose a new key combination.
Go on to the next step after you have found an unassigned key combination.
To use this keyboard shortcut in any new documents, be sure Normal.dotm, the global template, is selected in the Save changes in list.
Click Assign.
When you are ready to record the macro, click OK.
Click the commands or press the keys for each step in the task.
Word records your clicks and keystrokes, but it does not record text that you select with the mouse. To select text while recording a macro, use the keyboard.
To stop recording, click View > Macros > Stop Recording.
Run a macro
To run a macro, press the keyboard shortcut assigned to it, or run the macro from the Macros list.
Click View > Macros > View Macros.
In the list under Macro name, select the macro that you want to run.
Click Run.
Delete a macro
Click View > Macros > View Macros.
Verify that the location specified by Macros in includes the location of the macro that you want to delete.
The list will include the document that is open in the current window, as well as the global template and Word commands.
Under Macro name, select the macro that you want to delete, and then click the minus sign below the list.
When Word asks to confirm that you want to delete the macro, click Yes.
Excel
In Excel 2016 for Mac, you can create a macro that has a keyboard shortcut, run a macro, and delete a macro that you no longer need.
When you record a macro, the macro recorder records all the steps required to complete the actions that you want your macro to perform. These steps can include typing text or numbers, clicking cells or commands on the ribbon or on menus, formatting, selecting cells, rows, or columns, and dragging your mouse to select cells on your spreadsheet.
Note: The commands for recording, creating, and deleting macros, as well as other advanced features, are also available on the Developer tab on the Ribbon. For more information about using the Developer tab, see Use the Developer tab to create or delete a macro in Excel 2016 for Mac.
Create a macro with a keyboard shortcut
Select Tools > Macro > Record New.
In the Macro name box, enter a name for the macro.
To create a keyboard shortcut for the macro, type a letter in the Option + Cmd box.
Select a location for the macro from the drop-down list.
To save the macro
Click
In the document that you are creating macro in
This Workbook
In all open documents
New Workbook
To be available whenever you use Excel
Personal Macro Workbook
(Optional) Add a description of your macro.
When you are ready to record, click OK.
Click the commands or press the keys for each step in the task.
To stop recording, select Tools > Macro > Stop Recording. (This step will not be recorded in your macro.)
Run a macro
To run a macro, press the keyboard shortcut assigned to it, or run the macro from the Macros dialog.
On the View tab, click View Macros.
Click Macros in the pop up menu and select This Workbook.
Select your macro from the list and click Run.
Delete a macro
Select Tools > Macro > Macros.
Select the macro to delete, and then press the minus sign.
A confirmation message will appear. Click Yes to confirm the deletion.
You can also delete a macro from the Developer tab.
On the Developer tab, click Macros.
In the list, click the macro that you want to delete, and click the delete button.
Word
To automate a repetitive task, you can quickly create a macro in Word. You can also use the Visual Basic Editor in Microsoft Visual Basic for Applications to write your own macro script, or to copy all or part of a macro to a new macro.
Record a macro
When you record a macro, all the steps that are required to complete the actions that you want your macro to perform are recorded. However, navigation back to the tab to stop recording is not included in the recorded steps.
On the Developer tab, under Visual Basic, click Record.
If the Developer tab is not available
On the right side of the ribbon, click , and then click Ribbon Preferences.
Under Customize, select the Developer check box.
In the Macro name box, enter a name for the macro.
On the Store macro in pop-up menu, do one of the following:
To save the macro
Click
In the document that you are creating the macro in
[document name] (document)
In all open documents
All Documents (Normal)
Click OK, and then perform the actions that you want to record.
On the Developer tab, under Visual Basic, click Stop.
Use Visual Basic for Applications to create a macro
You can use the built-in Visual Basic Editor to create a macro.
On the Developer tab, under Visual Basic, click Editor.
If the Developer tab is not available
On the right side of the ribbon, click , and then click Ribbon Preferences.
Under Customize, select the Developer check box.
If you need to insert a module, in the Visual Basic Editor, on the Insert menu, click Module.
In the code window of the module, type or paste the macro code that you want to use.
When you are finished, on the Word menu, click Close and Return to Microsoft Word.
Run a macro
On the Developer tab, under Visual Basic, click Macros.
If the Developer tab is not available
On the right side of the ribbon, click , and then click Ribbon Preferences.
Under Customize, select the Developer check box.
In the list, click the macro that you want to run, and then click Run.
Edit a macro
To edit a macro, you use the Visual Basic Editor.
On the Developer tab, under Visual Basic, click Macros.
If the Developer tab is not available
On the right side of the ribbon, click , and then click Ribbon Preferences.
Under Customize, select the Developer check box.
In the list, click the macro that you want to edit, and then click Edit.
The Visual Basic Editor opens.
Make the changes that you want.
When you are finished, on the Word menu, click Close and Return to Microsoft Word.
Copy part of a macro to create another macro
On the Developer tab, under Visual Basic, click Macros.
If the Developer tab is not available
On the right side of the ribbon, click , and then click Ribbon Preferences.
Under Customize, select the Developer check box.
In the list, click the macro that you want to copy, and then click Edit.
The Visual Basic Editor opens.
In the code window of the Visual Basic Editor, select the lines of the macro that you want to copy.
Tip: To copy the whole macro, include the Sub and End Sub lines in the selection.
On the Edit menu, click Copy.
In the Project box of the code window, click the module where you want to put the code.
On the Edit menu, click Paste.
Delete a macro
On the Developer tab, under Visual Basic, click Macros.
If the Developer tab is not available
On the right side of the ribbon, click , and then click Ribbon Preferences.
Under Customize, select the Developer check box.
In the list, click the macro that you want to delete, and then click Delete.
PowerPoint
To automate a repetitive task, you can use Visual Basic for Applications to create a macro in PowerPoint.
Use Visual Basic for Applications to create a macro
You can use the built-in Visual Basic Editor to create a macro.
On the Developer tab, under Visual Basic, click Editor.
If the Developer tab is not available
On the right side of the ribbon, click , and then click Ribbon Preferences.
Under Customize, select the Developer check box.
If you need to insert a module, in the Visual Basic Editor, on the Insert menu, click Module.
In the code window of the module, type or paste the macro code that you want to use.
When you are finished, on the PowerPoint menu, click Close and Return to Microsoft PowerPoint.
Run a macro
On the Developer tab, under Visual Basic, click Macros.
If the Developer tab is not available
On the right side of the ribbon, click , and then click Ribbon Preferences.
Under Customize, select the Developer check box.
In the list, click the macro that you want to run, and then click Run.
Edit a macro
To edit a macro, you use the Visual Basic Editor.
On the Developer tab, under Visual Basic, click Macros.
If the Developer tab is not available
On the right side of the ribbon, click , and then click Ribbon Preferences.
Under Customize, select the Developer check box.
In the list, click the macro that you want to edit, and then click Edit.
The Visual Basic Editor opens.
Make the changes that you want.
When you are finished, on the PowerPoint menu, click Close and Return to Microsoft PowerPoint.
Copy part of a macro to create another macro
On the Developer tab, under Visual Basic, click Macros.
If the Developer tab is not available
On the right side of the ribbon, click , and then click Ribbon Preferences.
Under Customize, select the Developer check box.
In the list, click the macro that you want to copy, and then click Edit.
The Visual Basic Editor opens.
In the code window of the Visual Basic Editor, select the lines of the macro that you want to copy.
Tip: To copy the whole macro, include the Sub and End Sub lines in the selection.
On the Edit menu, click Copy.
In the Project box of the code window, click the module where you want to put the code.
On the Edit menu, click Paste.
Delete a macro
On the Developer tab, under Visual Basic, click Macros.
If the Developer tab is not available
On the right side of the ribbon, click , and then click Ribbon Preferences.
Under Customize, select the Developer check box.
In the list, click the macro that you want to delete, and then click Delete.
Excel
To automate a repetitive task, you can quickly create a macro in Excel. You can also use the Visual Basic Editor in Microsoft Visual Basic for Applications to write your own macro script, or to copy all or part of a macro to a new macro.
Record a macro
All the steps that are required to complete the actions that you want your macro to perform are recorded. However, navigation back to the tab to stop recording is not included in the recorded steps.
On the Developer tab, under Visual Basic, click Record.
If the Developer tab is not available
On the right side of the ribbon, click , and then click Ribbon Preferences.
Under Customize, select the Developer check box.
In the Macro name box, enter a name for the macro.
Ddvdvideosoft Free Video Editor Free Video Editor another video trimming tool step through the source movie, select the left and right points then cut that section from the video and save the results. https://safelucky.netlify.app/best-lite-video-editor-for-mac-os.html.
Tip: To create a keyboard shortcut for the macro, type a letter in the Option+Cmd+ box.
On the Store macro in pop-up menu, do one of the following:
To save the macro
Click
In the document that you are creating the macro in
This Workbook
In all open documents
New Workbook
To be available whenever you use Excel
Personal Macro Workbook
The personal macro workbook is located at Users/ username/Library/Application Support/ Microsoft/Office/Excel.
Click OK, and then perform the actions that you want to record.
On the Developer tab, under Visual Basic, click Record again.
Use Visual Basic for Applications to create a macro
You can use the built-in Visual Basic Editor to create a macro.
On the Developer tab, under Visual Basic, click Editor.
If the Developer tab is not available
On the right side of the ribbon, click , and then click Ribbon Preferences.
CSS sprite sheet editor for macOS An important tool for every web designer Quick Sprites simplifies the creation and maintenance of CSS sprite sheets that enable you to optimize your websites. Animated sprite editor & pixel art tool. Loop a section in forward, reverse, ping-pong modes. Change preview speed. Free sprite editor. Aug 07, 2018 A free sprite editor for mac. Discussion in '2D' started by MuffinMan101010, Jul 20, 2016. Joined: Jul 12, 2016 Posts: 1. Im looking for a free sprite sheet editor for mac, it doesn't need to make them into a sprite sheet, just an editor. I already have a sprite sheet maker, i just need an editor. Piskel, free online sprite editor. A simple web-based tool for Spriting and Pixel art. Create pixel art, game sprites and animated GIFs. Free and open-source.
Under Customize, select the Developer check box.
If you need to insert a module, in the Visual Basic Editor, on the Insert menu, click Module.
In the code window of the module, type or paste the macro code that you want to use.
When you are finished, on the Excel menu, click Close and Return to Microsoft Excel.
Run a macro
On the Developer tab, under Visual Basic, click Macros.
If the Developer tab is not available
On the right side of the ribbon, click , and then click Ribbon Preferences.
Under Customize, select the Developer check box.
In the list, click the macro that you want to run, and then click Run.
Tip: To run a macro by using a keyboard shortcut, press OPTION + +the letter you typed when you created the keyboard shortcut.
Edit a macro
To edit a macro, you use the Visual Basic Editor.
On the Developer tab, under Visual Basic, click Macros.
If the Developer tab is not available
On the right side of the ribbon, click , and then click Ribbon Preferences.
Under Customize, select the Developer check box.
In the list, click the macro that you want to edit, and then click Edit.
The Visual Basic Editor opens.
Make the changes that you want.
When you are finished, on the Excel menu, click Close and Return to Microsoft Excel.
Configure a macro to run automatically when a workbook is opened
After you complete this procedure, the macro will run whenever you open the workbook that contains the macro.
On the Developer tab, under Visual Basic, click Record.
If the Developer tab is not available
On the right side of the ribbon, click , and then click Ribbon Preferences.
Under Customize, select the Developer check box.
In the Macro name box, type Auto_Open.
On the Store macro in pop-up menu, do one of the following:
To save the macro
Click
In the document that you are creating the macro in
This Workbook
Top video editor for mac. In all open documents
New Workbook
To be available whenever you use Excel
Personal Macro Workbook
Click OK, and then perform the actions that you want to record.
On the Developer tab, under Visual Basic, click Record again.
On the File menu, click Save As.
On the Format pop-up menu, click Excel Macro-Enabled Workbook (.xlsm), and then click Save.
Copy part of a macro to create another macro
On the Developer tab, under Visual Basic, click Macros.
If the Developer tab is not available
On the right side of the ribbon, click , and then click Ribbon Preferences.
Under Customize, select the Developer check box.
In the list, click the macro that you want to copy, and then click Edit.
The Visual Basic Editor opens.
In the code window of the Visual Basic Editor, select the lines of the macro that you want to copy.
Tip: To copy the whole macro, include the Sub and End Sub lines in the selection.
On the Edit menu, click Copy.
In the Project box of the code window, click the module where you want to put the code.
On the Edit menu, click Paste.
Delete a macro
On the Developer tab, under Visual Basic, click Macros.
If the Developer tab is not available
On the right side of the ribbon, click , and then click Ribbon Preferences.
Under Customize, select the Developer check box.
In the list, click the macro that you want to delete, and then click Delete.
Excel on the Mac has not always been the same powerhouse it was on Windows. Macros really wouldn’t work unless they were created exclusively for the Mac.
Best photo editor free for mac. Starting in 2013, Microsoft brought back macros. There are two types of macros: those you can create by quickly recording your actions, and those that use VBA to design more advanced automations. With Office 2016, Excel is using the same codebase on all platforms. This change will make it easier for macros to work across platforms.
So let’s take a look at how this currently works on macOS.
Enabling Macros in Excel on Mac
Working with macros in Excel on your Mac may not be enabled by default. This setting is because macros can be a possible malware vector. The easiest way to tell is to see if you have the Developer tab available on the Ribbon in Excel. If you don’t see it, it is simple to enable.
Click on Excel in the menu bar, and then select Preferences in the dropdown. In the menu, click on Ribbon & Toolbar. In the right-hand list, Developer should be at the bottom, click the checkbox. Finally, click Save and you should see the Developer tab show up at the end of the Ribbon.
After you create each workbook with macros, save it in a new format .xlsm to use the macros after reopening the file. If you forget, Excel will remind you each time you try to save. You’ll also need to enable macros every time you open the file.
Manually Recording a Macro in Excel on Mac
Though you can code macros, that might not be for everyone. If you’re not ready to start working with VBA, Excel lets you record the steps for your macro in an existing sheet. Click on the Developer tab to see your options.
You’re looking for the third option in the Ribbon, Record Macro. Click this, and a dialog pops up allowing you to name your macro and set a keyboard shortcut. You can scope your macro to the Current Workbook, a New Workbook, or in your Personal Macro Workbook. The Personal Macro Workbook is in your user profile and lets you use your macros between your files.
Once you record your actions, they are available on this same tab. Clicking macros will bring up the saved macros in your workbook. Click on your macro name and click Run to run your recorded actions.
Example 1: Daily Sales Total and Hourly Average
For an example macro, you are going to run through a daily sales sheet, with the sales broken down by hourly totals. Your macro is going to add a daily sales total, and then add an average in the last column of each hourly period. If you work in retail or other sales position, this is a helpful sheet to track revenue.
We need to set up the first sheet. Using this first blank as a template to copy into a new tab each day could save you some time. In the first column/row put Hour/Date. Across the top add Monday through Friday.
Then in the first column put a break down of hourly totals from 8-5. I used 24-hour time, but you can use AM/PM notation if you prefer. Your sheet should match the screenshot above.
Add a new tab, and copy your template into it. Then fill out your sales data for the day. (If you don’t have data to populate this sheet, you can enter =RandBetween(10,1000) in all the cells to create dummy data.) Next, click on Developer in the Ribbon.
Then, click on Record Macro. In the dialog enter the name as AverageandSum and leave it stored in This Workbook. You can set a shortcut key if you like. You can enter a description if you need more details on what the macro does. Click okay to start setting up the macro.
At the bottom of the hourly listings enter Daily Totals. In the cell next to it, enter =SUM(B2:B10). Then copy and paste that into the rest of the columns. Then in the header add Average after the last column. Then in the next cell down, enter =Average(B2:F2). Then, paste that into the cells in rest of the column.
Then click Stop Recording. Your macro is now able to use on each new sheet you add to your workbook. Once you have another sheet of data, go back to Developer and click Macros. Your macro should be highlighted, click run to add your sums and averages.
This example can save you a couple of steps, but for more complex actions that can add up. If you do the same operations on data with identical formatting, use recorded macros.
VBA Macros in Excel on Mac
Manually recorded macros in Excel help with data that is always in the same size and shape. It is also useful if you want to perform actions on the entire sheet. You can use your macro to prove the problem.
Add another hour and day to the sheet and run the macro. You’ll see the macro overwrites your new data. The way we get around this is using code to make the macro more dynamic using VBA, which is a slimmed down version of Visual Basic. The implementation focuses on automation for Office.
It is not as easy to pick up as ApplescriptWhat Is AppleScript? Writing Your First Mac Automation ScriptWhat Is AppleScript? Writing Your First Mac Automation ScriptAppleScript can help you automate repetitive tasks on your Mac. Here's an overview and how to write your first AppleScript script.Read More, but Office’s automation is entirely built around Visual Basic. So once you work with it here, you are quickly able to turn around and use it in other Office apps. (It can also be a big help if you’re stuck with a Windows PC at work.)
When working with VBA in Excel, you have a separate Window. The screenshot above is our recorded macro as it appears in the code editor. The windowed mode can be helpful to play around with your code as you’re learning. When your macro gets hung up, there are debugging tools to look at the state of your variables and sheet data.
Office 2016 now comes with the full Visual Basic editor. It allows you to use the Object Browser and debugging tools that used to be limited to the Windows version. You can access the Object Browser by going to View > Object Browser or just press Shift + Command + B. You can then browse through all the classes, methods, and properties available. It was very helpful in constructing the code in the next section.
Example 2: Daily Sales Total and Hourly Average With Code
Before you start coding your macro, let’s start by adding a button to the template. This step makes it much easier for a novice user to access your macro. They can click a button to call the macro rather than digging into the tabs and menus.
Switch back to the blank template sheet you created in the last step. Click on Developer to get back to the tab. Once you’re on the tab, click on Button. Next, click somewhere in the sheet on the template to place the button. The macros menu comes up, name your macro and click New.
The Visual Basic Window will open up; you’ll see it listed as Module2 in the project browser. The code pane will have Sub AverageandSumButton() at the top and a few lines down End Sub. Your code needs to go between these two, as it is the beginning and the end of your macro.
Step 1: Declaring Variables
To begin, you will need to declare all of your variables. These are in the code block below, but a note about how they are constructed. You should declare all variables using Dim before the name, and then as with the datatype.
Now that you have all of your variables, you need to use some of the range variables right away. Ranges are objects that hold sections of the worksheet as addresses. The variable All Cells will be set to all the active cells on the sheet, which includes the column and row labels. You get this by calling the ActiveSheet object and then it’s UsedRange property.
The problem is you don’t want the labels included in the average and sum data. Instead, you’ll use a subset of the AllCells range. This will be the TargetCells range. You manually declare its range. Its start address is going to be the cell at the second row in the second column of the range.
You call this by calling your AllCells range, using its Cells class to get that specific cell using (2,2). To get the final cell in the range, you will still call AllCells. This time using SpecialCells method to get the property xlCellTypeLastCell. You can see both of these in the code block below.
Step 2: For Each Loops
The next two sections of code are For EachThe Absolute Basics Of Programming For Beginners (Part 2)The Absolute Basics Of Programming For Beginners (Part 2)In part 2 of our absolute beginners guide to programming, I'll be covering the basics of functions, return values, loops and conditionals. Make sure you’ve read part 1 before tackling this, where I explained the..Read More loops. These loops go through an object to act on each subset of that object. In this case, you are doing two of them, one for each row and one for each column. Since they are almost exactly the same, only one of them is here; but both are in the code block. The details are virtually identical.
Before you start the loop for each row, you need to set the target column where the loop writes the average of each row. You use the ColumnPlaceHolder variable to set this target. You set it equal to the Count variable of the Cells class of AllCells. Add one to it to move it to the right of your data by appending +1.
Next, you are going to start the loop by using For Each. Then you want to create a variable for the subset, in this case, subRow. After the In, we set the main object we are parsing TargetCells. Append .Rows at the end to limit the loop to only each row, instead of every cell in the range.
Word For Mac Editor Tools Macro System
Inside the loop, you use the ActiveSheet.Cells method to set a specific target on the sheet. The coordinates are set by using subRow.Row to get the row the loop is currently in. Then, you use ColumnPlaceHolder for the other coordinate.
You use this for all three steps. The first you append .value after the parentheses and set equal to WorksheetFunction.Average(subRow). This writes the formula for the average of the row into your target cell. The next line you append .Style and set that equal to “Currency”. This step matches the rest of your sheet. On the last line, you append .Font.Bold and set it equal to True. (Note there are not quotes around this one, as it is the boolean value.) This line bolds the font to make the summary info stand out from the rest of the sheet.
Both steps are in the code example below. The second loop swaps rows for columns and changes the formula to Sum. Using this method ties your calculations to the format of the current sheet. Otherwise, its linked to the size at the time you record the macro. So when you work more days or hours, the function grows with your data.
Step 3: Label Your Summaries
Next, label the new row and column, set RowPlaceHolder and ColumnPlaceHolder again. First, use AllCells.Row to get the first row in the range, and then AllCells.Column+1 to get the last column. Then you will use the same method as the loop to set the value to “Average Sales”. You’ll also use the same .Font.Bold property to bold your new label.
Then reverse it, setting your placeholders to the first column and last row to add “Total Sales”. You want to bold this as well.
Both steps are in the code block below. This is the end of the macro noted by End Sub. You should now have the entire macro, and be able to click the button to run it. You can paste all these code blocks in order into your excel sheet if you want to cheat, but where is the fun in that?
What’s Next for Macros in Excel on Mac?
Word Editor Free
Recorded macros are great to use for predictable repetition. Even if it’s something as simple as resizing all cells and bolding headers, these can save you time.
Visual Basic opens the door for Mac Excel users to dig deep into Office automation. Visual Basic was traditionally only available on Windows. It allows your macros to adapt to the data dynamically, making them more versatile. If you have the patience, this can be the doorway to more advanced programming.
Mac Editor De Video Gratis
Want more time-saving spreadsheet tricks? Learn how to highlight specific data automatically with conditional formatting in ExcelAutomatically Format Data in Excel Spreadsheets With Conditional FormattingAutomatically Format Data in Excel Spreadsheets With Conditional FormattingExcel's conditional formatting feature lets you format individual cells in an Excel spreadsheet based on their value. We show you how to use this for various everyday tasks.Read More and conditional highlighting in Numbers on MacHow to Use Conditional Formatting in Numbers on MacHow to Use Conditional Formatting in Numbers on MacConditional formatting makes your spreadsheets stand out. Here's how to use conditional formatting in Numbers on Mac.Read More.
Explore more about: Microsoft Excel, Visual Basic Programming.
I am a Mac user who has written (conservative estimate) 150 VBA macros for Excel and Word (mostly Excel). As I have programming experience dating back more years than I care to admit (and several programming languages), I did not find VBA to be challenging. However, .. many Office for Mac versions (I am using 2011) DO NOT INCLUDE VBA help. This disappeared somewhere along the line. In my case, I had enough experience by then to not be impacted, but a new user would surely find that a major issue.
Second, macros can be executable code or functions. The latter are useful in other macros. A function, Fcn, is used within other code as Fcn (a, b) where a and b are the values operated on by the function Fcn.
Another advice I would add is to document all the steps in your VBA macro with comment lines (these are lines starting with the ' character). Use these to identify the meaning of all your variables AND what each section of code is intended to do. Regardless of the quality of your memory, this will facilitate the inevitable corrections you will want to make or using the code in new macros. Following is code including comments from two of my Excel macros (a function and a procedure identifying info blocked) as an example [the reason for them is irrelevant, the point is the use of comments to document the code; the lines with just a ' are used for vertical spacing; copying what follows into a word processor will make it easier to read]:
*****
Function ExParse(Old As String) As String
'
' ExParse Function
' Function created by XXXXXX, on 8 February 2003
' Copyright XXXXXX, February 2003, All rights reserved
'
' ExParse returns the string Old with the '|' cleaned out and replaced with Y, M, and D
' If the string old will be ambiguous, the value of ExParse is 'Manual'
'
' ExParse requires these user-defined functions:
' - HowMany
' - FindIt (indirect)
'
Dim NumD, NumM, NumY, NumPole As Integer
'
' NumY, NumM, NumD, and NumPole are the number of Y, M, D, and '|', respectively, found in Old
'
ExParse = Old
'
' Eliminate the character '|' at the right end of string
'
If Right(Old, 1) = '|' Then
ExParse = Left(Old, Len(Old) - 1)
End If
'
' If the string is ambiguous, get out
'
NumD = HowMany('D', ExParse)
NumM = HowMany('M', ExParse)
NumY = HowMany('Y', ExParse)
NumPole = HowMany('|', ExParse)
If (NumD > 1) Or (NumM > 1) Or (NumY > 1) Or (NumPole > 0) Then
ExParse = 'Manual'
End IfEnd Function 'ExParse
*******
Sub SpaceRemover()
'
' SpaceRemover Macro
' Macro created by XXXXXX, on 22 April 2010
' Copyright, XXXXXX, April 2010, All rights reserved.
'
' SpaceRemover removes one or two leading or trailing space characters from
' every cell in an input range. Those are data input errors that are difficult
' to pick up in proofreading.
'
' SpaceRemover requires NO user-defined functions
'
Dim FirstRow As Integer
Dim LastRow As Long
Dim FirstColumn As Integer
Dim LastColumn As Integer
Dim J As Long
Dim K As Long
Dim Temp As String
'
' FirstRow, LastRow, FirstColumn, and LastColumn define the area on the spreadsheet
' to be checked
' J is a loop counter across the columns
' K is a loop counter across the rows
' Temp is a temporary string to hold the cell value
'
' Get the FirstRow, LastRow, FirstColumn, and LastColumn numbers
'
FirstRow = InputBox('What is the number of the first row?')
LastRow = InputBox('What is the number of the last row?')
FirstColumn = InputBox('What is the number of the first column?')
LastColumn = InputBox('What is the number of the last column?')
'
' Loop through the cells in the defined area
'
For J = FirstColumn To LastColumn
For K = FirstRow To LastRow
'
' Set Temp to the cell contents
'
Temp = Cells(K, J).Formula
'
' If the cell contains a formula, leave it alone
'
If Left(Temp, 1) = '=' Then
GoTo Skipping
End If
'
' Remove a leading or trailing space charater (twice)
'
If Temp ' Then
If Right(Temp, 1) = ' ' Then
Temp = Left(Temp, Len(Temp) - 1)
End If
If Right(Temp, 1) = ' ' Then
Temp = Left(Temp, Len(Temp) - 1)
End If
If Left(Temp, 1) = ' ' Then
Temp = Right(Temp, Len(Temp) - 1)
End If
If Left(Temp, 1) = ' ' Then
Temp = Right(Temp, Len(Temp) - 1)
End If
End If
'
' Set the cell contents back to (possibly revised) Temp
'
Cells(K, J).Formula = Temp
Skipping:
Next ' K
Next ' JEnd Sub ' SpaceRemover