# What I would like to see in Excel 2016



## Dryver14

I'd like a fixed TODAY() function

I know Today() is useful and relevant to alot of date coding but a lot of people ask how to hardcode it.

I think something like FIXTODAY() and FIXNOW() where when you use them that date or time are hardcoded
would be a useful thing.

Ideas, thoughts?


----------



## Ruddles

You could use *Ctrl-;* and *Ctrl-:*


----------



## Dryver14

Wow, that gets the date, : and ; are on the same key how do i seperate the 2?


----------



## Ruddles

Use the *Shift *key in the usual way, so *Ctrl-;* and *Shift-Ctrl-;*


----------



## Jon von der Heyden

Here's what I want:



The ability to use (load) CustomViews on a protected worksheet.
The ability to allow formulae in tables to autofill on a protected worksheet.
The ability to multi-select sheets from the Unhide sheets dialog so that I can unhide more than 1 at a time.
The ability to (un)protect multiple sheets in one hit (suing a common password).
The ability to use (date) grouping in PowerPivot pivot tables.
I also wish that slicer formats would properly apply to slicers when the affected pivot table contains calculated items.  At the moment items with no values do not inherit the specified format.
More text manipulation functions.
Finally there are  certain worksheet properties that are not preserved in the workbook memory.  For example, EnableOutlining!  If I set this to True then even still the outlines are disabled on a protected sheet when the workbook is opened; forcing me to use the Workbook_Open event to allow users to expand/contract groups in a protected sheet.  Sometimes I wish I could enable this in a macro-free workbook; but alas this remains impossible.


There you go Microsoft; my 2016 wish list.


----------



## Dryver14

Good list,

I'm joining you on the unhide multiple worksheets.


----------



## Jon von der Heyden

For me the restrictions imposed by worksheet protection is a real killer.  Experience has taught me the importance of locking things down and controlling how users use my spreadsheets.  It's just amazing how apt users are at breaking and tampering with spreadsheets.  Yet when w/s protection is applied so many of the really cool features become impossible.


----------



## nuked

Dryver14 said:


> Good list,
> 
> I'm joining you on the unhide multiple worksheets.



Not ideal, but you could copy this into the immediate window



		Code:
__


 for each sht in thisworkbook.Worksheets: sht.visible=true: next sht


----------



## Jon von der Heyden

You know what would be cool?  A dialog that utilises a dropdown similar to that of autofilter.  So one where you can (de)select multiple items, but also use a search box.  I rarely have too many sheets in a workbook but I know lots of people that flood there workbooks with sheets so it might be handy for the longer lists of sheet names.  And even useful for those that have some commonality in sheet names, so one could filter e.g. sales, and it would list all sheets that contains that keyword.

I hope MS are taking notes


----------



## RoryA

You could make one in about 10 minutes...


----------



## unorthodoxconqueror

how about an "Idiotproof this workbook" functionality that stops people breaking your formulas / macros / formatting....


----------



## Peter_SSs

unorthodoxconqueror said:


> how about an "Idiotproof this workbook" functionality that stops people breaking your formulas / macros / formatting....


Isn't that called sheet/workbook/vba protection?


----------



## Peter_SSs

nuked said:


> Not ideal, but you could copy this into the immediate window
> 
> 
> 
> Code:
> __
> 
> 
> for each sht in thisworkbook.Worksheets: sht.visible=true: next sht


Definitely not ideal. I only want to unhide 9 of the 15 hidden worksheets. 


Given that you can hide multiple sheets at once it does seem strange that you can't do the reverse.


----------



## unorthodoxconqueror

Peter_SSs said:


> Isn't that called sheet/workbook/vba protection?



locking VBA project yes, but i find that people manage to mashup my workbooks regardless of how well i've locked down the sheets and handled the errors...


----------



## Peter_SSs

I would like to see a change to AutoFilter. I would like to see the choices "(Select All)" and "(Blanks)" either 

a) Fixed, readily accessible, in the top section like "Text Filters", "Filter by Color" etc, or

b) (2nd choice) Listed at both the top and bottom of the scroll section.

I hate scrolling all the way to the bottom to take the tick off blanks, only to find there are no blanks.
Similarly, if I'm at or near the bottom of the scroll section and decide I want to show all, I'd like to be able to do so without a lot of scrolling first.


----------



## Peter_SSs

unorthodoxconqueror said:


> locking VBA project yes, but i find that people manage to mashup my workbooks regardless of how well i've locked down the sheets and handled the errors...


Well, we really know that idiots will always beat idiot-proofing so you've asked for an impossibility.


----------



## Jon von der Heyden

RoryA said:


> You could make one in about 10 minutes...


Yeh I suppose as part of an addin.  And there are already so many addins that have similar functionality...  I would prefer it to be native though. 

I rarely have more than 5-10 sheets so the search won't be beneficial to me.  Just thought it might be nifty for those that do have large numbers of sheets in their workbooks.



Peter_SSs said:


> I would like to see a change to AutoFilter. I would like to see the choices "(Select All)" and "(Blanks)" either
> 
> a) Fixed, readily accessible, in the top section like "Text Filters", "Filter by Color" etc, or
> 
> b) (2nd choice) Listed at both the top and bottom of the scroll section.


I second that!


----------



## Domski

Turn off 'Show items deleted from the data source' in sliders by default. Stupid setting!

Dom


----------



## t.powley

I would like the ability to do the following:

1. Group worksheets (multi-level grouping also).

2. Lock worksheet tabs so they are always on screen no matter how far you scroll accross the worksheet tabs.

3. Hide/Unhide multi sheets at once.

4. Lock/Unlock multi worksheets with a password all at the same time.

5. A forumla editor that works similar to code editors, hints (already does this) and also colours formulas. For example the forumla:

<span style="color:black;"> =SUM(IF<span style="color:green;">(</span><span style="color:blue;">A1</span>>100,<span style="color:blue;">A1</span>,SUM(A1+C1)<span style="color:green;">)+B1</span>)</span>

Would look like this:

<span style="color:blue;">=SUM(<span style="color:red;">IF(A1>100,A1,<span style="color:green;">SUM(A1+C1)</span>)</span><span style="color:blue;">+B1)</span></span>

It would make it a lot clearer to edit and troubleshoot complex formula. There would also have to be the option to switch between the colour views.


----------



## Peter_SSs

When I add my macros to the QAT I would like to be able to create/edit the button *image *like I could in Excel 2003-


----------



## Dryver14

I'm sure you are aware you can edit the image for the macro button by selecting modify once you have moved the macro to the QAT selection page, as for creating your own, I think you have to download specific software.


----------



## Peter_SSs

Dryver14 said:


> I'm sure you are aware you can edit the image for the macro button by selecting modify once you have moved the macro to the QAT selection page,


Yes I am, but I don't like the available choices. 



> as for creating your own, I think you have to download specific software.


Exactly, that's the problem. 
In 2003 you could easily modify any of the standard images or create a new one on a 16x16 grid that was readily accessible by right clicking the toolbar area & choosing Customize..


----------



## Dryver14

I have to agree that the choices do seem to have been created by one of the developers children.


----------



## Peter_SSs

Dryver14 said:


> .. you can edit the image for the macro button by selecting modify once you have moved the macro to the QAT selection page,..


Whilst that is true, the problem is actually worse if you are not putting macros on the QAT, but (many of the) *built-in features*. For example, putting "Hide Rows", "Unhide Rows", "Hide Columns", "Unhide Columns", "Hide Sheet", & "Unhide Sheets" on the QAT gives you 6 identical green circles which you cannot "Modify" at all.


----------



## MARK858

I'd just like the new licensing agreements killed so I  am happy to upgrade so currently 2010 looks good to me.


----------



## RoryA

Which new licensing agreements? (There has already been one U-turn)


----------



## MARK858

Sorry Rory I didn't realize they had done a u-turn? Is it on the 1 license per pc/laptop?


----------



## RoryA

See this blog entry: Office News - Office 2013 now transferable


----------



## MARK858

Thanks for the info Rory,  much appreciated.


----------



## Robert Mika

When working on multitple worksheet and switching back to main one  - you will not get the worskeet name together with cell address when you click the cell..


----------



## Rick Rothstein

Some off the top of my head things I would like to see added to XL2016 (would be nice if they could be extended to earlier versions as well).

1) Extend the CONCATENATE function to be able to handle ranges as well as single entries (similar to how I did it in this UDF...Flexible Concatenation Function or even this one... FORMATTED Flexible Concatenation Function). Oh, and change its name to something simpler to type, like maybe JOIN.

2). Add a FIELD function similar to this one... Get Field from Delimited Text String

3) Make the MID function's work like the VBA Mid function so that its third argument is optional (when omitted, it automatically pulls the remainder of the characters in the text).

4) After making a selection of contiguous or non-contiguous cells, give us a method to deselect individual cells from the selection.

5) Extend the VBA Precedents and Dependents properties so that they work across multiple worksheets instead of making us concoct some weird code using the NavigateArrow method coupled with worksheet activations.

6) Give VB a FindAll method so that we do not have to keep constructing multi-line loops in order to move between all cells matching the find's conditions.

I am sure there are tons more, but those come to mind quickly whenever I think about what is missing in Excel.


----------



## Peter_SSs

Rick Rothstein said:


> 6) Give VB a FindAll method so that we do not have to keep constructing multi-line loops in order to move between all cells matching the find's conditions.


I'd strongly second that one!


----------



## Robert Mika

Rick Rothstein said:


> Some off the top of my head things I would like to see added to XL2016 (would be nice if they could be extended to earlier versions as well).
> 
> 1) Extend the CONCATENATE function to be able to handle ranges as well as single entries (similar to how I did it in this UDF...Flexible Concatenation Function or even this one... FORMATTED Flexible Concatenation Function). Oh, and change its name to something simpler to type, like maybe JOIN.
> 
> 3) Make the MID function's work like the VBA Mid function so that its third argument is optional (when omitted, it automatically pulls the remainder of the characters in the text).



Have they ever done enything like this?
Extend(improve) function functionality
Or rather create a new one.

A native MAXIF(s) woudl be handy as well
But then who woudl ask this question anymore...


----------



## Rick Rothstein

Robert Mika said:


> Have they ever done enything like this?
> Extend(improve) function functionality
> Or rather create a new one.


I do not have experience with early releases of Excel, but if I had to guess, I would say "Yes". There is no real good reason for FIND and SEARCH to both exist unless they were created at different times... SEARCH could easily be extended to encompass the functionality of FIND with the addition of one more argument. My guess is FIND was placed into Excel first, then later on when the additional functionality of SEARCH was deemed necessary, I think they introduced the new SEARCH function rather than modify the existing FIND function and risk breaking some kind of backward compatibility. Of course, this is only a guess on my part.


----------



## xenou

I like some of the previous ideas.  I'll add:
regular expression searching
curve sketching for statistics, so that if you provide the mean and standard deviation (or degrees of freedom for t-distributions) excel will graph the curve for you.


----------



## RoryA

I'd like PowerPivot made available as a free add-in again (or built into all versions).


----------



## Jon von der Heyden

RoryA said:


> I'd like PowerPivot made available as a free add-in again (or built into all versions).


You mean backtrack and install in prior versions?  Can they do that?

I HATE that one can't group date fields in PowerPivot pivot tables in the conventional manner!


----------



## RoryA

Jon von der Heyden said:


> You mean backtrack and install in prior versions?



No, I mean make it available in, or to, versions of Office other than ProPlus - which is the restriction in 2013.


----------



## Jon von der Heyden

RoryA said:


> No, I mean make it available in, or to, versions of Office other than ProPlus - which is the restriction in 2013.


Ah I didn't realise that it wasn't...  Makes sense to be consistent!


----------



## Scott Huish

Application.FileSearch


----------



## Robert Mika

Is anyone going to send this to Steve?


----------



## Colin Legg

A couple more (I'm constraining myself to some realistic ones from my wishlist) which spring to mind:

A Workbook.Calculate() method. It's a mystery why this doesn't exist already.

A worksheet function which let's you specify the bounds and spacing and then returns a numeric array like {1,2,3,4} or {2;4;6;8}. This would save us having to use the volatile constructs such as ROWS(1:10) etc.


----------



## Jon von der Heyden

Colin Legg said:


> A couple more (I'm constraining myself to some realistic ones from my wishlist) which spring to mind:
> 
> A Workbook.Calculate() method. It's a mystery why this doesn't exist already.
> 
> A worksheet function which let's you specify the bounds and spacing and then returns a numeric array like {1,2,3,4} or {2;4;6;8}. This would save us having to use the volatile constructs such as ROWS(1:10) etc.


Yes, definitely seconded!

Maybe I should pull this into one list.  Any chance MS would give the list any serious consideration or would I be wasting my time?


----------



## njimack

- Allow formulae as table headings. Currently you can't even link table headings to cells in another sheet.
- Stop sparklines plotting blank/zero cells


----------



## Peter_SSs

In vba, enhancements to the existing Filter function.

1. Look for an Exact match, rather than a partial match. Perhaps
*Filter(sourcesrray, match[, include[, compare[, exact]]])*

*exact* would be an optional Boolean value and the default would need to be False to allow backwards compatibility.

2. Some way to return the index(es) in the original array of the elements that are returned in the Filtered array.

Probably need to be a new function and I don't know if it is feasible but ray:

3. A function to allow filtering of arrays that contain more than one dimension.


----------



## Robert Mika

Peter_SSs said:


> In vba, enhancements to the existing Filter function.
> 
> 1. Look for an Exact match, rather than a partial match. Perhaps
> *Filter(sourcesrray, match[, include[, compare[, exact]]])*
> 
> *exact* would be an optional Boolean value and the default would need to be False to allow backwards compatibility.
> 
> It would be good as well to have the same standart Excel function with Exact match.
> ike FIND.EXACT


----------



## Peter_SSs

> It would be good as well to have the same standart Excel function with Exact match.
> ike FIND.EXACT


Perhaps I don't understand what you have in mind, but don't we already have that with one of these?

=VLOOKUP("Ted",A1:A100,1,FALSE)

=MATCH("Ted",A1:A100,FALSE)



Does make me think about VLOOKUP/HLOOKUP with "instance" though.

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup], [instance])
(default instance = 1)


so you could drag a formula like this down to look up all the instances of finding "Ted".

=VLOOKUP("Ted",A1:A100,1,FALSE,ROWS(C$2:C2))


----------



## Rick Rothstein

Robert Mika said:


