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

I've got it working to the point of just pulling in data.  The way I found to get it to work was actually opening the xlsx files.  But I can't close them with a simple Workbooks.close XLfile.  Ha!  Round and round I go with weird things to find.  I tried variations without success, Workbooks(XLFile).close, also adding (savechanges:=false), but no luck so far......  

Sub Data_Pull()

'

' Data_Pull Macro

'

' Pulldata Macro

' Macro recorded 7/2/2020 by xxxxxxxxx

'

'This is for Pulling Amine Data for xxxxxxxxxxxxxx.

    Dim f As String, DataPath As String, Rows As Integer, z As String, SummaryRow As Variant

'Setting up

    Set fs = CreateObject("Scripting.FileSystemObject")

'Data Path location

    DataPath = Sheets("Setup").Cells(3, 3).Value

'Checking data path, stopping if not right, and adding \ at the end if not present

    If Right(DataPath, 1) <> "" Then DataPath = DataPath + ""

    If Not fs.folderexists(DataPath) Then

        MsgBox ("DataPath Not Found for Data. Execution Halted.")

        Exit Sub

    End If

'Turn off Protection so macro can work

    For Each wSheet In Worksheets

        wSheet.Unprotect ("xxxxxx")

    Next wSheet

'Clear old data

    Sheets("Setup").Range("c10:c1048576").Clear

    Sheets("Report").Range("b3:bn35").Clear

    Sheets("Report").Range("be3:bn3").Clear

    Sheets("Amines").Range("a57:s68").Clear

    Sheets("Amines").Range("e2:h56").Clear

    Rows = 10

    SummaryRow = 6

'Verify and pull in files via iteration

        f = Dir(DataPath, vbDirectory)

        Do While f <> ""

            If Right(f, 5) = ".xlsx" Then

                XLFile = DataPath + f

                Workbooks.Open XLFile

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

                Sheets("Summary").Select

'parsing the file name sent to a variable, z, so we can use that for naming the tab

                z = Replace(f, ".xlsx", "")

                ActiveSheet.Name = z

            End If          

a:      f = Dir()

        Loop

End Sub

Hopefully this goes out appropriately.  I copied text from the macro editor to notepad, saved it, then copied the plain text from there to here.

Thank you for your assistance.  It seems like it's just a hunt and peck scenario to get it right these days from previous experience doing it.  

I'll continue on and see if I can get this working.  But it is convincing me that I should perhaps end my willingness to code for folks.  Sad, but it is what it is.  It's an extremely powerful tool to use when you can get it to work.  Now???  More of a pain than a tool.

Answer 3

To:  D_A

re:  code tendered

Several things need to be done with your code...

1. It appears the module may be corrupted.

   As stated earlier, copy your module code out to Notepad and delete the existing module.

   Insert a new module and paste the Notepad text into the new module

2. Use Option Explicit as the first line in every module.
3. Use & not + to join text.
4. When writing code involving multiple workbooks/multiple worksheets...

    You should identify each workbook and and each worksheet.

    So as an example use:  Workbooks("Sludge").Worksheets ("Summary")

    and even better: Application. Workbooks("Sludge").Worksheets ("Summary")

    (using with statements will reduce the amount of typing required)

5. "ThisWorkbook" identifies the workbook where the code is located. 

    I'm not sure its use in the code is appropriate?

6. I removed most of spacing shown in the code as that is my personal preference.

   It makes evaluation easier for me. 

   [Edit- added 'not']  I still do not understand the intent of all of your code.

   Identifying the workbooks/worksheets should help me, you and Excel.

'---

A partial effort...

Option Explicit '<<<<

Sub Data_Pull_R1()

'This is for Pulling Amine Data for xxxxxxxxxxxxxx.

 Dim f As String, DataPath As String, Rows As Integer, z As String, SummaryRow As Variant

 Dim fs As Object, wSheet As Excel.Worksheet, XLfile As String '<<<<

'Setting up

 Set fs = CreateObject("Scripting.FileSystemObject")

