Alright, so first off detailed directions for adding the module to place your code. I'll go very basic and english for you, if some of this is common knowledge to you I mean no offense. Just want to be sure I capture everything to be helpful.
For creating module to run this macro:
1. Open visua basic via developer tab
2. You should see Sheet1("First Sheets name") open by default
3. Right-click any sheet listed and select the
Insert option
4. Select
Module from this list
From there your first module is open, I'm sure there's a fancy way to describe them but for me it's like this... The sheets found under the
Microsoft Excel Objects are like functions attached to the sheet, anything within them only functions with that sheet. Moving them to the modules allows them to work amongst all worksheets in the workbook, given that you properly write the commands.
So the code itself, in the best english I can provide:
We'll start off with the Dimension created...
Code:
Dimensions allow you to store an answer for later/multiple uses without having to do the equation over and over again, binding down the memory and slowing the macro down.
An example from the front line of the worksheet, it's like doing an equation in cell B3 for an equation that you'll need for B4:B999, and using B3 or the cells, rather than nesting the formula B3 is using to get it's value for every cell.
At first, Dim will take trial and error to know when they're handy versus when they're excessive. Not all declarations require a Dim to be preset. For example, I use the For.. if conjunction a lot and the declaration you're making up here isn't required to be declared in a preformed dim, excel catches it within the command and actually runs faster because it'll declare the variable as it sees necessary. Declarin the Dim for this is really only required if your dictating what the "As" will be (Integer, String, Long, etc). Enough pretext...
Dim Fee As Integer 'This declaration will represent the value captured from the worksheets we want to Sum for each cell within the table
Dim FeeName As String 'This is a string (or strand of text) that will pick up the fee name from the master workbook, starting at the first and working all the way down to the last Fee Name
Dim Act As String 'This is a string that will pick up the Account number from the masterwork book, starting at the first and working right to the last account name
Dim FeeName2 As String 'This is the second string for Fee Name that will pick up the fee name from the additional workbooks and is used to find the matching row
Dim Act2 As String 'This is the second string for Account Number that will pick up the account number from the additional workbooks and is used to find the matching account number
So now our declarations have been defined, excel now knows "I need to store "this much memory" to bank the values of the 5 dimensions provided. Different deliniations (spelling?) use different amounts of memory to hold the value. At the end of this I'll provide you a pretty basic guide to macros I put together to remind myself of these things.
Code:
Next was defining the logic. Since you have a total of 170 bank fees possible, but not every month will have all 170 fees shown, we couldn't just call out a set range of B2:"Last cell in the range" and make the macro SUM all other workbooks B2 and insert that value into the B2 on the master... we had to teach excel how to find the matching value based on Fee Name and Account Number. That's actually a good lesson I learned that made writing macro easier. Excel understands thousands of functions, but they're like puzzle pieces. You can find unique ways to cross-use them to get your result, either by making long and complicated algorithims or super clean and skinny code... but if the pieces don't fit excel won't know what the heck you're saying. Imagine someone speaking spanish to you, here and there they may say a word like No or a word that doesn't translate.. you may understand a few words but you can't do anything with it because everything else makes no sense.
So, with this code I took the route of capturing our X and Y values (X being our row/bank fee, and Y being our column/Account numbe) using the For Each statement.
For Each [B]Row[/B] in the Range(Cells(3, "A"), Cells(3,"A").End(xlDown))
[B]For Each[/B] can be used to walk through data by giving it a starting point and an ending point. So the above code reads in englisha s follows:
For Each (Cell in reference to be provided) [B]Row[/B] (A dim created to represent the current cell reviewed within the range coming up) in the range( "Here you declare your range, this can go vertical, horizontal, or with a little finese diaganol, but for the Row we went vetically down"...
When using "Cells" over range, you get a little more freedom in defining the row and column, but it also opens up more opportunity for typos/bad coding so just keep that in mind. Best practice is to use Range, like Range("A1") but when we need the rows and columns to shift cells is more useful, so the end fo of the for each statement...
[B]Cells(3, "A") [/B] is calling out the start point for the range, we want to start looking at values in the rows starting at A3. Cells command looks for the row first "3", and then the column "A" which must be enterred with quotes if you are using the letter. If you want to use a number which I more commonly do, it would need to be the respected column Number... A=1, B=2, etc.
[B]Cells(3, "A").End(xlDown)[/B] is the ending point of the range, so what we are doing here is saying "Whatever is the last cell from A3 down, that's our ending point." This is the equivalent for being in the workbook, selecting A3 and then pressing CTRL+SHIFT+DOWN KEY which would highlight your whole bank fee column. Same goes for the Account number later on, instead of .End(xlDown) we use .End(xltoRight) to Start at first account number B1 and go right until the populated cells end.
So we utlize the For Each comand four times in this formula, capturing the bank fee and account number from the master workbook and the sheets we want to summarize.
It's not important the order you capture these, we could have looked at account number first and then bank fee, but it is important the FeeName and Act are being captured when you walkign the data because we are using the active row to obtain our vaue.
So, within the for each statement I used
Row. This is just because this is helpful for me to remember what I'm doing, but you could have used anything in place of it. Let's say A2 was used instead...
When we declare a Dim to equal something during a for each statement, we can use the "Row" or "A2" to capture the current row we are in. Thus the Row.Row, if we chose A2 in it's place the Fee Name would have been written:
FeeName = Cells(A2.Row, 1) This is because it declare whatever row we are on for the A2 for each statement, this is the row value I want to use when providing a row vaue for the FeeName. Since we will always be in column A when looking at bank fee names we used a 1 for the column - also could have been "A".
The next line after determining what the FeeName was equal to on the current Row, we have excel as the question... Is our value for FeeName empty, or in other words have I reached the bottom of my populated cells?
We want it to stop if the cell is blank, so between the if statement and Else, we leave it blank. This will force the macro to ignore anything written within the path of the Else response and go right to the next row or otherwise cease running the macro.
Else in this scenario is the equivalent response of "No excel, the current cell I'm on in this row is not empty. It has a value, do something"
To which we move on, asking the value of the next For Each statement looking at the columns.
If we reach this point and both the Bank Fee name is obtained and the Account number is lined up we then have the variables to go looking into out other sheets, so we shift to a Do/Loop statement to create a cycle of driving through additional sheets.
The Do statement Says
Do Until Active.Sheet Next is Nothing which is bascally saying, I want you to keep doing the next set of commands till the next time your go a sheet to the right and a sheet isn't there.. or the sheet we are on now is the last
Then, we tell it to move to the next sheet if all is good. Unlike the For Each statement, the Do command just stops looping when the criteria is met, there is no option 1/option 2 path to declare based on it's response. This is why I used it in the middle of this code, If there is no worksheet, then we only have one possible action we would take at this moment and that's to begin te steps of bring back the recap sum to our master sheet.
So, if the next sheet is available, the
ActiveSheet.Next.Select command is the equivalent of the CTRL + Page Down command in the workbook, moving one sheet over to the right.
At this point, we have the Bank Fee name and the account lined up to know what and where we capture from the other sheets, so after the Do command runs and we are on a new sheet, say Jan< the next For Each statements work just like the first to find the FeeName2 Value, only we add to the Else path the question if the Fee Name we have on the new worksheet matches the Fee Name we are looking for from what we got in our Master. (Bank Fee 1 from the master, does this Bank Fee on the new sheet match Bank Fee 1 or is it something else?
Does this for the account number also.
If it ever fails to find the alignment, like Bank Fee 37 isn't on any month worksheet then it just moves on and will give Bank Fee 37 a 0 for all account numbers.
Once you have a lined up match for Master and the Active sheet, the macro
then calls out wha tthe Fee equals. Fee being the value we are collecting to place on our master recap.
Formula reads:
Fee = Fee + Cells(Row2.Row, Column2.Column)
or In English
The Value of
Fee is equal to whatever the last
Fee was + the new value found which finds the matched Bank Fee Name (row2.row) and the matching account number (column2.column). We put a piece of code after begining to look for the column that resets the Fee to 0 so each time we begin to look for a new sum it starts over.
Before it restarts though, the end of the code says
Sheets("Recap".Select 'Go back to the master tab
Cells(Row.Row, Column.Column) = Fee 'From the initial Bank Fee row and Account # Column, where these two intersect put the Sum of the Fees I collected within the "Fee" Dim.
Now.. that's a lot, but if you need it simplified let me know, I tend to ramble.
The Application.Enableevents = true/false, this stops events from occuring while the macro is runnning.
It's excel is doing a math problem, and by turning off enableevents it no longer has to show it's math - it just gets the answer/actions and when it's done the workbook updates to the results.
The Application.ScreenUpdating = true/false, this stops the workbook from displsying changes happening
With this macro we are running through 170 bank fees across 31 accounts, that a lot of cells within it that are cross referencing multiple months... this script stops you from seeing all the sheet changes and reference points moving around and just takes the time excel needs to think and then snaps the image to the results from the macro,
Kind of like a guy building a bridge, you can watch him build the bridge or you can set screenupdating to false and you just see the bridge.
So, as promised here is the guide to variable types:
'Variable Types
'Type 'Storage reserved 'Definition
'Byte 1 byte Small positive integers, 0 - 255 (limited but less storage used)
'Integer 2 bytes Integer values -32,768 to 32,767 (for larger/longer ranges use Long)
'Long 4 bytes Integer values -2,147,483,648 to 2,147,483,647
'Sinlge 4 bytes Integer values -3.402823E+38 to -1.401298E-45 for negative values, and 1.401298E-45 to 3.402823E+38 for positive values (rounds at 7 characters)
'Double 8 bytes Integer values -1.79769313486232E+308 to -4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E+308 for positive values. (rounds at 15 characters)
'Currency 8 bytes Monutary value -922,337,203,685,477.5808 to 922,337,203,685,477. (Limited to a max of 15 characters to the left of the decimal and 4 to the right)
'Decimal 12 bytes Integer values –79,228,162,514,264,337,593,543,950,335 to 79,228,162,514,264,337,593,543,950,335 (rounds at 28 characters)
'String 10+ bytes, + String length* Value as text, by default String can store up to 2 billion characters, but can be fixed if stated "As string * 7", 7 being the fixed number of characters. This feature is limited to 65,535 characters
'Date 8 bytes Value representing date, time, or both. Date range January 1, 100 to December 31, 9999
'Boolean 2 bytes True or False, by default value is false. Can respond to integers where if value = 0 the Boolean variable is FALSE, anything else equals TRUE
'Object 4 bytes An object reference, such as a specific program or sheet within the workbook
'Variant 16-22 bytes, + String length* Flexible value, variant adapts to value you're using by guessing a what it should be, if a number is present it will play at an integer type while if it's text in quotes it will be a text.
'Note: Variant is highly useful when you're uncertain of the variables data type but uses the most storage which will slow down the macro
'* Storage may increase to accomdate the String
'Certain Variable types must be defined using specific commands unless otherwise designated (i.e. referencing a cell or trim from another source), reference the examples below for further detail:
xxxxxxxxxxxxxxxxxxxxxxxx
'String type
example = "Text should be within quotes"
xxxxxxxxxxxxxxxxxxxxxxxx
'Date type
'Date
example = #12/12/2012#
'Time
exmaple = #12:35:00 PM#
'Date & Time
example = #12/12/2012 12:35:00 PM#
xxxxxxxxxxxxxxxxxxxxxxxx
'Boolean
Dim TrueOrFalse As Boolean
TrueOrFalse = True
'or if desired, not required as it defaults to false
TrueOrFalse = False
'Using Values for Boolean
'TrueOrFalse = rows("A1")
'If A1 is equal to 0 the variable will stand for FALSE, anything else the variable will stand for TRUE
xxxxxxxxxxxxxxxxxxxxxxx
'Object
'The word SET must be in front of the object variable when defining it's value, or object to reference
Dim Grabthat As Object
Set Grabthat = Worksheets("Sheet1")
I would also try out these links, super helpful!
https://msdn.microsoft.com/en-us/library/office/ff604039(v=office.14).aspx
17 ways to Optimize VBA Code for FASTER Macros
If you need more, again, just let me know!
I'm no expert, but I try to help others when I can. I understand this stuff as I go, but my job has forced me to learn unique ways of making it work for me.