> Peter_SSs said:
> 
> 
> 
> It would be good as well to have the same standart Excel function with Exact match.
> ike FIND.EXACT
> 
> 
> 
> 
> 
> 
> Peter_SSs said:
> 
> 
> 
> Perhaps I don't understand what you have in mind, but don't we already have that with one of these?
> 
> =VLOOKUP("Ted",A1:A100,1,FALSE)
> 
> =MATCH("Ted",A1:A100,FALSE)
> 
> Click to expand...
> 
> I think he means to return the position of a word, as a stand-alone word, within a larger text string. For example, find the name "Don" within "Don't talk to Don today", by-passing the letters "Don" in "Don't". To that end, a while ago, I created a function that can be used as a UDF with that particular functionality at its core... it is described in my mini-blog article here...
> 
> InStrExact - Find Location Of A Word, As A Word, Not Embedded Within Another Word
Click to expand...


----------



## Robert Mika

Rick Rothstein said:


> I think he means to return the position of a word, as a stand-alone word, within a larger text string. For example, find the name "Don" within "Don't talk to Don today", by-passing the letters "Don" in "Don't". To that end, a while ago, I created a function that can be used as a UDF with that particular functionality at its core... it is described in my mini-blog article here...
> 
> InStrExact - Find Location Of A Word, As A Word, Not Embedded Within Another Word



Yes Rick.
This is exactly what I mean.
Peter, we had many occasion to meet on s  post where you have pointed out to some of my solutions.
This function(or Rick's code) would solve many of this problem,.

BTW.Rick, I can not see your code without registering.
Do not you think that this will rather discourage people to come back?
This form of persuasion does not work for me personally.


----------



## Rick Rothstein

Robert Mika said:


> BTW.Rick, I can not see your code without registering.
> Do not you think that this will rather discourage people to come back?
> This form of persuasion does not work for me personally.


I was not aware that registration was necessary to view it... I thought that not registering simply meant you could not make a comment to the thread. This is a sub-forum that the forum owner's carved out for me to use as I like, so I use it as a mini-blog type site. I will have to check this out with the forum owners in order to see what is what. Did you register? If not, I can send you a copy of the article via email. If you want to do that, email me at rickDOTnewsATverizonDOTnet (replace the obvious with the obvious).


----------



## Peter_SSs

Robert Mika said:


> Yes Rick.
> This is exactly what I mean.


Ah, I see. First thoughts are that yes, that would be useful but I can also see a "can of worms" about how people might want it to work.

I'm sure some people would see the words below as a word, not embedded in another word, while others would not.
I too cannot see Rick's code so I don't know how his code would handle these, but in any case it doesn't really matter as Robert is wishing for a native worksheet function to do the job. 

My 'can' worksheet has a blue tab
tin.can@tinned.soup.com
It was a can-style structure
I like Ross' car


----------



## raj2206

Hi Guys,

This seems to be interesting tread.

The most common problem (working in E-Commerce Industry) is unable to concatenate an array with a assigned delimiter, which can also has the feature of ignoring the blanks. Although I have the Add-In Multicat whic is pretty useful, but would like but see it inbuild.

Thanks/Raj


----------



## Rick Rothstein

@Peter and Robert

I am having the administrators see if the can unbind my sub-forum from their over-riding rule about making people register in order to see my articles. In the meantime, for your "enjoyment", here is the article in question...

*InStrExact - Find Location Of A Word, As A Word, Not Embedded Within Another Word*<!-- google_ad_section_end -->

The InStr function is a handy tool. It will return the character position of text that is embedded within a larger text string. And while this is handy, the function is useless for finding words as stand-alone words in the text. Let's say you had this text string...

TextString = "Don't tell me, but your name is Don, right?"

and you wanted to find the character position of the name Don in that text. If you used the InStr function like this...

MsgBox InStr(TextString, "Don")

It would return the number 1 because it would find the letters "Don" inside the word "Don't" and never look further into the text to see if Don existed as a stand-alone word or not. Even using InStr's optional arguments and having it perform a binary search would not help because the letter casing for the first 3 characters of "Don't" is the same a for the name "Don". Here is a function which can be called from your <ACRONYM title=vBulletin>VB</ACRONYM> code or which *can be used as a UDF *on a worksheet, if desired, that will find text as stand-alone words that are not embedded as part of other words...



		Code:
__


Function InStrExact(Start As Long, SourceText As String, WordToFind As String, _
                    Optional CaseSensitive As Boolean = False, _
                    Optional AllowAccentedCharacters As Boolean = False) As Long
  Dim x As Long, Str1 As String, Str2 As String, Pattern As String
  Const UpperAccentsOnly As String = "ÇÉÑ"
  Const UpperAndLowerAccents As String = "ÇÉÑçéñ"
  If CaseSensitive Then
    Str1 = SourceText
    Str2 = WordToFind
    Pattern = "[!A-Za-z0-9]"
    If AllowAccentedCharacters Then Pattern = Replace(Pattern, "!", "!" & UpperAndLowerAccents)
  Else
    Str1 = UCase(SourceText)
    Str2 = UCase(WordToFind)
    Pattern = "[!A-Z0-9]"
    If AllowAccentedCharacters Then Pattern = Replace(Pattern, "!", "!" & UpperAccentsOnly)
  End If
  For x = Start To Len(Str1) - Len(Str2) + 1
    If Mid(" " & Str1 & " ", x, Len(Str2) + 2) Like Pattern & Str2 & Pattern Then
      InStrExact = x
      Exit Function
    End If
  Next
End Function


This function takes three required arguments and provides for two additional optional arguments. The first argument, named Start, is required and lets you set the starting character number for the search to begin at (use 1 to start the search at the first character). The second argument, named SourceText, is required and is the text to look for the word in. The third argument, named WordToFind, is required and, as the name indicates, is the word whose position you want to locate. The fourth argument, named CaseSensitive, is optional and allows you to make the search case sensitive or not (the default is FALSE representing a case insensitive search). The fifth argument, named AllowAccentedCharacters, is optional and allows certain accented letters to be considered as being part of a word (the default is False meaning only normal ASCII characters can make up the word). For example, with the default value of False, the word "resumé" would never be found because the accented "e" (é) would be considered a non-letter. Setting the fifth argument to True would allow the function to find the word "resumé". I only allowed for three accented letters "ç", "é" and "ñ" (in both lower and upper case), but you can add more if you wish by modifying the UpperAccentsOnly and UpperAndLowerAccents constants (the Const statements at the beginning of the code).<!-- google_ad_section_end -->


----------



## raj2206

That's really amazing!!


----------



## Rick Rothstein

raj2206 said:


> That's really amazing!!


Thank you very much for your nice comment Raj, I really appreciate it.




Peter_SSs said:


> I too cannot see Rick's code so I don't know how his code would handle these, but in any case it doesn't really matter as Robert is wishing for a native worksheet function to do the job.
> 
> My 'can' worksheet has a blue tab
> tin.can@tinned.soup.com
> It was a can-style structure
> I like Ross' car


In case you haven't test the code I posted yet, I'll just let you know that my InStrExact function (correctly I presume) finds the position of the word "can" in each of those example texts (except for the last one which does not contain the word "can"... it returns 0 for that one) because it is not embedded within another word (such as would be the case if the word "scant" were included in the text).


----------



## Robert Mika

Thank you Rick.


----------



## Rick Rothstein

Robert Mika said:


> Thank you Rick.


You are quite welcome... hopefully you will find the function useful when you are in need of its particular functionality.

And I want to thank you back for alerting me to the registration requirement to see the code in my mini-blog articles... I really was completely unaware of it. I have asked the forum administrators there to look into it. They have that register-to-see-code requirement as a policy for their full forum but they are checking if they can free my sub-forum out from under that requirement. If they can't, I have a possible work-around, but I don't know what the ramification would be of implementing it. I did a test yesterday and noted that if I change the code tags that surround the code to HTML tags (HTML is what's in the brackets), then the code appears to display correctly (all spacing remains in tact) and is visible without registering, but I do not know if using the HTML tags will, or could, misinterpret some VB code syntax for whatever it is that the HTML tags look for to process, so I am hesitant to simply plow through and make the change until I hear back from the forum administrators.


----------



## Robert Mika

You are welcome Rick.
I registered on the Fox a long time ago but has not participated yet.
I saw that ExcelForum has implemented the same policy - no registration - no visibility.
(In some cirsumstances even formulas are not visible).
I hope that MrExcel will remain " see free" forum.


----------



## Rick Rothstein

Robert Mika said:


> You are welcome Rick.
> I registered on the Fox a long time ago but has not participated yet.
> I saw that ExcelForum has implemented the same policy - no registration - no visibility.
> (In some cirsumstances even formulas are not visible).
> I hope that MrExcel will remain " see free" forum.


I'll thank you one last time (without your comment, I would never have known there was a problem)... the administrator have implemented a fix and my portion of the ExcelFox forum is now completely visible for all to see (you still have to register to leave a comment, but that is acceptable to me). So you, and everyone else, are now free to see all the "words of wisdom" I have posted over there. If you (or anyone else) is of a mind to see what I have posted, here is a link to my sub-forum (mini-blog) site...

Rick Rothstein's Corner


----------



## gkoehler

I'd like to see 
1) an option for undercase am/pm label.  Can do it now with Text function but very inefficient.
2) DSUM, DMAX, DAVERAGE etc. functions that you can define without having to specificy a fix column span value that breaks the formula when you insert columns.  Having to keep track of these creates a lot of work.  Why can't they be dynamic like other formulas?


----------



## gkoehler

I meant be able to use DSUM etc. without having to set names, i.e. use column counter but have that number dynamically update when you add or subtract columns.


----------



## Darren Bartrup

A one stop 'Where's the last cell' function in VBA or as a formula, rather than the UsedRange with it's problems before saving, or go to the bottom of the sheet and move up, or FIND the last cell.


Concatenate across a range with optional delimiter.
Do something with merged cells & center across selection.


----------



## Smitty

> I meant be able to use DSUM etc. without having to set names, i.e. use column counter but have that number dynamically update when you add or subtract columns.



Why not use SUMIF instead?


----------



## Scott Huish

I couldn't get that to work, it returned 1:

Excel WorkbookAB1Don't tell me, but your name is Don, right?1Sheet1





Rick Rothstein said:


> @Peter and Robert
> 
> I am having the administrators see if the can unbind my sub-forum from their over-riding rule about making people register in order to see my articles. In the meantime, for your "enjoyment", here is the article in question...
> 
> *InStrExact - Find Location Of A Word, As A Word, Not Embedded Within Another Word*<!-- google_ad_section_end -->
> 
> The InStr function is a handy tool. It will return the character position of text that is embedded within a larger text string. And while this is handy, the function is useless for finding words as stand-alone words in the text. Let's say you had this text string...
> 
> TextString = "Don't tell me, but your name is Don, right?"
> 
> and you wanted to find the character position of the name Don in that text. If you used the InStr function like this...
> 
> MsgBox InStr(TextString, "Don")
> 
> It would return the number 1 because it would find the letters "Don" inside the word "Don't" and never look further into the text to see if Don existed as a stand-alone word or not. Even using InStr's optional arguments and having it perform a binary search would not help because the letter casing for the first 3 characters of "Don't" is the same a for the name "Don". Here is a function which can be called from your <ACRONYM title=vBulletin>VB</ACRONYM> code or which *can be used as a UDF *on a worksheet, if desired, that will find text as stand-alone words that are not embedded as part of other words...
> 
> 
> 
> Code:
> __
> 
> 
> Function InStrExact(Start As Long, SourceText As String, WordToFind As String, _
> Optional CaseSensitive As Boolean = False, _
> Optional AllowAccentedCharacters As Boolean = False) As Long
> Dim x As Long, Str1 As String, Str2 As String, Pattern As String
> Const UpperAccentsOnly As String = "ÇÉÑ"
> Const UpperAndLowerAccents As String = "ÇÉÑçéñ"
> If CaseSensitive Then
> Str1 = SourceText
> Str2 = WordToFind
> Pattern = "[!A-Za-z0-9]"
> If AllowAccentedCharacters Then Pattern = Replace(Pattern, "!", "!" & UpperAndLowerAccents)
> Else
> Str1 = UCase(SourceText)
> Str2 = UCase(WordToFind)
> Pattern = "[!A-Z0-9]"
> If AllowAccentedCharacters Then Pattern = Replace(Pattern, "!", "!" & UpperAccentsOnly)
> End If
> For x = Start To Len(Str1) - Len(Str2) + 1
> If Mid(" " & Str1 & " ", x, Len(Str2) + 2) Like Pattern & Str2 & Pattern Then
> InStrExact = x
> Exit Function
> End If
> Next
> End Function
> 
> 
> This function takes three required arguments and provides for two additional optional arguments. The first argument, named Start, is required and lets you set the starting character number for the search to begin at (use 1 to start the search at the first character). The second argument, named SourceText, is required and is the text to look for the word in. The third argument, named WordToFind, is required and, as the name indicates, is the word whose position you want to locate. The fourth argument, named CaseSensitive, is optional and allows you to make the search case sensitive or not (the default is FALSE representing a case insensitive search). The fifth argument, named AllowAccentedCharacters, is optional and allows certain accented letters to be considered as being part of a word (the default is False meaning only normal ASCII characters can make up the word). For example, with the default value of False, the word "resumé" would never be found because the accented "e" (é) would be considered a non-letter. Setting the fifth argument to True would allow the function to find the word "resumé". I only allowed for three accented letters "ç", "é" and "ñ" (in both lower and upper case), but you can add more if you wish by modifying the UpperAccentsOnly and UpperAndLowerAccents constants (the Const statements at the beginning of the code).<!-- google_ad_section_end -->


----------



## Rick Rothstein

Scott Huish said:


> I couldn't get that to work, it returned 1:
> 
> *Sheet1*
> 
> 
> AB1Don't tell me, but your name is Don, right?1
> 
> <tbody>
> 
> </tbody>
> 
> *Spreadsheet Formulas*
> CellFormulaB1=InStrExact(1,A1,"Don")
> 
> <tbody>
> 
> </tbody>
> 
> <tbody>
> 
> </tbody>
> 
> *Excel tables to the web >> * Excel Jeanie HTML 4


I could not get it to work either. 

See the revised code in my mini-blog article here...

*InStrExact - Find Location Of A Word, As A Word, Not Embedded Within Another Word*


----------



## dannyfry

A more user friendly HLOOKUP and VLOOKUP because they always trip me up


----------



## Scott Huish

XLOOKUP to replace INDEX MATCH MATCH