'Data Path location

 DataPath = Sheets("Setup").Cells(3, 3).Value

'Checking data path, stopping if not right, and adding \ at the end if not present

 If Right(DataPath, 1) <> "" Then DataPath = DataPath + ""

 If Not fs.folderexists(DataPath) Then

   MsgBox ("DataPath Not Found for Data. Execution Halted.")

   Set fs = Nothing '<<<<

   Exit Sub

 End If

 Set fs = Nothing   '<<<<

'Turn off Protection so macro can work

For Each wSheet In Worksheets

  wSheet.Unprotect ("xxxxxx")

Next wSheet

'Clear old data

Sheets("Setup").Range("c10:c1048576").Clear

Sheets("Report").Range("b3:bn35").Clear

Sheets("Report").Range("be3:bn3").Clear

Sheets("Amines").Range("a57:s68").Clear

Sheets("Amines").Range("e2:h56").Clear

Rows = 10

SummaryRow = 6

'Verify and pull in files via iteration

f = Dir(DataPath, vbDirectory)

Do While f <> ""

  If Right(f, 5) = ".xlsx" Then

      XLfile = DataPath + f

      Workbooks.Open XLfile

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

      Sheets("Summary").Select

     'parsing the file name sent to a variable, z, so we can use that for naming the tab

      z = Replace(f, ".xlsx", "")

      ActiveSheet.Name = z

     'CLOSE  EACH WORKBOOK HERE

  End If

a: f = Dir()

Loop

End Sub

'---

And...

Office 2013 VBA Documentation  (VBA help file download - the latest available)

https://www.microsoft.com/en-us/download/details.aspx?id=40326

'---

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

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

Answer 4

Replying to:

1. It appears the module may be corrupted.

   As stated earlier, copy your module code out to Notepad and delete the existing module.

   Insert a new module and paste the Notepad text into the new module

2. Use Option Explicit as the first line in every module.
3. Use & not + to join text.
4. When writing code involving multiple workbooks/multiple worksheets...

    You should identify each workbook and and each worksheet.

    So as an example use:  Workbooks("Sludge").Worksheets ("Summary")

    and even better: Application. Workbooks("Sludge").Worksheets ("Summary")

    (using with statements will reduce the amount of typing required)

5. "ThisWorkbook" identifies the workbook where the code is located. 

    I'm not sure its use in the code is appropriate?

6. I removed most of spacing shown in the code as that is my personal preference.

   It makes evaluation easier for me.  I still do understand the intent of all of your code.

   Identifying the workbooks/worksheets should help me, you and Excel.

Regarding:

1.  Ok, duh... reading quickly I was thinking that comment was just in regard to the mushed text and getting this platform to show it correctly.  I'll try that regarding replacing the module.  But the code is working thus far.  Still plenty of code to add though.

2.  Ok, I'll have to refresh myself on why, but I can do that as well.

3.  Ok, didn't remember that being an issue but ok.

4.  Yes, I hear you.  There are times when ultimate precision nailing things down is best, but I've also had other times when not doing so works when opening similar files and allowing that back and forth vague nature.  I've typically written code, got it to function, and saved excess nail it down text when not absolutely necessary.

5.  ThisWorkbook is a shortcut way I sometimes use to ensure this workbook is identified for the command instead of typing path and name or assigning it to a variable and using that.  The shortest (most concise) code is typically best and runs fastest.

6.  Yes, absolute reference nails things down and avoids confusion.  But see points 4 and 5.

Thanks for the feedback.  I do appreciate the input.  Bouncing it off someone else always helps clarify matters.  Makes one think about things they're missing or why things are performing as they are.  I'll review your suggestions (meaning look them up, if lookup gives any good results that is) and proceed.

Kind regards NLTL, have a nice evening.

Answer 5

Thanks for listing out links to reference documents.  That was my initial request. 

I'll go through them.  I know I've seen some of them (on Microsoft).  Perhaps the others will help me in my quest.

Kind regards,

D_A