Excel VBA for Win10 and Office365 documentation?

Question

Greetings,

I have written macros at work and at home off and on for years for various Managers, PhD, Techs, and family.  It seems like every version of Office changes what code or syntax we are supposed to use to code macros in Excel.  Ok, I get it, things change.

I have macros that have worked for years and then a new version of Office is rolled out at my worksite.  And then my macros for people won't work.  So I then search for the reason(s), and it is typically that a command has been removed from availability or no longer functions the same and I have to find "another way to skin the cat" to use a metaphor. 

My Question:

WHERE CAN A PERSON GET A VBA CODING GUIDE FOR THEIR VERSION OF OFFICE AND EXCEL? 

If I search online in Microsoft for something like, lets say "EXCEL VBA" "OFFICE365" ".copy after:=worksheets(worksheets.count)" I get thousands of hits on that search with none on the first five pages relevant (best hits first right?) or useful.  Making the search useless as it would take a month to go through thousands of them, perhaps to not even find what I'm looking for.  A total waste of my time, which isn't cheap.

In this current instance I'm taking a previously functional very basic line of code from another macro and hoping to use it in a new macro for a new scenario.  This line of code USED to work but no longer functions.  

Worksheets("Summary").Copy After:=Worksheets(Worksheets.Count) 

Now when stepping through this line in the macro I get a run time error '9': Subscript out of range.  Which of course is fairly useless information. 

• Subscript of which thing?
• What is the range it needs to fit?
• I don't know if the problem lies with the "copy" portion
• Or the "after" portion.
• I don't know if it's not happy about copying a worksheet from one workbook to put into another.

I've tried various resolutions for this one thing.  No joy.

I've been able to muddle through previous Excel VBA help files, which weren't great but I could always find some resolution, some bit of code that I could make work or some enlightenment regarding which code or syntax to use.  Now, unfortunately, it seems futile to even search.

I'm still trying to utilize Excel macros for the fact that everyone has it on their work computer, and thus other programs are not needed.  But it seems that Excel, in regard to users being able to utilize the extreme power of VBA macros combined with spreadsheets, is becoming totally useless.

I very much look forward to finding out where a person can search for Excel VBA for Win10 and Office365 and not ALSO get links to worthless (in this situation) .NET code, graphics code, etc, etc, etc.

Anyone have any better way to search than I have found for finding ANYTHING relevant regarding Excel VBA these days?  Or how to find the relevant coding guide for a version?  Anyone?

Answer 1

"jumbled trash" ...

Not your fault. The editor used by this site sometimes does that. Strips out all formatting and, "extra" blank spaces and line returns.  EXTREMELY frustrating when it makes some formatted text into an unreadable blob.

.

Actually, Excel VBA changes very little from version to version.

.

Here are some links to VBA references I've collected

Excel VBA reference (MS)

https://docs.microsoft.com/en-us/office/vba/api/overview/excel

This reference contains conceptual overviews, programming tasks, samples, and references to help you develop Excel solutions.

Use the table of contents in the navigation on the left to view the topics in the following sections:

.  *  Concepts: Provides important concepts for developing custom Excel solutions.

.  *  Object model reference: Provides reference materials for the Excel object model.

.  *  Graph Visual Basic reference

.

MS VBA Language Referencehttps://msdn.microsoft.com/VBA/VBA-Language-Reference****Welcome to the Visual Basic for Applications (VBA) language reference for Office. This reference contains conceptual overviews, programming tasks, samples, and references to guide you in developing solutions based on VBA.

.

@ Shortcut for getting Command Syntax rightJohn Franco **www.wizdoh.com** **– “The Power Of Understanding The Fundamentals Of Excel”**Have you ever had these problems:

   - Struggling to type the full syntax of a particular Excel VBA command or object, or

   - Trying to remember the exact syntax of your VBA instruction

   - Guessing about what variations exist for each VBA keyword, which arguments you can omit without errors, etc…

   - Receiving compile error messages like “Argument not optional” over and over

Then keep reading because I will share with you a magic and simple macro trick you can use RIGHT NOW to write the full VBA syntax of your macros with less typing and without a doubt…

ET MR Macros.docx.

Functions (alphabetical) 2016-2007.

https://support.office.com/en-us/article/Excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188 .

Excel Functions by Category- MyOnlineTrainingHub.

https://www.myonlinetraininghub.com/excel-functions .

Here you'll find an index of common Excel Functions written in layman's terms and in a language we all understand. No computer speak, just plain English and practical examples of Excel functions used in formulas.

Excel functions (by category) – 365 / 2019 – 2007 – (MS Support)

https://support.office.com/en-us/article/excel-functions-by-category-5f91f4e9-7b42-46d2-9bd1-63f26a86c0eb .