----------



## Smitty

Scott Huish said:


> XLOOKUP to replace INDEX MATCH MATCH



But INDEX/MATCH/MATCH can make you look so cool!


----------



## Scott Huish

Native Regular Expressions functions.


----------



## Fazza

I'd like to see



conditional formatting revert back to the basic three options (such as in Excel 2003)
pivot tables revert back to Excel 2003
simpler data connections (so more users can do this easier,though it is OK for me as is) for queries & pivot tables
simpler queries and database-like functionality - so users wouldn't need formulas for things like INDEX/MATCH or multiple lookups, and also users could avoid array formulas. Array formulas IMO are the biggest mess in Excel ATM
database like reporting. So from a query or table in Excel one could go straight to an Access type report. This did exist ~20 years ago but was removed
menu structure revert to Excel 2003 style
print preview revert to Excel 2003
removal of merged cells functionality!
data forms like Access forms with their simple, powerful & handy built-in filtering, sorting, etc


Or from another perspective, revert Excel back to Excel 2003 specifications & leave it there (including lower row & column limits), develop Access (wizards or whatever it takes) so that it is easier for spreadsheet users to transition to databases.


----------



## Taul

Fazza said:


> I'd like to see
> 
> 
> 
> conditional formatting revert back to the basic three options (such as in Excel 2003)
> pivot tables revert back to Excel 2003
> simpler data connections (so more users can do this easier,though it is OK for me as is) for queries & pivot tables
> simpler queries and database-like functionality - so users wouldn't need formulas for things like INDEX/MATCH or multiple lookups, and also users could avoid array formulas. Array formulas IMO are the biggest mess in Excel ATM
> database like reporting. So from a query or table in Excel one could go straight to an Access type report. This did exist ~20 years ago but was removed
> menu structure revert to Excel 2003 style
> print preview revert to Excel 2003
> removal of merged cells functionality!
> data forms like Access forms with their simple, powerful & handy built-in filtering, sorting, etc
> 
> Or from another perspective, revert Excel back to Excel 2003 specifications & leave it there (including lower row & column limits), develop Access (wizards or whatever it takes) so that it is easier for spreadsheet users to transition to databases.



While you're at it, remove the ribbon


----------



## Peter_SSs

Fazza said:


> I'd like to see
> 
> 
> print preview revert to Excel 2003


For this one, just pop the 'Print Preview Full Screen' icon/command onto your Quick Access Toolbar.


----------



## Fazza

Peter_SSs said:


> For this one, just pop the 'Print Preview Full Screen' icon/command onto your Quick Access Toolbar.



Thank you, Peter. I don't yet know/use Quick Access Toolbar. Or other toolbars. Just keyboard shortcuts - as much as possible anyway. I'll investigate the Quick Access Toolbar.


----------



## mfexcel

For this, I will use "Clear Filter From..." to show all.



Peter_SSs said:


> Similarly, if I'm at or near the bottom of the scroll section and decide I want to show all, I'd like to be able to do so without a lot of scrolling first.


----------



## mfexcel

Didn't know that.  It's really good to know it.  Thanks.



Peter_SSs said:


> For this one, just pop the 'Print Preview Full Screen' icon/command onto your Quick Access Toolbar.


----------



## Peter_SSs

Fazza said:


> Thank you, Peter. I don't yet know/use Quick Access Toolbar. Or other toolbars. Just keyboard shortcuts - as much as possible anyway. I'll investigate the Quick Access Toolbar.


