![excel 2016 vba and macros review excel 2016 vba and macros review](https://images-na.ssl-images-amazon.com/images/I/41uFIqNpb6L._SX383_BO1,204,203,200_.jpg)
- #EXCEL 2016 VBA AND MACROS REVIEW HOW TO#
- #EXCEL 2016 VBA AND MACROS REVIEW CODE#
- #EXCEL 2016 VBA AND MACROS REVIEW PLUS#
We'll do an example on how to automatically create and format an Excel chart based on a dataset. I'll show you how to to enable the Developer Tab, which allows you to record and run macros.
#EXCEL 2016 VBA AND MACROS REVIEW PLUS#
Plus you learn all of this in 10 minutes! Macros allow you to automate repetitive tasks in Excel. GetGroupRange(ActiveSheet, 20).In this video you'll learn the basics of Macros and VBA in Excel. GetGroupRange(ActiveSheet, 19).Visible = msoTrue GetGroupRange(ActiveSheet, 71).Visible = msoTrue GetGroupRange(ActiveSheet, 23).Visible = msoTrue GetGroupRange(ActiveSheet, 19, 20, 23, 71).Visible = msoFalse Set getGroupRange = ws.Shapes.Range(GroupNames) If IsNumeric(GroupNames(n)) Then GroupNames(n) = "Group " & GroupNames(n)
![excel 2016 vba and macros review excel 2016 vba and macros review](https://m.media-amazon.com/images/I/41B4vcrIogS._SL500_.jpg)
Public Function getGroupRange(ws As Worksheet, ParamArray GroupNames() As Variant) As ShapeRange Reference: MSDN - Shapes.Range Property(Excel)Ĭonsidering that all Group default names start with "Group ", you could further simplify it by creating a function that will return the ShapeRange based of the Group number. (Array("Group 19", "Group 20", "Group 23", "Group 71")).Visible = msoTrueĪctiveSheet.Shapes("Group 20").Visible = msoFalse
![excel 2016 vba and macros review excel 2016 vba and macros review](https://exceleratorbi.com.au/wp-content/uploads/2015/04/image6.png)
The simple answer is to use pass an array of names to the Shapes.Range, hide all 4 Groups, and then make the one Group visible.
#EXCEL 2016 VBA AND MACROS REVIEW CODE#
You might want to have some parameter checking and error handling in place, depending on where and how the code is going to be used. This is as far as I'm able to simplify the process. HideAllButOneGroup ActiveSheet, NameGroupA Which then simplifies the event procedures like this: Sub Pic_1_SA_click() Now if you expect you will need several more similar procedures, then you could even go further and define a subroutine that takes a worksheet and a GroupName to hide all but the specified group: Private Sub HideAllButSpecifiedGroup(ContainingSheet as Worksheet, ByVal GroupName as String)ĬontainingSheet.Shapes(GroupName).Visible = True This leads to you only needing to specify one group to be shown in each event procedure: Sub Pic_1_SA_click()ĪctiveSheet.Shapes(NameGroupA).Visible = True (Not very useful in this specific case, but in general a good practice.) Replacing the ActiveSheet by a parameter makes the procedure (somewhat) reusable. So the next thing we can to is define a subroutine that hides all groups: Private Sub HideAllGroups(ContainingSheet as Worksheet) With the assumed randomness in the event execution order, there is no point in trying to find a specific pattern that could be encoded to optimize things. This way, should you decide to rename them or something changes, it only needs to be changed in one place. Private Const NameGroupD As String = "Group 20" Private Const NameGroupC As String = "Group 19" Private Const NameGroupB As String = "Group 71" Private Const NameGroupA As String = "Group 23" To start refactoring, I'd save the group names in module constants.