Excel for Office 365, 365 for Mac, Online, 2019, 2016, 2016 for Mac, Mac 2011, 2010, 2007, iPad, iPhone, Android tablets, Android phones, Mobile, Starter 2010 .

If you are doing complex formulas, this is a handy tool to make them more readable

Ron De Bruin’s Excel 2003-2016 Function Bible -

http://www.rondebruin.nl/efb/efb.htm

You can use the link below to download all the 468 example files and a Function Index workbook so it is easy to search and open the example workbooks. When you open the Excel Function Bible Menu workbook you will find a worksheet for each function classification group but I also add the classifications on my site with a brief description, check out the links below the download. Download the Excel Function Bible (18.4 mb)

.

@ **** Automate tasks with the Macro Recorder- record macrohttps://support.office.com/en-us/article/Automate-tasks-with-the-Macro-Recorder-974ef220-f716-4e01-b015-3ea70e64937b

To automate a repetitive task, you can record a macro with the Macro Recorder in Microsoft Excel. Imagine you have dates in random formats and you want to apply a single format to all of them. A macro can do that for you. You can record a macro applying the format you want, and then replay the macro whenever needed.

.

Here is a reference to an older version of VBA

@ Excel 4.0 Macro Functions (download)      2017 10 26

https://www.myonlinetraininghub.com/excel-4-macro-functions

e vba- Excel 4.0 Macro Functions Reference.pdf  downloaded

Before VBA there were XLM macros. VBA was introduced in Excel version 5, so these Excel macros are also known as Excel 4 macros.

Even though these XLM macros are quite old they still work in Excel 2016. To use them you call them like a regular function e.g. =GET.CELL(64,A1) but entering this into a cell will give you an error.

What you need to do is combine them with defined names and then you can do some cool stuff.

You can do things that might normally require writing VBA, so if you don't fancy doing any coding check these out. Listing files in a folder, highlighting cells containing formulas, or getting the background color (ColorIndex) of a cell. Once you know a cell's background color, you could then do math with cells of the same color like sum, average etc.

Enumerations in the Word object model 2016https://docs.microsoft.com/en-us/office/vba/api/word(enumerations)

List of constants you can use to control formatting in VBA

ET MR Macros.docx . @   Word Enumerated Constants Office 2003https://msdn.microsoft.com/en-us/library/office/aa211923(v=office.11).aspx

This topic provides a list of all enumerated constants in the Microsoft Word object model.

List of constants you can use to control formatting in VBA All on one page (for eash copy and search)

ET MR Macros.docx.

@ Wellsr.com Excel VBA TutorialsChapter 1: Getting Started . 1. Enable Developer Tab  https://wellsr.com/vba/excel/enable-developer-tab/

. 2. Open Visual Basic Editor------------ .3.        Create your first Macro. 4. Recording a Macro------------------- **.**5.        CommentsChapter 2: Getting Around. 1. Range Object-------------------------- .2.        Select and Selection. 3. Workbook Object--------------------- **.**4.        Worksheet ObjectChapter 3: Variables and Data Types. 1. VBA Data Types---------------------- .2.        Declaring Variables. 3. Variable Scope & Lifetime----------- **.**4.        Option Explicit

.

Excel Formula Beautifier

http://excelformulabeautifier.com/?numberOfSpaces=3

Parses Excel formulas with indentation ie: =IF(SUM( IF(FOO = BAR, 10, 0), 10 ) = 20 , “FOO”, “BAR”)

is converted to

=IF(

    SUM(

        IF(

            FOO = BAR,

            10,

            0

        ),

        10

    ) = 20,

    “FOO”,

    “BAR”

)

You can copy the spaced out formula back in to Excel in the formula bar (not the cell!) and it will maintain the spacing.

.

Answer 2

To:  DataAutomation

re:  "I've tried various resolutions for this one thing.  No joy."

Excel went to the Single Document Interface after XL2010.

That did affect how code is used.

My belief is changes are designed to drive users to the new XL versions with the monthly rent.

Fully qualifying your code can help.

Does the following code work for you?...

ThisWorkbook.Worksheets("Summary").Copy _ After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)

'---

The Professional_Compare file also cleans, lists common items, lists uniques and more.

http://s000.tinyupload.com/?file\_id=03004384606927257055

Answer 3

Whoa, my reply looks like jumbled trash.  I don't know why it changed all my spacing...  smh.   It didn't do that with my original question.   I deleted it.

Answer 4

To:  Data Automation

re:  jumbled reply

Delete the jumbled post.

Maybe...

  copy your code to Notepad,

  insert a new module,

  copy from Notepad, paste into the new module.

  delete the old module

NLtL

'---