If you want to use a keyboard shortcut (I'm more a mouse user as my keyboard skills are poor) then add the command to the QAT as I described & move it high in the list (in the top 9 is best for frequently used items), say to position 3 on the QAT. The keyboard shortcut then will be Alt-3.

If you have more than 9 items on the QAT then they will be assigned slightly longer shortcuts (eg Alt-0C), but in a pattern. To see the keystrokes that go with the Alt, just press the Alt key and look at the QAT.


----------



## Peter_SSs

mfexcel said:


> For this, I will use "Clear Filter From..." to show all.


Thanks, yes, I eventually tweaked to that.


----------



## DocAElstein

Wot I would like to see:
.  An extra argument option in the Advanced Filter, (argument:- unique) , that is to say instead of just Unique an additional UniqueWithoutHeadings 
.  It catches a lot of  people out that this Method applied to a Column of data filters starting at the second row, and simply copies the first row  a s a heading to the filtered data column. (  http://www.mrexcel.com/forum/excel-...d-filter-unique-values-only.html?#post3920344 ). If you did not know that (as I did not yesterday!) you could think the copied heading is just a filtered unique piece of data!! Of course as a beginner I may be wrong and there is such an optional argument? – anyone knows about that?)
Alan


----------



## gsistek

-The ability to resize the 'Evaluate Formula' window.
-The ability to more easily determine formats of conditionally formatted cells (especially via VBA)


----------



## RoryA

gsistek said:


> -The ability to more easily determine formats of conditionally formatted cells (especially via VBA)



That's already here with the DisplayFormat introduced in 2010.


----------



## Peter_SSs

gsistek said:


> -The ability to resize the 'Evaluate Formula' window.


I'd definitely vote for that one!


----------



## Peter_SSs

Improvement to the INDEX function so you could get it to (easily) return a null string instead of a zero if the target cell is empty.
So we don't have to do constructs like
=IF(INDEX(A1:A6,3)="","",INDEX(A1:A6,3))


----------



## shift-del

Hello Peter



Peter_SSs said:


> Improvement to the INDEX function so you could get it to (easily) return a null string instead of a zero if the target cell is empty.
> So we don't have to do constructs like
> =IF(INDEX(A1:A6,3)="","",INDEX(A1:A6,3))



But that applies to every lookup function.
So it should be a more general approach like: IFEMPTY() or IFNULL().


----------



## Peter_SSs

shift-del said:


> Hello Peter
> 
> 
> 
> But that applies to every lookup function.
> So it should be a more general approach like: IFEMPTY() or IFNULL().


True. Would be useful even for something as simple as =A1


----------



## DocAElstein

Peter_SSs said:


> True. Would be useful even for something as simple as =A1


 
…a bit along these lines .  Some way of truly getting a cell or range to be seen by Excel as it was as if nothing had ever been done to it. This seems to be a fundamental problem with Excel not being truly/ fully able to „forget“!. (Maybe deleting comes close sometimes). Even as a beginner I see lots of inconsistent problems finding last cells, empty cells, as well as strange inconsistent memory increases in File size that do not tie up with wot is there, after clearing, deleting etc. So a command like Cells(y,x).Forget  would be good

Alan Elston


----------



## Fazza

Obviously VBA can be used to make cells empty.

Also queries do this. There are many possibilities. Along the lines of lookup type situations,

UPDATE table A INNER JOIN another_table B ON A.common_field = B.common_field
SET A,whatever = B.whatever

Using queries instead of INDEX/MATCH removes formulas and works well with nulls.

UPDATE table A INNER JOIN another_table B ON A.common_field = B.common_field
SET A,whatever Null
WHERE some_condition

Many questions & complex formulas are caused by the poor data structures used.


----------



## DocAElstein

Fazza said:


> Obviously VBA can be used to make cells empty….. Along the lines of lookup type....


 
Sorry, I am a complete beginner and with Look Up things  I have no experience.



DocAElstein said:


> …….lots of inconsistent problems finding last cells, empty cells, as well as strange inconsistent memory increases in File size that do not tie up with wot is there, after clearing, deleting etc. So a command like Cells(y,x).Forget would be good..


 

.  Can you tell me please if there IS a way to achieve this, that is to say set a cell or range to exactly as if it was never touched?. It is not such a problem for me now, as I have now , thanks  amongst other things to help from this forum (http://www.mrexcel.com/forum/excel-...%3D-y-%96-1-usedrange-rows-count-anomale.html ),  just about got up on the different ways of finding the last bit, or end of something. But originally, as many beginners, I was continually confused with wot exactly for example UsedRange was talking about..


.   I am continually overwhelmed by what is possible in VBA but at the same time frustrated because of lack of experience in finding out exactly how! – And the Microsoft Help Functions, F1, F2 Libraries, etc. do not seem to be keeping up with things! The help from you experienced users in this Forum is invaluable. - In my day we had command reference books. That does not seem to be possible for Microsoft these days because the possibilities (and resultant Book Weight!) are just too big!? – Clearly no-one person knows everything. – (Some of you guys come closest).
Alan.

P.s.  *Re: What I would like to see in Excel 2016: *Improved Help Functions!


----------



## XOR LX

shift-del said:


> But that applies to every lookup function.
> So it should be a more general approach like: IFEMPTY() or IFNULL().



Indeed! Or even an IFZERO(). 

I often use IFERROR() in these cases so as not to have to repeat the main clause, especially if that clause is an extremely long one.

Sometimes this is because of a particularly long filepath in the formula (e.g. to a closed workbook), which means that a repetition of the main clause makes even a simple VLOOKUP hard to digest.

But more often it's because I have a formula construction made up of a long series of functions which results in an array from which I'd like to exclude zeroes (or some other value), before passing on to a final function for processing. 

Then, rather than repeating the whole main clause again I employ a construction with IFERROR, taking advantage of the fact that only zero will produce an error when reciprocated with unity, along the lines of:

=IFERROR(1/(1/(...

See here for more:

http://excelxor.com/2014/08/29/iferror-techniques-for-excluding-certain-values-from-results/

Regards


----------



## Rick Rothstein

XOR LX said:


> Then, rather than repeating the whole main clause again I employ a construction with IFERROR, taking advantage of the fact that only zero will produce an error when reciprocated with unity, along the lines of:
> 
> =IFERROR(1/(1/(...


I like this idea... have you ever encountered any Excel induced rounding issues with the double reciprocal such that X ends up not equaling 1/(1/X)?


----------



## XOR LX

Rick Rothstein said:


> I like this idea... have you ever encountered any Excel induced rounding issues with the double reciprocal such that X ends up not equaling 1/(1/X)?



Thanks. And a good question. 

I'm quite surprised myself that I seem to have not yet had the need to add a rounding function when using this construction, especially when using the square/square-root one (or maybe the chance of rounding issues is _less_ with whatever algorithm Excel uses for these calculations?).

I guess to be rigorous I should stop checking each case on a one-by-one basis and accept that it's bound to happen sooner or later (though like I say, it - strangely - still hasn't yet!). In which case I should start wrapping it in some rounding function as default.

Regards


----------



## theBardd

Domski said:


> Turn off 'Show items deleted from the data source' in sliders by default. Stupid setting!



Turn off Autoifit column widths on update in pivots. Stupid setting!


----------



## Darren Bartrup

Find and Replace only in cell references.
Sometimes dragging a formula over just won't give you the cell references you need, and sometimes there's a mismatch of absolute and relative references in a formula.

I want to be able to update the reference to column C to column M without my COUNTIF turning to a MOUNTIF (queue the double-entendre).


----------



## RoryA

They could just add regex support to the Find/Replace operation.


----------



## AusSteelMan

Peter_SSs said:


> I would like to see a change to AutoFilter. I would like to see the choices "(Select All)" and "(Blanks)" either
> 
> a) Fixed, readily accessible, in the top section like "Text Filters", "Filter by Color" etc, or
> 
> b) (2nd choice) Listed at both the top and bottom of the scroll section.
> 
> I hate scrolling all the way to the bottom to take the tick off blanks, only to find there are no blanks.
> Similarly, if I'm at or near the bottom of the scroll section and decide I want to show all, I'd like to be able to do so without a lot of scrolling first.



Absolutely Peter!  Although it may not seem a big deal, as soon as you have a large data set scrolling all the way to the bottom to tick blanks (after foregting to untick All at the top!) just uses lots of time when repeating this task all-day.

Cheers,
Darren


----------



## AusSteelMan

Robert Mika said:


> A native MAXIF(s) woudl be handy as well
> But then who woudl ask this question anymore...



Seconded


----------



## kylefoley76

I would like to see a vba editor that alphabetizes the declarations.  I sometimes have 100 declarations and I cannot find them in the vba locals windows because there are so many of them.


----------



## mfexcel

Would be great if there is a NETWORKHOURS function.


----------



## Scott Huish

.FindAll in VBA that would return a collection you could either loop through or apply the same action to all at once.



		Code:
__


Sub test()
Dim c As Range
Set c = Range("C:C").FindAll("findthis")
c.Copy Range("E:E")
End Sub


That would be awesome.


----------



## Peter_SSs

Scott Huish said:


> .FindAll in VBA ...


I'm agreeing with you too, just like I did with Rick earlier.


----------



## J.Ty.

1. Make arrays first-class citizens. At the moment I can produce an array formula which creates and handles array values, I can type {1;...;5} into a formula or make it an assigned name, but I cannot simply store such an array in a cell in a way which makes it accessible later.  This would tremendously increase the power of Excel, and it is almost there.

2. Improve the formula editor in Data Validation, Conditional Formatting, etc. The present one is ridiculous.

3. Introduce sorting, duplicate removal, filtering and Text-to-Columns as array formulas, very much like Google spreadsheet does to the first three. 

4. Improve data tables to let them serve as User Defined Functions, without the need to use VBA. LibreOffice is presently better than Excel in this respect.


J.Ty.


----------



## Robert Mika

Is out for the last few weeks:

https://products.office.com/en-us/office-2016-preview


----------



## DocAElstein

Robert Mika said:


> Is out for the last few weeks?
> 
> https://products.office.com/en-us/office-2016-preview




Yes, the preview appears to be
Office 2016 public preview now available | Excel Matters


----------



## deletedalien

Jon von der Heyden said:


> Here's what I want:
> 
> 
> 
> 
> The ability to multi-select sheets from the Unhide sheets dialog so that I can unhide more than 1 at a time.








		Code:
__


Sub UnhideAllSheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Visible = xlSheetVisible
Next ws
End Sub


Save it to your personal workbook
Then just Customize the ribbon and add it there..

that's what i did.... 
if i wait for microsoft it could take a while....


----------



## MARK858

@deletedalien, I don't think that was exactly what Jon meant (he is probably aware how to loop through sheets with code  ), I think you will find Jon wanted to be able to manually select multiple individual sheets in the unhide sheets dialog... think the equivalent of holding Ctrl and then clicking individual cells in a spreadsheet.


----------



## deletedalien

MARK858 said:


> @deletedalien, I don't think that was exactly what Jon meant (he is probably aware how to loop through sheets with code  ), I think you will find Jon wanted to be able to manually select multiple individual sheets in the unhide sheets dialog... think the equivalent of holding Ctrl and then clicking individual cells in a spreadsheet.




ohhh ok ok ok....


Then....  No.  hahahaha


----------



## RoryA

MARK858 said:


> (he is probably aware how to loop through sheets with code  )



I wouldn't be too sure about that - you'd be amazed at some of the things he doesn't know...


----------



## MARK858

RoryA said:


> I wouldn't be too sure about that - you'd be amazed at some of the things he doesn't know...



If that is the case I would suggest getting more practice on one of those Excel forums you hear about. Now if I can just remember where I last saw one....


----------



## deletedalien

MARK858 said:


> If that is the case I would suggest getting more practice on one of those Excel forums you hear about. Now if I can just remember where I last saw one....



http://www.excelforum.com/ :D

I'm getting banned huh?.......


----------



## MARK858

Probably not as they tend to not over-react here but maybe not the wisest move to test that.


----------



## deletedalien

MARK858 said:


> Probably not as they tend to not over-react here but maybe not the wisest move to test that.



LOL


----------



## deletedalien

well....

My account was hacked by someone in THAT forum and posted that stuff up there ^^ hahahahaha


----------



## Smitty

deletedalien said:


> I'm getting banned huh?.......





> Location: Tijuana Mexico



I dunno, a bottle of good tequila might convince Rory and Jon to keep you around.


----------



## deletedalien

I know a friend named "DON JULIO" who could help me with that


----------



## Scott Huish

An N function that would work with arrays.


----------



## XOR LX

Scott Huish said:


> An N function that would work with arrays.



It already does, doesn't it?

Regards


----------



## Scott Huish

XOR LX said:


> It already does, doesn't it?
> 
> Regards



I've never been able to get it to do that. If it did, I would expect the first formula to return 3 (the value of the text which is 0 plus the two numbers). The regular count of course returning 2

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt;  padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px;  " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >H</td><td >I</td><td >J</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >a</td><td style="text-align:right; ">1</td><td style="text-align:right; ">7</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >*</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >*</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">1</td><td style="text-align:right; ">2</td><td >*</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >H7</td><td >{=COUNT(N<span style=' color:008000; '>(H4:K4)</span>)}</td></tr><tr><td >I7</td><td >=COUNT(H4:K4)</td></tr></table></td></tr><tr><td ><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>Formula Array:</span><span style=' font-family:Arial,Arial; font-size:9pt;'><br />Produce enclosing </span><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>{ }</span><span style=' font-family:Arial,Arial; font-size:9pt;'> by entering<br />formula with CTRL+SHIFT+ENTER!</span></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span>


----------



## XOR LX

Ah, yes. I see what you mean. I'm so used to N being used in conjunction with some other, appropriate function that I forget that, on its own, it struggles to coerce an array of returns.

But we need to be a bit more specific: it's not so much that N "won't work with arrays". It's more that it is not able to operate over an array which comprises a range of cells _within the actual worksheet_, without prior "deferencing" of that range.

For example, there is no problem whatsoever if we attempt:

=COUNT(N({"a",1,7}))

(which does not even require CSE).

If those elements are contained within actual _cells_, however, as in your example, we need to first "deference" them to their actual values. Often we see this when N is used in conjunction with e.g. OFFSET or INDIRECT, though there are much simpler methods. For example, with H4:J4 contained those values as above ("a", 1 and 7), a simple coercion to numerics, i.e.:

=COUNT(N(0+H4:J4))

with CSE, will resolve to:

=COUNT(N({#VALUE!,1,7}))

and then to:

=COUNT({#VALUE!,1,7})

i.e. 2.

However, this is not quite what you want here, since N("a") should be 0, not an error, as here.

One way to "dereference" ranges into their actual values, whilst retaining the datatype of those values, is:

=COUNT(N(IF({1},H4:J4)))

(No CSE required this time.)

And this nicely coerces (or "deferences") the values in the range H4:J4 into their cell contents, which can then be passed to N.

I take your point, though. It would be nice if N could have this property all on its own, without requiring such additional coercion.

And this is not the only function which behaves like this. DOLLARDE, DEC2BIN, IMSUM and CELL are just a few more examples. For example, with H4:J4 containing 1, 2 and 3:

=SUMPRODUCT(DOLLARDE(H4:J4,5))

will return #VALUE!, though:

=SUMPRODUCT(DOLLARDE(0+H4:J4,5))

correctly returns 6.

Regards


----------



## steve case

Stop plotting empty cells as zero


Allow a choice to disable the date function (the date functions has caused me MANY errors.)


Allow a choice to disable those annoying flags that cover up my data


Allow choice of older formats.  I don't use Excel 2013 because too many buttons were moved and re-named.  Simple things in the graphic section became more complicated. Do a key stroke count sometime. 


See Fazza's post #70


----------



## Scott Huish

> Allow a choice to disable those annoying flags that cover up my data



If you mean the green error flags, there already is a choice for that.
Go into Excel Options, Formulas. Uncheck Enable Background Error Checking. Click OK.


----------



## steve case

Scott Huish said:


> If you mean the green error flags, there already is a choice for that.
> Go into Excel Options, Formulas. Uncheck Enable Background Error Checking. Click OK.



Thanks for the tip, that might get rid of some of them.


----------



## bbott

Intellisense for UDFs.


----------



## petertenthije

Change the workday formula so that it does not require full days. For instance WORKDAY(startdate;1,5;holidays).


----------



## Worf

Don't know if this was already mentioned: a resizable dialog box for conditional formatting rules.


----------



## Worf

Cells that do not loose conditional formatting when something is pasted over them.


----------



## JVBEaupre

I'm trying to keep alive code for 2010 (Windows), 2011(Mac) and 2016(Mac) versions of Excel, so my interest is mainly VBA stuff: 

1. Bring back fully functional DIR functions
2. Ungray PlotArea sizes so that changes can be accomplished by VBA and recorded!
3. Use the MacOS path syntax. Preview copy uses "/" and not ":" and other changes.
4. Bring back Dialogs or otherwise improve file handling. It's ridiculous to use Applescript to open files or find paths to folders & files
5. Improve VBA documentation over the existing for 2011 and 2013. Method examples are very helpful and should be included.
6. The VBA editor (I know, I know, is not final) but does not bode well. I hope MS does something useful with that. Needs better tabbing, object browser, help, and multi-module window capability.
7. Most recent "bomb" caused by Applicationworkbookfunction.Percentile(.5). 
8. Something has also changed in intepreting the content of variants: val() is now lowercase and often bombs.

I guess the Office 2007 VBA was the last fully functional VBA--It's been all downhill since then.
Jim


----------



## RoryA

This thread was actually started in relation to the Windows version of Excel - it is _extremely_ unfortunate that MS has chosen to use the same version number for both Mac and Windows!
I think almost all of your comments relate to the Mac version, though I completely disagree with your last comment. Office 2007 was appalling and its VBA (macro recorder aside) was no better or worse than subsequent versions - I'd be intrigued to know why you think it was better than say 2010?


----------



## JVBEaupre

RoryA said:


> This thread was actually started in relation to the Windows version of Excel - it is _extremely_ unfortunate that MS has chosen to use the same version number for both Mac and Windows!
> I think almost all of your comments relate to the Mac version, though I completely disagree with your last comment. Office 2007 was appalling and its VBA (macro recorder aside) was no better or worse than subsequent versions - I'd be intrigued to know why you think it was better than say 2010?



Rory,
Yes, my comments were about the Mac 2016 Excel. 
In 2010 the examples dropped out of the Help info. As for the rest, the functionality (in memory) was about the same, except for "grayed out" controls. I think 2010 was the 1st one where (if memory serves) that ".PlotArea.Width = ..." didn't work.
Anyway, it was so long ago, I'm not going to the wall with my impressions.
Thanks for the comments.
jim


----------



## Terp1

The ability to undo after running a macro.  I generally avoid macros because I can't undo afterwards.  (If I can do this already, please let me know).


----------



## DocAElstein

Terp1 said:


> The ability to undo after running a macro.  .....  (If I can do this already, please let me know).



.    Like many people I guess, It has become an instinct with me to save the file just before running a macro, at least when I am developing a macro and testing it. ( Then,  of course, if something goes wrong after a macro, you just close the file without saving changes and open it again, and the wrong doings of the macro are gone! ).  (Also I mostly run in debug ( F8 ) mode while developing a macro, that minimises the damage a macro can do - you stop then as soon as anything goes wrong, and often damage is small enough to repair manually before going further. )

Alan

P.s. While I am here:  Wot I would most like to see in XL 2016.  - I would like it not to come out. I would rather they update, consolidate and get compatibility between existing versions better.


----------



## Smitty

Terp1 said:


> The ability to undo after running a macro.  I generally avoid macros because I can't undo afterwards.  (If I can do this already, please let me know).



You can, but's it's not necessarily all that efficient.  See: http://spreadsheetpage.com/index.php/tip/undoing_a_vba_subroutine/ 

But like Doc said, the best bet is to work on a copy of your wb until you get your code doing what you want (not what you tell it to do...)  Avoiding VBA because of the lack of undo is a silly excuse to not avail yourself of such an awesome resource!



> I would rather they update, consolidate and get compatibility between existing versions better.



Yeah, that's gonna' happen...


----------



## Fazza

*Minor issue*

I'm using Excel 2013 in my current workplace. When using basic filtering, one handy thing is being able to drag the filtering window. Annoyingly, when next filtering the window has reset to its default. It'd be good if it remembered its previous re-sizing.

The name manager does remember its resize.


----------



## Drrellik

*Re: Minor issue*

I would like to be able to turn on and off sheet calculations under the cell properties for each cell. 
would help non VBA users with dates and times only updating when they wanted them to.


----------



## Scott Huish

*Re: Minor issue*

Customizable User Interface Color Schemes


----------



## mfexcel

*Re: Minor issue*

Intelligence to detect when a user wants to input a date or a text.  For example, if a user input "Oct 15", Excel asks the user whether s/he wants to input
1) Date as: 1st October, 2015
2) Date as: 15th October of current year
3) Text as "Oct 15"
Option: Remember this option for the rest of the column.


----------



## Drrellik

*Re: Minor issue*

How about the Insert Function be able to assist with combined functions.  so when you enter an argument for the INDEX function and for your Row you type Match and the Function argument would understand and continue to assist you are you walked through you formula.   and not just for that one example but for any argument.


----------



## Terp1

*Re: Minor issue*

Ability to change the Alt shortcut key for items in the ribbon without using VBA or an add-in.  E.g., change Paste Special from Alt-H-V to Alt-A-Z.


----------



## dhsilv2

*Re: Minor issue*

Count unique ifs!
Allow a user to filter based on another range.  For example if I have 500 accounts I want filtered out of 100,000 rows, I can just select them instead of doing something like a vlookup or match to find non N/A's and then filter (the number of times a day people do this is absurd)
Create a basic VBA UI to allow users to start creating macros without knowing VBA.  If Access can create SQL code based on a UI, there's no reason excel can't allow at least basic functionality.  
Many to many joins on pivot tables and power pivot (or remove both and force users to use a database tool).  
Improve performance or Arrays.  something like {sum(if(if())} instead of saying adding 4 sumifs to some 4 columns is really easy to do, and honestly easier to read, but performance can be horrible.


----------



## DocAElstein

.  Hallo.
Good Day to you.
.......
As a general improvement / advancement I would like to see would be to increase the “strange” limit of 255 characters in the VBA Evaluate Method
=Evaluate( StringArgument )
, which ( I find strange ) applies both to the StringArgument and the Output string
http://www.mrexcel.com/forum/excel-...ons-evaluate-method-255-character-limit.html?

.. maybe there is some fundamental limit here and it is inappropriate for a “simple “ version Update. ??

......................
Any comments? To aid, - 
.  If I may a short clarification:
.  If  I understand correctly, the simplest explanation of the *VBA Evaluate Method* is that it will act on its string argument as if it were a Spreadsheet formula in a cell. So like
=Evaluate(“ Put in here  what would be in the spreadsheet cell ” )
...
..
But it has the extra advantage over the Spreadsheet that you can include some VBA Code in that argument to help build up that string argument...

=Evaluate(“ Put in here  what would be in the spreadsheet cell ” “ & Bit of VBA Code & “ ” )

.... so really powerful,  or would be if not having that character limit.
.
.   Wot I mean .. consider this code Sub AlanEvaluate() where I attempt to mimic the VBA Evaluate Method. It makes a very long string formula, pastes that into the cell and then retrieves the spreadsheet  evaluated answer,  ( a bit more detail to the code is given in the above Thread reference. )
.   The equivalent  VBA Evaluate Method , line ,  at line number 120 fails due to the 255 limit ( in this example I am talking about the 255 Limit to the string actually seen by VBA in the =Evaluate(  ) code line )

Code:



		Rich (BB code):
__


Sub AlanGotEvaluate()
10  Dim wks As Worksheet: Set wks = ThisWorkbook.Worksheets("Limit255") 'Sheet Info
20  wks.Range("B2:D6").ClearContents 'Clear contents in output Range
30  Dim lr As Long: Let lr = 34 '34 chosen to make the evalute String a bit longer than 255 Characters and cause VBA Evalute Method to fail.  ( 33 Works )
40  Dim rngE As Range: Set rngE = wks.Range("A1:A" & lr & "") 'Input Range
50
60  'Build string for Evalute "One liner" which is just a bit too long for Evalute Method String Argument ( 258 )
70   Dim r As Long 'Variable for row
80   Dim EvalArgStr As String '
90      For r = 1 To lr - 1 'lr Step 1
100     Let EvalArgStr = EvalArgStr & "" & wks.Range("A" & r & "").Address & "" & "&"" ""&" 'Concatenate lines with space between
110     Next r
120  Let EvalArgStr = EvalArgStr & "" & wks.Range("A" & lr & "").Address & "" 'Concatenate last cell value without space
130  Let EvalArgStr = Replace(EvalArgStr, "$", "") 'Remove "$" in string to shorten it a bit
140  Let EvalArgStr = "=" & EvalArgStr 'Include the = so as not to rely on the Implicit Default, which makes substitution in Spreadsheet easier.
150
160 'Output size of Evaluate string argument, and Output Evaluate Argument String as Text
170 Let wks.Range("B2").Value = Len(EvalArgStr) 'Output character length of Evaluate Argument String
180 Let wks.Range("C2").Value = " " & EvalArgStr 'Output Evaluate Argument String ( Add a space so that it comes out as a Text String )
190
200 'Output result of VBA Evalute Method and Check size of the evaluated String THESE LINES WILL ERROR
210         '    Let wks.Range("C6").Value = Evaluate(EvalArgStr) 'Output results of "Evaluate Range one - liner" to Cell C6
220         '    Let wks.Range("B6").Value = Len(wks.Range("C6").Value) 'Output character length in cell C6
230     'Let wks.Range("B6").Value = Len(Evaluate(EvalArgStr))
240
250 ' "Alan Evaluate" Put string Formula into cell and retrieve Spreadsheet Evaluate result.
260 Let wks.Range("D2").Value = EvalArgStr 'Output Evaluate Argument as Formula
270 Dim AlanGotEvaluate As String: Let AlanGotEvaluate = wks.Range("D2").Value 'Retrieve Spreadsheet Evaluated Value
280 Let wks.Range("D6").Value = wks.Range("D2").Value 'Paste out Spreadsheet Evaluated Value

End Sub

Code works on this Spreadsheet

Using Excel 2007

Row\ColA​B​C​D​*1*​1​Character CountStringFormula*2*​1​*3*​1​*4*​1​*5*​1​*6*​1​*7*​1​*8*​1​*9*​1​*10*​1​*11*​1​*12*​1​*13*​1​*14*​1​*15*​1​*16*​1​*17*​1​*18*​1​*19*​1​*20*​1​*21*​1​*22*​1​*23*​1​*24*​1​*25*​1​*26*​1​*27*​1​*28*​1​*29*​1​*30*​1​*31*​1​*32*​1​*33*​1​*34*​1​*35*​

*Limit255*

.....................................

Alan..

P.s. 
File with test codes in

https://app.box.com/s/i8h2llddp9nweb17oopdcy6xhr735rtu


----------



## Darren Bartrup

When restricted to a single screen I'd like to see something that would make it easier to step through VBA code and see what going on in the UI without lots of moving and fitting windows (still restricted to XP so can't move windows to the side of the screen to take up half page, etc).


----------



## steve case

*Excel 2016 under Options needs a "Turn Off Date Function" selection.*

Here's what my data as text looks like:




		Code:
__


-9999   -9999
189  6  239  6
222  6-9999
272  6  272  6
322  6-9999
350  6  333  6
267  6  272  6
156  6-9999
100  6  156  6
128  6-9999



Here's what it looks like after I paste it into Cell A1 and do a text to columns:




		Code:
__


-9    999   -9999
189    6  239  6
222    Jun-99
272    6  272  6
322    Jun-99
350    6  333  6
267    6  272  6
156    Jun-99
100    6  156  6
128    Jun-99



Is there anyway I can turn off Excel's automatic date function?


It is driving me crazy!


----------



## RoryA

Specify it's a Text field in the last step of the dialog.

As 2016 is out today, it will be interesting to see how many, if any, of these wishes have come true. Or perhaps just rename the thread to refer to the "next version of Office" instead...


----------



## Domski

steve case said:


> *Excel 2016 under Options needs a "Turn Off Date Function" selection.*
> 
> Here's what my data as text looks like:
> 
> 
> 
> 
> Code:
> __
> 
> 
> -9999   -9999
> 189  6  239  6
> 222  6-9999
> 272  6  272  6
> 322  6-9999
> 350  6  333  6
> 267  6  272  6
> 156  6-9999
> 100  6  156  6
> 128  6-9999
> 
> 
> 
> Here's what it looks like after I paste it into Cell A1 and do a text to columns:
> 
> 
> 
> 
> Code:
> __
> 
> 
> -9    999   -9999
> 189    6  239  6
> 222    Jun-99
> 272    6  272  6
> 322    Jun-99
> 350    6  333  6
> 267    6  272  6
> 156    Jun-99
> 100    6  156  6
> 128    Jun-99
> 
> 
> 
> Is there anyway I can turn off Excel's automatic date function?
> 
> 
> It is driving me crazy!



Specify the column as text in the 3rd step of the Text to Columns Wizard.

Dom


----------



## steve case

Domski said:


> Specify the column as text in the 3rd step of the Text to Columns Wizard.
> 
> Dom


Thanks for the suggestions.  

When your column of data is several hundred lines and only a few get interpreted as dates, and you did't notice and you've got 20 columns of crap and you don't  know which ones  Excel will screw up and you don't want to go through the hassle of making everything text so the formulas don't work and then trying to get them back formatted as numbers and they show up as dates again, it really gets frustrating.  An option to turn off the date function would really be nice.


----------



## Scott Huish

OK, for next version of Office.

The general rule of thumb has been if it can be done in a formula, do it in a formula because it's faster than VBA, even if a VBA solution is easier and/or quicker to write.

My thought, since the .xlsm format is essentially a .zip file, is that Excel could compile VBA code on Save and keep the high-level VBA file separate along with a compiled version of the same thing. Then Excel always runs the compiled version so that it runs faster while still allowing you to edit your program.


----------



## theBardd

Scott Huish said:


> My thought, since the .xlsm format is essentially a .zip file, is that Excel could compile VBA code on Save and keep the high-level VBA file separate along with a compiled version of the same thing. Then Excel always runs the compiled version so that it runs faster while still allowing you to edit your program.



No way that MS are going to do anything to change the VBA world other than adding to the object model.


----------



## Scott Huish

theBardd:
I don't think they'll ever fix my issue with Outlook either but if you're gonna dream, dream big.


----------



## FenceFurniture

Peter_SSs said:


> If you have more than 9 items on the QAT then  they will be assigned slightly longer shortcuts (eg Alt-0C), but in a  pattern. To see the keystrokes that go with the Alt, just press the Alt  key and look at the QAT.



That is excellent! Thanks.


----------



## Scott Huish

An N() function that works with arrays.


----------



## Smitty

theBardd said:


> No way that MS are going to do anything to change the VBA world other than adding to the object model.



True, but you can always suggest it at https://excel.uservoice.com.  If it gets more than 20 votes the Excel team will answer.


----------



## Scott Huish

Smitty said:


> True, but you can always suggest it at https://excel.uservoice.com.  If it gets more than 20 votes the Excel team will answer.



Thanks for that site. There is already an interesting conversation about it there already: https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10628445-make-vba-faster#comments


----------



## arkusM

I am late to this party and have been quiet here for a long time.
We just got forcegraded to 2016... man, I though 2010 blew chunks... 
the upgrade broke a bunch of my macros that I have built over the years, whine, whine I know.
The new tweaks to the interface seem to further reduce efficiencies.  It is not like I want to open a workheet on my local drive/network often of anything?  
Why can't I pin recent dir's anymore? Why does it default to searching for templates online? Whiskey Tango Foxtrot
It takes me sic clicks/double clicks to open a worksheet now! 

I am surprised that none has written a XML replacement for the ribbon (or is that not possible?) it would be nice to remove the "Designed by Fisher Price" interface. 
free will I admit that I am no where near the level of experienced user as many here (all you MVPs!) but good grief I do not enjoy working in Excel anymore. It has become a chore and task.

Things I would like to see:
- not have to back out of page setup/view Setup to access the other half of the dialog box
- easier access to open files (ctrl-O does not even bahave normally anymore)
- Customization of Icons
- Choice of Ribbon or classic menu (ha!)
- Smart Indentor to work in 2016 biggrin
- All I can think of at the moment.


/Rant


Cheers All and thanks for keeping this amazing resource humming, I have referred many people here.
Mark


----------



## mole999

This could have been designed for you > Navigator Utilities - Excel clasic toolbar


----------



## arkusM

mole999 said:


> This could have been designed for you > Navigator Utilities - Excel clasic toolbar



mole999 that is awesome! Thank you this is fantastic. (Not exactly what I had in mind as the real estate is still chewed up by the ribbon but it does make an EXCELlent stop gap!! Thank you.


----------



## Scott Huish

I don't use Word very often, but I was amazed that Find and Replace supports Regular Expressions. I wish that similar functions in programs across the Office platform worked the same.


----------



## Fazza

arkusM said:


> Things I would like to see:
> - Smart Indentor to work in 2016 biggrin


https://www.experts-exchange.com/articles/26599/Installing-Smart-Indenter-in-Office-2013-2016.html


----------



## MARK858

@arkusM, Including whether it is Excel 2016 64 bit or Excel 2016 32 bit.


----------



## arkusM

OH right! HAHA I had that in the footer of my signature but it is out of date now. Sigh.

2016 32bit (16.0.6925.1044)


----------



## MARK858

arkusM said:


> OH right! HAHA I had that in the footer of my signature but it is out of date now. Sigh.
> 
> 2016 32bit (16.0.6925.1044)


Try the below...

https://www.experts-exchange.com/articles/26599/Installing-Smart-Indenter-in-Office-2013-2016.html


----------



## arkusM

Sweet - Thank you.

Now I discover that I am thwarted by my BPU.  errr I mean IT... No installs for me....


----------



## MARK858

Fazza wrote a macro to do similar to Smart Indenter. When I get in I will post it if you want (can't remember if it has anything you won't have installed and of course is reliant on your IT letting you run macro's of course).


----------



## MARK858

Actually remembered I did email it to someone so....



		Code:
__


Public gstr_Wbk_Name As String
  
  ' Comments  : Code Indenter
  '
  ' REQUIRED REFERENCE : Microsoft Visual Basic for Applications Extensibility 5.3
  '
  ' Date        Change
  ' ------------------------------------------------
  ' 16-May-13 Created
  ' 16-Jun-16 Fix up handling of "IF *** THEN"
  ' 16-Feb-19 Modified to work for me, source: "http://www.mrexcel.com/forum/excel-questions/802527-replicate-indentervba-exe-com-add.html"
  
  
Sub MyCodeIndenter()
  
  Dim iLoop As Long
  Dim arListOfCodeModules() As String
  Dim vbComp As VBIDE.VBComponent
  
  gstr_Wbk_Name = ActiveWorkbook.Name
  
  If Len(gstr_Wbk_Name) > 0 Then
    
    ReDim arListOfCodeModules(1 To 500)
    For Each vbComp In Application.Workbooks(gstr_Wbk_Name).VBProject.VBComponents
      If vbComp.CodeModule.CountOfLines > 3 Then
        iLoop = iLoop + 1
        arListOfCodeModules(iLoop) = vbComp.Name
      End If
    Next vbComp
    Set vbComp = Nothing
    
    If iLoop > 0 Then
      ReDim Preserve arListOfCodeModules(1 To iLoop)
      For iLoop = LBound(arListOfCodeModules) To UBound(arListOfCodeModules)
        Call IndentModule(NameOfModule:=arListOfCodeModules(iLoop))
      Next iLoop
    End If
    Erase arListOfCodeModules
    
    MsgBox prompt:="Done", Buttons:=vbExclamation, Title:="VBA Code Indented for file " & gstr_Wbk_Name
    
  End If
  
End Sub
  
Private Sub IndentModule(ByVal NameOfModule As String)
  
  On Error Resume Next
  
  Const IndentStep As Long = 2
  
  Dim blnInitialCommentInModule As Boolean
  Dim blnIndentNextLine As Boolean, blnOutdentNow As Boolean
  Dim blnNoIndent As Boolean
  
  Dim iLoop As Long, HowManyIndents As Long
  Dim strMyCodeLine As String
  Dim vbComp As VBIDE.VBComponent
  Dim vbProj As VBIDE.VBProject
  
  Set vbProj = Workbooks(gstr_Wbk_Name).VBProject
  Set vbComp = vbProj.VBComponents(NameOfModule)
  
  With vbComp.CodeModule
    
    blnNoIndent = False
    blnInitialCommentInModule = True
    blnIndentNextLine = False
    blnOutdentNow = False
    
    For iLoop = 1 To .CountOfLines
      
      strMyCodeLine = Trim$(.Lines(iLoop, 1))
      
      If Len(strMyCodeLine) > 0 Then
        
        Select Case FirstWord(strMyCodeLine)
          
        Case "Option"
          blnNoIndent = True
          
        Case "Declare", "Enum", "Function", "Private", "Public", "Sub", "Type"
          blnNoIndent = True
          blnInitialCommentInModule = False
          
        Case "If", "IIf"
          blnIndentNextLine = True
          If InStr(strMyCodeLine, " Then ") > 0 Then
          If Not Replace$(strMyCodeLine, " ", vbNullString) Like "*Then'*" Then blnIndentNextLine = False
          End If
          blnInitialCommentInModule = False
          
        Case "Do", "For", "Select", "With"
          blnIndentNextLine = True
          blnInitialCommentInModule = False
          
        Case "Case"
          'if PREVIOUS line was not a Select or Case statement, outdent now
          If Len(.Lines(iLoop - 1, 1)) > 0 Then
            If Not FirstWord(Trim$(.Lines(iLoop - 1, 1))) Like "Case" And Not FirstWord(Trim$(.Lines(iLoop - 1, 1))) Like "Select" Then
              blnOutdentNow = True
            End If
          End If
          'if NEXT line is not an End or Case statement, indent next line
          If Len(.Lines(iLoop + 1, 1)) > 0 Then
            If Not FirstWord(Trim$(.Lines(iLoop + 1, 1))) Like "End" And Not FirstWord(Trim$(.Lines(iLoop + 1, 1))) Like "Case" Then
              blnIndentNextLine = True
            End If
          End If
          
        Case "Else", "ElseIf"
          blnOutdentNow = True
          blnIndentNextLine = True
          blnInitialCommentInModule = False
          
        Case "End"
          If strMyCodeLine Like "End Function*" Or strMyCodeLine Like "End Sub*" Then
            blnNoIndent = True
          End If
          If strMyCodeLine Like "End If*" Or strMyCodeLine Like "End Select*" Or strMyCodeLine Like "End With*" Then
            blnOutdentNow = True
          End If
          blnInitialCommentInModule = False
          
        Case "Loop", "Next"
          blnOutdentNow = True
          blnInitialCommentInModule = False
          
        Case Else
          If FirstWord(strMyCodeLine) Like "*:" Then
            blnNoIndent = True
          End If
          
        End Select
        
      End If
      
      If blnNoIndent Then HowManyIndents = 0
      If blnOutdentNow Then HowManyIndents = HowManyIndents - 1
      
      .ReplaceLine iLoop, String(HowManyIndents * IndentStep, "  ") & strMyCodeLine
      
      If HowManyIndents = 0 Then HowManyIndents = 1
      If blnInitialCommentInModule Then HowManyIndents = 0
      If blnIndentNextLine Then HowManyIndents = HowManyIndents + 1
      
      blnNoIndent = False
      blnIndentNextLine = False
      blnOutdentNow = False
      
    Next iLoop
    
  End With
  
  Set vbComp = Nothing
  Set vbProj = Nothing
  
End Sub
  
Private Function FirstWord(ByVal WholeLine As String) As String
  
  Dim ar As Variant
  ar = Split(WholeLine, " ")
  FirstWord = ar(LBound(ar))
  Erase ar
  
End Function

Obviously make sure that you set the reference.


----------



## arkusM

Yeah, They let me make and run Macros! LOL


----------



## Fazza

posting a newer version of that code indenter as just the other day I revised it.

now has
- optional line numbers
- whether line numbers are unique in a workbook or just reset every procedure
- whether or not line numbers are used for comments, or blank lines

you can see the variables that control these. I just hard coded some settings as I almost never change them 

still uses a userform to select the workbook to process. I think if you don't want that the changes are very simple. gsWbkName just needs the name of the file

cheers



		Code:
__


Option Explicit


' Comments  : Code Indenter
'
' REQUIRED REFERENCE : Microsoft Visual Basic for Applications Extensibility 5.3
'
' Date    Change
' ------------------------------------------------------------------------------
' 16-May-13 Created
' 16-Jun-15 Fix handling of "IF *** THEN" lines
' 13-Mar-17 Quick hack to tidy up long spaces before comments. Add "End Enum"
' 07-Apr-17 Add Yes/No option to line number code
' 12-Apr-17 Optionally allow line numbers to be unique within a workbook


Public gsWbkName As String


Const mbUNIQUE_LINE_NUMBERS As Boolean = True 'When line numbers are used, uniquer line numbers are different for every code line in the file (otherwise restart numbering every routine/function)
Dim mlLineNumber As Long
    
Sub IndentCode()
101
102     Dim bWantLineNumbers As Boolean
103     Dim iLoop As Long
104     Dim asListOfCodeModules() As String
105     Dim vbComp As VBIDE.VBComponent
106
107     ufWorkbooks.Show
108
109     If Len(gsWbkName) > 0 Then
110
111         If MsgBox(Prompt:="Do you want code line numbers?", Buttons:=vbYesNo + vbDefaultButton1, Title:="Code Line Numbers?") = vbYes Then
112             bWantLineNumbers = True
113             mlLineNumber = 0 'Zero value used in IndentModule code to know it is first time through loop. (And need to initialise line numbering)
114         End If
115
116         ReDim asListOfCodeModules(1 To 500)
117         For Each vbComp In Application.Workbooks(gsWbkName).VBProject.VBComponents
118             If vbComp.CodeModule.CountOfLines > 3 Then
119                 iLoop = iLoop + 1
120                 asListOfCodeModules(iLoop) = vbComp.Name
121             End If
122         Next vbComp
123         Set vbComp = Nothing
124
125         If iLoop > 0 Then
126             ReDim Preserve asListOfCodeModules(1 To iLoop)
127             For iLoop = LBound(asListOfCodeModules) To UBound(asListOfCodeModules)
128                 Call IndentModule(sNameOfModule:=asListOfCodeModules(iLoop), bWantLineNumbers:=bWantLineNumbers)
129             Next iLoop
130         End If
131         Erase asListOfCodeModules
132
133         MsgBox Prompt:="Done", Buttons:=vbExclamation, Title:="VBA Code Indented for file " & gsWbkName
134
135     End If
136
End Sub


Private Sub IndentModule(ByRef sNameOfModule As String, ByRef bWantLineNumbers As Boolean)
137
138     Const bWANT_LINE_NUMBERS_FOR_BLANK_LINES As Boolean = True 'When line numbers are used, whether applied to blank lines
139     Const bWANT_LINE_NUMBERS_FOR_COMMENTS As Boolean = True 'When line numbers are used, whether applied to comments
140
141     Const lINDENT_STEP As Long = 4 'Indent size (spaces)
142
143     Const lLINE_NUMBER_FIRST As Long = 10000 'For small projects, lower value may be preferred. Such as 100 or 1000
144     Const lLINE_NUMBER_INCREMENT As Long = 1
145
146     Dim bContinuingLine As Boolean
147     Dim bGetsLineNumber As Boolean
148     Dim bIncase As Boolean
149     Dim bIndentNextLine As Boolean, bOutdentNow As Boolean
150     Dim bInitialCommentInModule As Boolean
151     Dim bInProcedure As Boolean
152     Dim bInSelect As Boolean
153     Dim bIsComment As Boolean
154     Dim bNoIndent As Boolean
155     Dim bSelectCaseCannotHaveLineNumbersBeforeFirstCase As Boolean
156     Dim bSelectCaseJustStarted As Boolean
157
158     Dim lLenBefore As Long, lLenAfter As Long
159     Dim iLoop As Long, lHowManyIndents As Long
160     Dim sMyCodeLine As String
161     Dim sNewLine As String
162
163     Dim vbComp As VBIDE.VBComponent
164     Dim vbProj As VBIDE.VBProject
165     '-------------------------
166     Set vbProj = Workbooks(gsWbkName).VBProject
167     Set vbComp = vbProj.VBComponents(sNameOfModule)
168     With vbComp.CodeModule
169
170         bContinuingLine = False
171         bNoIndent = False
172         bInitialCommentInModule = True
173         bIndentNextLine = False
174         bInProcedure = False
175         bIncase = False
176         bOutdentNow = False
177         bSelectCaseJustStarted = False
178         If mbUNIQUE_LINE_NUMBERS Then
179             If mlLineNumber = 0 Then mlLineNumber = lLINE_NUMBER_FIRST
180         Else 'Reset line number each time routine called
181             mlLineNumber = lLINE_NUMBER_FIRST
182         End If
183
184         For iLoop = 1 To .CountOfLines
185
186             sMyCodeLine = Trim$(.Lines(iLoop, 1))
187
188             '=================================================================
189             '13-Mar-17 Quick hack to tidy up long spaces before comments
190             sMyCodeLine = Replace$(sMyCodeLine, "'", "'")        'first pass
191             lLenBefore = Len(sMyCodeLine)
192             sMyCodeLine = Replace$(sMyCodeLine, "'", "'")
193             lLenAfter = Len(sMyCodeLine)
194             Do While lLenAfter <> lLenBefore
195                 lLenBefore = lLenAfter
196                 sMyCodeLine = Replace$(sMyCodeLine, "'", "'")
197                 lLenAfter = Len(sMyCodeLine)
198             Loop
199             '=================================================================
200             '=================================================================
201             '07-Apr-17 Quick code to remove pre-existing line numbers
202             Do While Left$(sMyCodeLine, 1) Like "[0-9]"
203                 sMyCodeLine = Trim$(Right$(sMyCodeLine, Len(sMyCodeLine) - 1))
204             Loop
205             '=================================================================
206             bGetsLineNumber = True
207
208             If Len(sMyCodeLine) > 0 Then
209
210                 bIsComment = Left$(sMyCodeLine, 1) = "'"
211
212                 Select Case FirstWord(sMyCodeLine)
                        
                        Case "Option"
213                         bGetsLineNumber = False
214                         bNoIndent = True
215
216                     Case "Declare", "Enum", "Function", "Private", "Public", "Sub", "Type"
217                         bGetsLineNumber = False
218                         bNoIndent = True
219                         bInitialCommentInModule = False
220
221                     Case "If", "IIf"
222                         'For IF lines, default to indent the next line. However check for something after the THEN.
223                         bIndentNextLine = True
224                         'If the next non-space character is NOT "'" (so, it is not a comment) then do not indent the
225                         'next line as it is a one line IF THEN: not the common IF THEN - END IF block. Clear as mud?
226                         If InStr(sMyCodeLine, " Then ") > 0 Then
227                         If Not Replace$(sMyCodeLine, " ", vbNullString) Like "*Then'*" Then bIndentNextLine = False
228                         End If
229                         bInitialCommentInModule = False
230
231                     Case "Do", "For", "With"
232                         bIndentNextLine = True
233                         bInitialCommentInModule = False
234
235                     Case "Select"
236                         bIndentNextLine = True
237                         bInitialCommentInModule = False
238                         bSelectCaseJustStarted = True
239
240                     Case "Case"
241                         bIndentNextLine = True
242                         If bIncase Then bOutdentNow = True
243                         bIncase = True
244
245                     Case "Else", "ElseIf"
246                         bOutdentNow = True
247                         bIndentNextLine = True
248                         bInitialCommentInModule = False
249
250                     Case "End"
251                         If sMyCodeLine Like "End Enum*" Then
252                             bGetsLineNumber = False
253                             bNoIndent = True
254                             bInitialCommentInModule = True        'to get ready for comments after this sub/function
255                         End If
256                         If sMyCodeLine Like "End Function*" Or sMyCodeLine Like "End Sub*" Then
257                             bInProcedure = False
258                             bGetsLineNumber = False
259                             bNoIndent = True
260                             bInitialCommentInModule = True        'to get ready for comments after this sub/function
261                         End If
262                         If sMyCodeLine Like "End If*" Or sMyCodeLine Like "End Select*" Or sMyCodeLine Like "End With*" Then
263                             bOutdentNow = True
264                             bInitialCommentInModule = False
265                         End If
266                         If sMyCodeLine Like "End Select*" Then
267                             bIncase = False
268                         End If
269
270                     Case "Loop", "Next"
271                         bOutdentNow = True
272                         bInitialCommentInModule = False
273
274                     Case Else
275                         If FirstWord(sWholeLine:=sMyCodeLine) Like "*:" Then
276                             bGetsLineNumber = False
277                             bNoIndent = True
278                         End If
279
280                 End Select
281
282             End If
283
284             If bNoIndent Then lHowManyIndents = 0
285             If bOutdentNow Then lHowManyIndents = lHowManyIndents - 1
286             If sMyCodeLine Like "End Select*" Then lHowManyIndents = lHowManyIndents - 1
287             '------------------------------
288             'Last step before new line creation: handle line numbering.
289             If bWantLineNumbers And bInProcedure And bGetsLineNumber Then    ' If want line number at all
290
291                 If bContinuingLine Or (bIsComment And Not bWANT_LINE_NUMBERS_FOR_COMMENTS) Or bSelectCaseCannotHaveLineNumbersBeforeFirstCase Then
292                     'These are special, replace the line number with spaces instead: keeps alignment with other lines.
293                     sNewLine = String(Len(CStr(mlLineNumber)), " ") & " " & String(lHowManyIndents * lINDENT_STEP, " ") & sMyCodeLine
294                 Else
295                     If bWANT_LINE_NUMBERS_FOR_BLANK_LINES Or Len(sMyCodeLine) > 0 Then  'Want to number blank lines as well as normal lines
296                         mlLineNumber = mlLineNumber + lLINE_NUMBER_INCREMENT
297                         sNewLine = CStr(mlLineNumber) & " " & String(lHowManyIndents * lINDENT_STEP, " ") & sMyCodeLine
298                     Else    'don't number blank lines
299                         sNewLine = ""
300                     End If
301                 End If
302
303             Else    'No line numbers
304                 sNewLine = String(lHowManyIndents * lINDENT_STEP, " ") & sMyCodeLine
305             End If
306             .ReplaceLine iLoop, sNewLine
307
308             If sMyCodeLine Like "Private Function *" Or sMyCodeLine Like "Public Function *" _
                    Or sMyCodeLine Like "Function *" Or sMyCodeLine Like "Private Sub *" _
                    Or sMyCodeLine Like "Public Sub *" Or sMyCodeLine Like "Sub *" Then
309                 bInProcedure = True
310                 If Not mbUNIQUE_LINE_NUMBERS Then mlLineNumber = lLINE_NUMBER_FIRST
311             End If
312             '------------------------------
313             If lHowManyIndents = 0 Then lHowManyIndents = 1
314             If bInitialCommentInModule Then lHowManyIndents = 0
315             If bIndentNextLine Then lHowManyIndents = lHowManyIndents + 1
316
317             bContinuingLine = Right$(sMyCodeLine, 1) = "_"
318             bNoIndent = False
319             bIndentNextLine = False
320             bOutdentNow = False
321
322             'At end of code so is after line number handling of "Select Case"
323             If bSelectCaseJustStarted Then
324                 bSelectCaseJustStarted = False 'It is turned off within the same loop as turned on
325                 bSelectCaseCannotHaveLineNumbersBeforeFirstCase = True 'Available for use next time through loop
326             End If
327             If bIncase Then bSelectCaseCannotHaveLineNumbersBeforeFirstCase = False 'Now the first case is reached, turn off
328
329         Next iLoop
330     End With
331
332     Set vbComp = Nothing
333     Set vbProj = Nothing
334
End Sub


Private Function FirstWord(ByRef sWholeLine As String) As String 'NOTE : input is trimmed before calling this function
335
336     Dim ar As Variant
337
338     ar = Split(sWholeLine, " ")
339     FirstWord = ar(LBound(ar))
340     Erase ar
341
End Function


----------



## Worf

Fazza

Are you still on Excel 2003 as your location informs?


----------



## Fazza

Worf said:


> Fazza
> 
> Are you still on Excel 2003 as your location informs?


No. (Unfortunately )
At work I use a recent version, at home the desktop has 2007. My old laptop has Excel 2003 - though  I use it rarely these days. Even in the newer versions I prefer to save files in .xls format; though lately have used some xlsb. Most of the day to day files I use are actually from Excel 2003: and I normally just use Excel 2003 formats (colorindex up to 55). Newer versions offer very little new features of interest to me: I use the Excel 2003 shortcuts and most days (I use Excel most of the day) don't touch the ribbon. Maybe just for an occassional page setup. cheers


----------



## arkusM

Awesome Thank you



MARK858 said:


> Actually remembered I did email it to someone so....
> 
> Obviously make sure that you set the reference.


----------



## arkusM

And thank you FAZZA for the update! (would you be able to post the Userform code as well, pretty please?)

Like I said before, it has been a long while since I have spent much time on this board, but dang it is is still as good as it ever was. Such a great place!! 





Fazza said:


> posting a newer version of that code indenter as just the other day I revised it.
> 
> now has
> - optional line numbers
> - whether line numbers are unique in a workbook or just reset every procedure
> - whether or not line numbers are used for comments, or blank lines
> 
> you can see the variables that control these. I just hard coded some settings as I almost never change them
> 
> still uses a userform to select the workbook to process. I think if you don't want that the changes are very simple. gsWbkName just needs the name of the file
> 
> cheers
> 
> 
> 
> Code:
> __
> 
> 
> Option Explicit
> 
> 
> ' Comments  : Code Indenter
> '
> ' REQUIRED REFERENCE : Microsoft Visual Basic for Applications Extensibility 5.3
> '
> ' Date    Change
> ' ------------------------------------------------------------------------------
> ' 16-May-13 Created
> ' 16-Jun-15 Fix handling of "IF *** THEN" lines
> ' 13-Mar-17 Quick hack to tidy up long spaces before comments. Add "End Enum"
> ' 07-Apr-17 Add Yes/No option to line number code
> ' 12-Apr-17 Optionally allow line numbers to be unique within a workbook
> 
> 
> Public gsWbkName As String
> 
> 
> Const mbUNIQUE_LINE_NUMBERS As Boolean = True 'When line numbers are used, uniquer line numbers are different for every code line in the file (otherwise restart numbering every routine/function)
> Dim mlLineNumber As Long
> 
> Sub IndentCode()
> 101
> 102     Dim bWantLineNumbers As Boolean
> 103     Dim iLoop As Long
> 104     Dim asListOfCodeModules() As String
> 105     Dim vbComp As VBIDE.VBComponent
> 106
> 107     ufWorkbooks.Show
> 108
> 109     If Len(gsWbkName) > 0 Then
> 110
> 111         If MsgBox(Prompt:="Do you want code line numbers?", Buttons:=vbYesNo + vbDefaultButton1, Title:="Code Line Numbers?") = vbYes Then
> 112             bWantLineNumbers = True
> 113             mlLineNumber = 0 'Zero value used in IndentModule code to know it is first time through loop. (And need to initialise line numbering)
> 114         End If
> 115
> 116         ReDim asListOfCodeModules(1 To 500)
> 117         For Each vbComp In Application.Workbooks(gsWbkName).VBProject.VBComponents
> 118             If vbComp.CodeModule.CountOfLines > 3 Then
> 119                 iLoop = iLoop + 1
> 120                 asListOfCodeModules(iLoop) = vbComp.Name
> 121             End If
> 122         Next vbComp
> 123         Set vbComp = Nothing
> 124
> 125         If iLoop > 0 Then
> 126             ReDim Preserve asListOfCodeModules(1 To iLoop)
> 127             For iLoop = LBound(asListOfCodeModules) To UBound(asListOfCodeModules)
> 128                 Call IndentModule(sNameOfModule:=asListOfCodeModules(iLoop), bWantLineNumbers:=bWantLineNumbers)
> 129             Next iLoop
> 130         End If
> 131         Erase asListOfCodeModules
> 132
> 133         MsgBox Prompt:="Done", Buttons:=vbExclamation, Title:="VBA Code Indented for file " & gsWbkName
> 134
> 135     End If
> 136
> End Sub
> 
> 
> Private Sub IndentModule(ByRef sNameOfModule As String, ByRef bWantLineNumbers As Boolean)
> 137
> 138     Const bWANT_LINE_NUMBERS_FOR_BLANK_LINES As Boolean = True 'When line numbers are used, whether applied to blank lines
> 139     Const bWANT_LINE_NUMBERS_FOR_COMMENTS As Boolean = True 'When line numbers are used, whether applied to comments
> 140
> 141     Const lINDENT_STEP As Long = 4 'Indent size (spaces)
> 142
> 143     Const lLINE_NUMBER_FIRST As Long = 10000 'For small projects, lower value may be preferred. Such as 100 or 1000
> 144     Const lLINE_NUMBER_INCREMENT As Long = 1
> 145
> 146     Dim bContinuingLine As Boolean
> 147     Dim bGetsLineNumber As Boolean
> 148     Dim bIncase As Boolean
> 149     Dim bIndentNextLine As Boolean, bOutdentNow As Boolean
> 150     Dim bInitialCommentInModule As Boolean
> 151     Dim bInProcedure As Boolean
> 152     Dim bInSelect As Boolean
> 153     Dim bIsComment As Boolean
> 154     Dim bNoIndent As Boolean
> 155     Dim bSelectCaseCannotHaveLineNumbersBeforeFirstCase As Boolean
> 156     Dim bSelectCaseJustStarted As Boolean
> 157
> 158     Dim lLenBefore As Long, lLenAfter As Long
> 159     Dim iLoop As Long, lHowManyIndents As Long
> 160     Dim sMyCodeLine As String
> 161     Dim sNewLine As String
> 162
> 163     Dim vbComp As VBIDE.VBComponent
> 164     Dim vbProj As VBIDE.VBProject
> 165     '-------------------------
> 166     Set vbProj = Workbooks(gsWbkName).VBProject
> 167     Set vbComp = vbProj.VBComponents(sNameOfModule)
> 168     With vbComp.CodeModule
> 169
> 170         bContinuingLine = False
> 171         bNoIndent = False
> 172         bInitialCommentInModule = True
> 173         bIndentNextLine = False
> 174         bInProcedure = False
> 175         bIncase = False
> 176         bOutdentNow = False
> 177         bSelectCaseJustStarted = False
> 178         If mbUNIQUE_LINE_NUMBERS Then
> 179             If mlLineNumber = 0 Then mlLineNumber = lLINE_NUMBER_FIRST
> 180         Else 'Reset line number each time routine called
> 181             mlLineNumber = lLINE_NUMBER_FIRST
> 182         End If
> 183
> 184         For iLoop = 1 To .CountOfLines
> 185
> 186             sMyCodeLine = Trim$(.Lines(iLoop, 1))
> 187
> 188             '=================================================================
> 189             '13-Mar-17 Quick hack to tidy up long spaces before comments
> 190             sMyCodeLine = Replace$(sMyCodeLine, "'", "'")        'first pass
> 191             lLenBefore = Len(sMyCodeLine)
> 192             sMyCodeLine = Replace$(sMyCodeLine, "'", "'")
> 193             lLenAfter = Len(sMyCodeLine)
> 194             Do While lLenAfter <> lLenBefore
> 195                 lLenBefore = lLenAfter
> 196                 sMyCodeLine = Replace$(sMyCodeLine, "'", "'")
> 197                 lLenAfter = Len(sMyCodeLine)
> 198             Loop
> 199             '=================================================================
> 200             '=================================================================
> 201             '07-Apr-17 Quick code to remove pre-existing line numbers
> 202             Do While Left$(sMyCodeLine, 1) Like "[0-9]"
> 203                 sMyCodeLine = Trim$(Right$(sMyCodeLine, Len(sMyCodeLine) - 1))
> 204             Loop
> 205             '=================================================================
> 206             bGetsLineNumber = True
> 207
> 208             If Len(sMyCodeLine) > 0 Then
> 209
> 210                 bIsComment = Left$(sMyCodeLine, 1) = "'"
> 211
> 212                 Select Case FirstWord(sMyCodeLine)
> 
> Case "Option"
> 213                         bGetsLineNumber = False
> 214                         bNoIndent = True
> 215
> 216                     Case "Declare", "Enum", "Function", "Private", "Public", "Sub", "Type"
> 217                         bGetsLineNumber = False
> 218                         bNoIndent = True
> 219                         bInitialCommentInModule = False
> 220
> 221                     Case "If", "IIf"
> 222                         'For IF lines, default to indent the next line. However check for something after the THEN.
> 223                         bIndentNextLine = True
> 224                         'If the next non-space character is NOT "'" (so, it is not a comment) then do not indent the
> 225                         'next line as it is a one line IF THEN: not the common IF THEN - END IF block. Clear as mud?
> 226                         If InStr(sMyCodeLine, " Then ") > 0 Then
> 227                         If Not Replace$(sMyCodeLine, " ", vbNullString) Like "*Then'*" Then bIndentNextLine = False
> 228                         End If
> 229                         bInitialCommentInModule = False
> 230
> 231                     Case "Do", "For", "With"
> 232                         bIndentNextLine = True
> 233                         bInitialCommentInModule = False
> 234
> 235                     Case "Select"
> 236                         bIndentNextLine = True
> 237                         bInitialCommentInModule = False
> 238                         bSelectCaseJustStarted = True
> 239
> 240                     Case "Case"
> 241                         bIndentNextLine = True
> 242                         If bIncase Then bOutdentNow = True
> 243                         bIncase = True
> 244
> 245                     Case "Else", "ElseIf"
> 246                         bOutdentNow = True
> 247                         bIndentNextLine = True
> 248                         bInitialCommentInModule = False
> 249
> 250                     Case "End"
> 251                         If sMyCodeLine Like "End Enum*" Then
> 252                             bGetsLineNumber = False
> 253                             bNoIndent = True
> 254                             bInitialCommentInModule = True        'to get ready for comments after this sub/function
> 255                         End If
> 256                         If sMyCodeLine Like "End Function*" Or sMyCodeLine Like "End Sub*" Then
> 257                             bInProcedure = False
> 258                             bGetsLineNumber = False
> 259                             bNoIndent = True
> 260                             bInitialCommentInModule = True        'to get ready for comments after this sub/function
> 261                         End If
> 262                         If sMyCodeLine Like "End If*" Or sMyCodeLine Like "End Select*" Or sMyCodeLine Like "End With*" Then
> 263                             bOutdentNow = True
> 264                             bInitialCommentInModule = False
> 265                         End If
> 266                         If sMyCodeLine Like "End Select*" Then
> 267                             bIncase = False
> 268                         End If
> 269
> 270                     Case "Loop", "Next"
> 271                         bOutdentNow = True
> 272                         bInitialCommentInModule = False
> 273
> 274                     Case Else
> 275                         If FirstWord(sWholeLine:=sMyCodeLine) Like "*:" Then
> 276                             bGetsLineNumber = False
> 277                             bNoIndent = True
> 278                         End If
> 279
> 280                 End Select
> 281
> 282             End If
> 283
> 284             If bNoIndent Then lHowManyIndents = 0
> 285             If bOutdentNow Then lHowManyIndents = lHowManyIndents - 1
> 286             If sMyCodeLine Like "End Select*" Then lHowManyIndents = lHowManyIndents - 1
> 287             '------------------------------
> 288             'Last step before new line creation: handle line numbering.
> 289             If bWantLineNumbers And bInProcedure And bGetsLineNumber Then    ' If want line number at all
> 290
> 291                 If bContinuingLine Or (bIsComment And Not bWANT_LINE_NUMBERS_FOR_COMMENTS) Or bSelectCaseCannotHaveLineNumbersBeforeFirstCase Then
> 292                     'These are special, replace the line number with spaces instead: keeps alignment with other lines.
> 293                     sNewLine = String(Len(CStr(mlLineNumber)), " ") & " " & String(lHowManyIndents * lINDENT_STEP, " ") & sMyCodeLine
> 294                 Else
> 295                     If bWANT_LINE_NUMBERS_FOR_BLANK_LINES Or Len(sMyCodeLine) > 0 Then  'Want to number blank lines as well as normal lines
> 296                         mlLineNumber = mlLineNumber + lLINE_NUMBER_INCREMENT
> 297                         sNewLine = CStr(mlLineNumber) & " " & String(lHowManyIndents * lINDENT_STEP, " ") & sMyCodeLine
> 298                     Else    'don't number blank lines
> 299                         sNewLine = ""
> 300                     End If
> 301                 End If
> 302
> 303             Else    'No line numbers
> 304                 sNewLine = String(lHowManyIndents * lINDENT_STEP, " ") & sMyCodeLine
> 305             End If
> 306             .ReplaceLine iLoop, sNewLine
> 307
> 308             If sMyCodeLine Like "Private Function *" Or sMyCodeLine Like "Public Function *" _
> Or sMyCodeLine Like "Function *" Or sMyCodeLine Like "Private Sub *" _
> Or sMyCodeLine Like "Public Sub *" Or sMyCodeLine Like "Sub *" Then
> 309                 bInProcedure = True
> 310                 If Not mbUNIQUE_LINE_NUMBERS Then mlLineNumber = lLINE_NUMBER_FIRST
> 311             End If
> 312             '------------------------------
> 313             If lHowManyIndents = 0 Then lHowManyIndents = 1
> 314             If bInitialCommentInModule Then lHowManyIndents = 0
> 315             If bIndentNextLine Then lHowManyIndents = lHowManyIndents + 1
> 316
> 317             bContinuingLine = Right$(sMyCodeLine, 1) = "_"
> 318             bNoIndent = False
> 319             bIndentNextLine = False
> 320             bOutdentNow = False
> 321
> 322             'At end of code so is after line number handling of "Select Case"
> 323             If bSelectCaseJustStarted Then
> 324                 bSelectCaseJustStarted = False 'It is turned off within the same loop as turned on
> 325                 bSelectCaseCannotHaveLineNumbersBeforeFirstCase = True 'Available for use next time through loop
> 326             End If
> 327             If bIncase Then bSelectCaseCannotHaveLineNumbersBeforeFirstCase = False 'Now the first case is reached, turn off
> 328
> 329         Next iLoop
> 330     End With
> 331
> 332     Set vbComp = Nothing
> 333     Set vbProj = Nothing
> 334
> End Sub
> 
> 
> Private Function FirstWord(ByRef sWholeLine As String) As String 'NOTE : input is trimmed before calling this function
> 335
> 336     Dim ar As Variant
> 337
> 338     ar = Split(sWholeLine, " ")
> 339     FirstWord = ar(LBound(ar))
> 340     Erase ar
> 341
> End Function


----------



## Fazza

sure, mate, cheers
	
	
	
	
	
	




		Code:
__


Option Explicit


Private Sub btnCANCEL_Click()
  gsWbkName = vbNullString
  Unload ufWorkbooks
End Sub


Private Sub btnOK_Click()
  gsWbkName = vbNullString
  On Error Resume Next
  gsWbkName = lbWorkbooks.Value
  Unload ufWorkbooks
End Sub


Private Sub lbWorkbooks_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
  gsWbkName = lbWorkbooks.Value
  Unload ufWorkbooks
End Sub


Private Sub UserForm_Initialize()


  Dim wbkLoop As Excel.Workbook


  For Each wbkLoop In Application.Workbooks
    Select Case wbkLoop.Name
      Case ThisWorkbook.Name, "PERSONAL.XLS", "PERSONAL.XLSB"
      Case Else: ufWorkbooks.lbWorkbooks.AddItem wbkLoop.Name
    End Select
  Next wbkLoop
  Set wbkLoop = Nothing


End Sub


----------



## Fazza

I just noticed an error in the code indenter that I posted earlier.

What I posted has a useless section of code in lines 188 to 199. It doesn't do anything. It is supposed to remove large spaces and when the code ran on a copy of itself, it deleted the excess spaces & made the code useless.  Using the code posted earlier will have no errors, it just won't remove excess spaces - which doesn't matter much.

Still, to fix it, here are the replacement lines 188 to 199.
	
	
	
	
	
	




		Code:
__


188             '=================================================================
189             '13-Mar-17 Quick hack to tidy up long spaces before comments
190             sMyCodeLine = Replace$(sMyCodeLine, "    '", "'")        'first pass
191             lLenBefore = Len(sMyCodeLine)
192             sMyCodeLine = Replace$(sMyCodeLine, "  '", "'")
193             lLenAfter = Len(sMyCodeLine)
194             Do While lLenAfter <> lLenBefore
195                 lLenBefore = lLenAfter
196                 sMyCodeLine = Replace$(sMyCodeLine, "  '", "'")
197                 lLenAfter = Len(sMyCodeLine)
198             Loop
199             '=================================================================

And for completeness, the full corrected code.
	
	
	
	
	
	




		Code:
__


Option Explicit


' Comments  : Code Indenter
'
' REQUIRED REFERENCE : Microsoft Visual Basic for Applications Extensibility 5.3
'
' Date    Change
' ------------------------------------------------------------------------------
' 16-May-13 Created
' 16-Jun-15 Fix handling of "IF *** THEN" lines
' 13-Mar-17 Quick hack to tidy up long spaces before comments. Add "End Enum"
' 07-Apr-17 Add Yes/No option to line number code
' 12-Apr-17 Optionally allow line numbers to be unique within a workbook


Public gsWbkName As String


Const mbUNIQUE_LINE_NUMBERS As Boolean = True 'When line numbers are used, uniquer line numbers are different for every code line in the file (otherwise restart numbering every routine/function)
Dim mlLineNumber As Long
    
Sub IndentCode()
101
102     Dim bWantLineNumbers As Boolean
103     Dim iLoop As Long
104     Dim asListOfCodeModules() As String
105     Dim vbComp As VBIDE.VBComponent
106
107     ufWorkbooks.Show
108
109     If Len(gsWbkName) > 0 Then
110
111         If MsgBox(Prompt:="Do you want code line numbers?", Buttons:=vbYesNo + vbDefaultButton1, Title:="Code Line Numbers?") = vbYes Then
112             bWantLineNumbers = True
113             mlLineNumber = 0 'Zero value used in IndentModule sub to know it is first time through loop. (And need to initialise line numbering)
114         End If
115
116         ReDim asListOfCodeModules(1 To 500)
117         For Each vbComp In Application.Workbooks(gsWbkName).VBProject.VBComponents
118             If vbComp.CodeModule.CountOfLines > 3 Then
119                 iLoop = iLoop + 1
120                 asListOfCodeModules(iLoop) = vbComp.Name
121             End If
122         Next vbComp
123         Set vbComp = Nothing
124
125         If iLoop > 0 Then
126             ReDim Preserve asListOfCodeModules(1 To iLoop)
127             For iLoop = LBound(asListOfCodeModules) To UBound(asListOfCodeModules)
128                 Call IndentModule(sNameOfModule:=asListOfCodeModules(iLoop), bWantLineNumbers:=bWantLineNumbers)
129             Next iLoop
130         End If
131         Erase asListOfCodeModules
132
133         MsgBox Prompt:="Done", Buttons:=vbExclamation, Title:="VBA Code Indented for file " & gsWbkName
134
135     End If
136
End Sub


Private Sub IndentModule(ByRef sNameOfModule As String, ByRef bWantLineNumbers As Boolean)
137
138     Const bWANT_LINE_NUMBERS_FOR_BLANK_LINES As Boolean = True 'When line numbers are used, whether applied to blank lines
139     Const bWANT_LINE_NUMBERS_FOR_COMMENTS As Boolean = True 'When line numbers are used, whether applied to comments
140
141     Const lINDENT_STEP As Long = 2 'Indent size (spaces)
142
143     Const lLINE_NUMBER_FIRST As Long = 100 'Say 100 for small projects or 10000 for large projects
144     Const lLINE_NUMBER_INCREMENT As Long = 1
145
146     Dim bContinuingLine As Boolean
147     Dim bGetsLineNumber As Boolean
148     Dim bIncase As Boolean
149     Dim bIndentNextLine As Boolean, bOutdentNow As Boolean
150     Dim bInitialCommentInModule As Boolean
151     Dim bInProcedure As Boolean
152     Dim bInSelect As Boolean
153     Dim bIsComment As Boolean
154     Dim bNoIndent As Boolean
155     Dim bSelectCaseCannotHaveLineNumbersBeforeFirstCase As Boolean
156     Dim bSelectCaseJustStarted As Boolean
157
158     Dim lLenBefore As Long, lLenAfter As Long
159     Dim iLoop As Long, lHowManyIndents As Long
160     Dim sMyCodeLine As String
161     Dim sNewLine As String
162
163     Dim vbComp As VBIDE.VBComponent
164     Dim vbProj As VBIDE.VBProject
165     '-------------------------
166     Set vbProj = Workbooks(gsWbkName).VBProject
167     Set vbComp = vbProj.VBComponents(sNameOfModule)
168     With vbComp.CodeModule
169
170         bContinuingLine = False
171         bNoIndent = False
172         bInitialCommentInModule = True
173         bIndentNextLine = False
174         bInProcedure = False
175         bIncase = False
176         bOutdentNow = False
177         bSelectCaseJustStarted = False
178         If mbUNIQUE_LINE_NUMBERS Then
179             If mlLineNumber = 0 Then mlLineNumber = lLINE_NUMBER_FIRST
180         Else 'Reset line number each time routine called
181             mlLineNumber = lLINE_NUMBER_FIRST
182         End If
183
184         For iLoop = 1 To .CountOfLines
185
186             sMyCodeLine = Trim$(.Lines(iLoop, 1))
187
188             '=================================================================
189             '13-Mar-17 Quick hack to tidy up long spaces before comments
190             sMyCodeLine = Replace$(sMyCodeLine, "    '", "'")        'first pass
191             lLenBefore = Len(sMyCodeLine)
192             sMyCodeLine = Replace$(sMyCodeLine, "  '", "'")
193             lLenAfter = Len(sMyCodeLine)
194             Do While lLenAfter <> lLenBefore
195                 lLenBefore = lLenAfter
196                 sMyCodeLine = Replace$(sMyCodeLine, "  '", "'")
197                 lLenAfter = Len(sMyCodeLine)
198             Loop
199             '=================================================================
200             '=================================================================
201             '07-Apr-17 Quick code to remove pre-existing line numbers
202             Do While Left$(sMyCodeLine, 1) Like "[0-9]"
203                 sMyCodeLine = Trim$(Right$(sMyCodeLine, Len(sMyCodeLine) - 1))
204             Loop
205             '=================================================================
206             bGetsLineNumber = True
207
208             If Len(sMyCodeLine) > 0 Then
209
210                 bIsComment = Left$(sMyCodeLine, 1) = "'"
211
212                 Select Case FirstWord(sMyCodeLine)
                        
                        Case "Option"
213                         bGetsLineNumber = False
214                         bNoIndent = True
215
216                     Case "Declare", "Enum", "Function", "Private", "Public", "Sub", "Type"
217                         bGetsLineNumber = False
218                         bNoIndent = True
219                         bInitialCommentInModule = False
220
221                     Case "If", "IIf"
222                         'For IF lines, default to indent the next line. However check for something after the THEN.
223                         bIndentNextLine = True
224                         'If the next non-space character is NOT "'" (so, it is not a comment) then do not indent the
225                         'next line as it is a one line IF THEN: not the common IF THEN - END IF block. Clear as mud?
226                         If InStr(sMyCodeLine, " Then ") > 0 Then
227                         If Not Replace$(sMyCodeLine, " ", vbNullString) Like "*Then'*" Then bIndentNextLine = False
228                         End If
229                         bInitialCommentInModule = False
230
231                     Case "Do", "For", "With"
232                         bIndentNextLine = True
233                         bInitialCommentInModule = False
234
235                     Case "Select"
236                         bIndentNextLine = True
237                         bInitialCommentInModule = False
238                         bSelectCaseJustStarted = True
239
240                     Case "Case"
241                         bIndentNextLine = True
242                         If bIncase Then bOutdentNow = True
243                         bIncase = True
244
245                     Case "Else", "ElseIf"
246                         bOutdentNow = True
247                         bIndentNextLine = True
248                         bInitialCommentInModule = False
249
250                     Case "End"
251                         If sMyCodeLine Like "End Enum*" Then
252                             bGetsLineNumber = False
253                             bNoIndent = True
254                             bInitialCommentInModule = True        'to get ready for comments after this sub/function
255                         End If
256                         If sMyCodeLine Like "End Function*" Or sMyCodeLine Like "End Sub*" Then
257                             bInProcedure = False
258                             bGetsLineNumber = False
259                             bNoIndent = True
260                             bInitialCommentInModule = True        'to get ready for comments after this sub/function
261                         End If
262                         If sMyCodeLine Like "End If*" Or sMyCodeLine Like "End Select*" Or sMyCodeLine Like "End With*" Then
263                             bOutdentNow = True
264                             bInitialCommentInModule = False
265                         End If
266                         If sMyCodeLine Like "End Select*" Then
267                             bIncase = False
268                         End If
269
270                     Case "Loop", "Next"
271                         bOutdentNow = True
272                         bInitialCommentInModule = False
273
274                     Case Else
275                         If FirstWord(sWholeLine:=sMyCodeLine) Like "*:" Then
276                             bGetsLineNumber = False
277                             bNoIndent = True
278                         End If
279
280                 End Select
281
282             End If
283
284             If bNoIndent Then lHowManyIndents = 0
285             If bOutdentNow Then lHowManyIndents = lHowManyIndents - 1
286             If sMyCodeLine Like "End Select*" Then lHowManyIndents = lHowManyIndents - 1
287             '------------------------------
288             'Last step before new line creation: handle line numbering.
289             If bWantLineNumbers And bInProcedure And bGetsLineNumber Then    ' If want line number at all
290
291                 If bContinuingLine Or (bIsComment And Not bWANT_LINE_NUMBERS_FOR_COMMENTS) Or bSelectCaseCannotHaveLineNumbersBeforeFirstCase Then
292                     'These are special, replace the line number with spaces instead: keeps alignment with other lines.
293                     sNewLine = String(Len(CStr(mlLineNumber)), " ") & " " & String(lHowManyIndents * lINDENT_STEP, " ") & sMyCodeLine
294                 Else
295                     If bWANT_LINE_NUMBERS_FOR_BLANK_LINES Or Len(sMyCodeLine) > 0 Then  'Want to number blank lines as well as normal lines
296                         mlLineNumber = mlLineNumber + lLINE_NUMBER_INCREMENT
297                         sNewLine = CStr(mlLineNumber) & " " & String(lHowManyIndents * lINDENT_STEP, " ") & sMyCodeLine
298                     Else    'don't number blank lines
299                         sNewLine = ""
300                     End If
301                 End If
302
303             Else    'No line numbers
304                 sNewLine = String(lHowManyIndents * lINDENT_STEP, " ") & sMyCodeLine
305             End If
306             .ReplaceLine iLoop, sNewLine
307
308             If sMyCodeLine Like "Private Function *" Or sMyCodeLine Like "Public Function *" _
                    Or sMyCodeLine Like "Function *" Or sMyCodeLine Like "Private Sub *" _
                    Or sMyCodeLine Like "Public Sub *" Or sMyCodeLine Like "Sub *" Then
309                 bInProcedure = True
310                 If Not mbUNIQUE_LINE_NUMBERS Then mlLineNumber = lLINE_NUMBER_FIRST
311             End If
312             '------------------------------
313             If lHowManyIndents = 0 Then lHowManyIndents = 1
314             If bInitialCommentInModule Then lHowManyIndents = 0
315             If bIndentNextLine Then lHowManyIndents = lHowManyIndents + 1
316
317             bContinuingLine = Right$(sMyCodeLine, 1) = "_"
318             bNoIndent = False
319             bIndentNextLine = False
320             bOutdentNow = False
321
322             'At end of code so is after line number handling of "Select Case"
323             If bSelectCaseJustStarted Then
324                 bSelectCaseJustStarted = False 'It is turned off within the same loop as turned on
325                 bSelectCaseCannotHaveLineNumbersBeforeFirstCase = True 'Available for use next time through loop
326             End If
327             If bIncase Then bSelectCaseCannotHaveLineNumbersBeforeFirstCase = False 'Now the first case is reached, turn off
328
329         Next iLoop
330     End With
331
332     Set vbComp = Nothing
333     Set vbProj = Nothing
334
End Sub


Private Function FirstWord(ByRef sWholeLine As String) As String 'NOTE : input is trimmed before calling this function
335
336     Dim ar As Variant
337
338     ar = Split(sWholeLine, " ")
339     FirstWord = ar(LBound(ar))
340     Erase ar
341
End Function


----------



## arkusM

Fazza,

Thank you so much for sharing all this code, I really appreciate it.

Cheers,

Mark



Fazza said:


> sure, mate, cheers
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> Code:
> __
> 
> 
> Option Explicit
> 
> 
> Private Sub btnCANCEL_Click()
> gsWbkName = vbNullString
> Unload ufWorkbooks
> End Sub
> 
> 
> Private Sub btnOK_Click()
> gsWbkName = vbNullString
> On Error Resume Next
> gsWbkName = lbWorkbooks.Value
> Unload ufWorkbooks
> End Sub
> 
> 
> Private Sub lbWorkbooks_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
> gsWbkName = lbWorkbooks.Value
> Unload ufWorkbooks
> End Sub
> 
> 
> Private Sub UserForm_Initialize()
> 
> 
> Dim wbkLoop As Excel.Workbook
> 
> 
> For Each wbkLoop In Application.Workbooks
> Select Case wbkLoop.Name
> Case ThisWorkbook.Name, "PERSONAL.XLS", "PERSONAL.XLSB"
> Case Else: ufWorkbooks.lbWorkbooks.AddItem wbkLoop.Name
> End Select
> Next wbkLoop
> Set wbkLoop = Nothing
> 
> 
> End Sub


----------



## GJSBED

They should bring back the Easter egg that let you run Solitaire and Minesweeper than was in Excel 97 or 95 even when the company didnt have Games on the PC... cant remember which, but it was sweet. So much work got done...honest!


----------

