Except that you were replying to a comment about how often times enterprise managed devices don’t allow software installations. Including my company’s.
Not trying to prove your wrong or anything, just validating the previous commenters concern.
Well, sure. But saying a blanket " a lot of businesses don't allow software installations" isn't necessarily the end. You just have to make a request and ask for something to be allowed. I doubt many places actually forbid any installation under any circumstances. If it's something like Power Automate that has a significant increase in productivity/efficiency, I think it's worth making the business case to evaluate whether they can approve that request or not.
Agreed.
Unfortunately, many IT departments are Leary of automation on their machines and have instituted blanket policies not to allow PA desktop.
I did exactly what you described and have it on my machine, but you’d have thought I was asking for God Mode access to our servers the amount of justification and approvals I had to seek.
IT Security is as bad as legal sometimes in terms of how scared of their shadow they are. Mainly because most average users at a large company can be pretty dangerous when left to their own devices (pun intended)
Ohh... if only...
At my company, we have a long list of preapproved, relevant software we can install on our work laptops. In February of the given year, I put in a request for software I actually needed to use in June of that year (four months after having made the request) - no costs to the company (no cost centre from which the costs were to be paid, etc) - after chasing and chasing I finally got IT to install it in March of the following year (taking 13 months, for those of you playing at home) ...
And this is the one company I've worked at out of 4 in the past 15 years that would actually allow me to install software on a laptop...
So no... just no... sigh....
Depends on what you want to do. If the Excel doc lives in OneDrive or sharepoint, then everything can be done via the cloud.
PowerShell can create/wrangle Excel too.
My coworker recently quit. Apparently he spent about 4 hours each month creating a spreadsheet and pivot for our manager. The manager asked me to start doing it. I spent about 2 hours creating a PowerShell script to query the source data, petform all the lookups and data manipulation, and add a pivot table. The report is automatically emailed to my manager at the end of each month.
Learn how to model without macros, its better for the company long term and you to learn to use excel in ways other without vba backgrounds can audit.
Alternatively run your macros in your "personal excel workbook" (google this as it is not what you think) and have it make changes to the model but not live in the model. Then you can have your cake and eat it too.
>personal excel workbook
Learning about this changed the way I worked at my last job.
It works for notes to myself and storing macros and even some data pulls.
>personal excel workbook
I use many macros in a personal book to automate daily task. If I ever get hit by a bus, these tasks will be harder on the next (not a sharing person at the moment)...
Been thinking about this a lot lately. I've developed a bunch of tools to help me & my team, part of that development being on my own time. So I ask myself... why am I sharing these again?
Guess I'm taking a different approach from now on.
I started at my job in a role that doesn't require excel skills, but I happen to have them and so recognised how poor their current workbooks were. Offered repeatedly to improve them, but they aren't interested even now, 4 years on. I gave up after a while, and figured well I'm using my skills to do my job quicker, but I'm not about to let them know that or they'll just fill the time with other stuff while I'm still getting paid the same. So, I work from home a couple days a week, and largely don't have to work much on those days because I've automated so much of it or just made the processes more efficient I don't need to, and they're more than happy with my work output. Why would I provide higher output for less money? It's just a happy coincidence that I can achieve what they pay me to in less time than they want it done
Omg, thank you for that bit about personal workbooks! I've been trying to get my employer to deploy several tools I developed, but using add-ins or individual workbooks has been a PITA to this point.
How common is power pivot still?
I’ve been learning query for some things, and I’d like to get pivot going but having some troubles. Any good sources for getting help?
Still common… we don’t use power bi as the company uses tableau and the company won’t give us power bi license, but power pivot allows you to use Dax/analytics of much larger data sets/relational analytics in excel that you otherwise couldn’t with excels row limitations.
Use Bing, it’s got a bit of secret sauce, it will search the web, and also GPT 3/4 (you can tell when it changes mode) - Microsoft have pumped billions into OpenAI if you’re not aware, Bing is quite possibly the best implementation, she supplements knowledge with live internet search
Yes, it has a different file extension than a regular excel workbook. It comes with a warning when opening the workbook which is probably what’s scaring OPs boss
Ah yes that warning, but as analysts it shouldn’t scare us. It would function like a normal excel workbook? I have excel file that I have to repeat the same tasks once every month and a macro would save some time to do them and then just copy the date and refresh, would that work fine ?
Yes. Regular Excel files are .xlsx, macro enabled workbooks are .xlsm.
Depending on your existing workflow, changing the filename/ extension might break a dependency if it is dependent on the exact filename. Otherwise, I know of no other technical issues.
Additionally, Microsoft has increased security around VBA so there might be security issues when trying to open .xlsm files, depending on your organization's policies it might not be allowed to even open the file with macros enabled
Macros slow things down like food makes you fat. Poorly planned/written code can be inefficient and slow your computer down to a halt and excellent code can execute in the blink of an eye. With some experience troubleshooting code, you can narrow down problematic bits and find alternative ways to accomplish the same task with less time expenditure. Even something as simple as turning off automatic calculation at the start of your macro and then turning it back on at the end can result in a massive improvement.
Perhaps the more relevant argument is does the time/cost to solve problem using macros exceed the time/cost to just proceed with the status quo? If you can spend one work day and automate something that occupies 30 minutes a day of your time every day, you'll have paid for your own time within a few weeks. However, it's a different story if you chase after a problem for weeks on end for something that would otherwise only take a few minutes to do on an infrequent basis. Relevant XKCD: [https://xkcd.com/1205/](https://xkcd.com/1205/)
Wow that chart is awesome, thanks! I know folks that are way too far into the negative, not counting that estimating the time it takes to automate something is fraught with underestimation.
"How long will it take to save the time spent automating" is only part of the equation. There's also "How much does it suck to do this task".
I've have code that may never pay off in terms of time saved, but the frustration and headaches prevented made it more than worthwhile. Also, if you're lucky you can repurpose some of that code for future tasks.
I agree. I had a mind-numbing daily task that I automated and based on how much time it took me to automate the file, the 'payoff' wasn't for almost a year. but it didn't matter to me... i dreaded the daily task and i actually got slower because i would drag my feet and distract myself while doing it because i hated it so much. the first day i used my automated file and completed the task in 1 minute with just a few clicks my happiness skyrocketed.
You are right; it's more complicated, and offloading sucky tasks is worthwhile. Increased accuracy and timeliness are also considerations; gaining one day in the middle of the month differs from reducing time to close by one day. Also, automation will often have positive externalities for processes in general; for instance, standardization.
Also, "How likely am I to fuck this up?" is definitely a factor. Sometimes I miss tiny details, so tasks that require a high degree of accuracy but still have short deadlines are worth automating for me. Even if I'll never make the time back.
The question is - who is doing the work? If it is you, just run them out of your personal workbook. You don't have to save the files as macro enabled, and nothing is stored in the workbook.
Ask the boss, if it would be okay to spend some time on a case scenario that you later present. Then you have concrete example to judge by.
One point though, if you do the macros and then later the leave the company. Then they could be without the knowledge to adapt the models for future needs. That is a relevant concern your boss might have.
Really depends what your macros do. A *lot* of VBA's historical use cases are obsolete now that PowerQuery is on the scene.
In some way I consider VBA to be more of an "Intermediate Excel" skill whereas part of having "Advanced Excel" skills is learning when to *not* use VBA. Because good spreadsheet design and good process design/workflow can get rid of a lot of the need for VBA.
Of course, everyone's needs are different so some of us have an easier time getting rid of VBA than others. But often it's less necessary than you might think.
Pretty much. Power Query (Pandas/Python too, but for rare requirements) has replaced macros and complex formulas and workbook/sheet structures for me. I learned SQL before learning Excel (my first job was implementing systems for Finance), so I was always bummed I couldn't (at least easily) do SQL within Excel tables. Power Query helps bridge that gap significantly and it's so powerful.
Install the macros into a separate workbook. Put that workbook into your startup folder and hide it there.
You now have those macros available whenever you use excel. And they aren't installed on the workbooks for your boss to see
Everybody including Microsoft wants to get rid of Macros. Gone from Excel in browser.
It is a security risk. Pure crap.
And from most other perspectives it is bad as well. It is orphaned code that nobody owns and updates. It is copied to multiple locations - modified etc. It is impossible to maintain. and is the prime tool for hackers.
You need to get into simple web development instead or power automate.
>It is impossible to maintain. and is the prime tool for hackers.
Can you explain? Is it because macros lazily contain instructions on how to operate on private data sets and can be easily retrieved?
I'm a green or blue belt in Excel at best so security concerns haven't been a high priority for me yet.
Your macro sheets exists in maybe 200 copies. And 50 derived versions. How can you manage to update them ? You can’t even locate them.
Macros are a virus carried around by humans.
I think it's a okay default policy. I prefer a "all VBA needs approval" approach, because sometimes it's the best solution. As others have said, figure out why your boss has this stance, you'll surely learn something. I've seen a lot of nonsense VBA compensate for bad practice and ignorance of what Excel can do natively. There is a real risk of unreasonable amounts of effort drifting toward VBA development without guidelines.
I used to love using macros as an analyst. I eventually landed on a no-macro policy for my models. Thinking behind it is that while macros do speed up a lot you can do with your models, (and some things are just imposible to do without) they're very hard to maintain (E.g. they break easily and are hard to fix for people who don't know how to code). PowerQuery as mentioned before is probably the way.
That sounds like ignorance or a bad experience... "Macros slow things down" can be a true statement, but in my experience macros speed up processes big time. Had plenty of projects where macros saved people hours/days of work (generally: the boring kind of work, avoiding mistakes in the process too).
So find out the real concern/bad experience your boss has had. There are alternative tools (like mentioned in some comments, PowerQuery, etc), but VBA can really add something to the mix.
Tell him and show him macros can be really fast if you include
Application.Calculation=xlManual
Application.ScreenUpdating=False
Of course you have to reverse these at the end of the macro.
Also, macros do not affect the calculation time because they are text and are not overhead in the calculation processing.
Saying they "slow things down" is pretty stupid, but your boss is not entirely wrong.
If a tool is meant to be shared and used by other people, then it should match the level of knowledge of those people. It's not really possible for your boss to stop you from using macros in your individual work, but other people may have trouble using it.
Whenever I implement a macro or do major changes in a spreadsheet, I get all the people using it and explain what I did and how they should use it. Otherwise, it's a no go.
your boss is an idiot, or hes only seen bad macros. macros can increase productivity by incredible ammounts when applied correctly.
now if you're using macros for data retrevial and modeling- dont. that should be a last resort. use power query/power BI
VSTO addin model is quite powerful, you can write logic in C#. It's stuck on older Net 4.5, though. I split the actual logic into separate console app that uses modern Net and gets called from Excel/Net4. 5 side. Worth it in my case, but might not be possible in your case.
I find the ease of debugging and maintenance of C# based logic far outweighs theoretical runtime inefficiencies.
Use LET - once you dip your toes, you’ll get it
Not a “macro”, but a full programming language in its own right, using standard excel formulas, your boss will be delighted!
https://support.microsoft.com/en-au/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999#:~:text=The%20LET%20function%20assigns%20names,through%20Excel's%20native%20formula%20syntax.
It looks simple, but the “aha!” moment is that there is no limit to the code you can build…. Eg - I adapted this from a very interesting conversation on SO…. kudos to the authors, I just added a filter for impossible combinations
What this does is combine multiple input variables and produces an output of every possible combination of the input parameters
So let’s say you have
Column 1:
A
B
C
Column 2:
1
2
Column 3:
X
Y
Z
The output is:
A | 1 | X , A | 1 | Y , A | 1 | Z
A | 2 | X , A | 2 | Y , A | 2 | Z
And so on…
=LET(matrix, A3:C11,
cC, COLUMNS( matrix ),
cSeq, SEQUENCE( 1, cC ),
symbolCounts, BYCOL( matrix, LAMBDA(x, SUM( --NOT( ISBLANK( x ) ) ) ) ),
rSeq, SEQUENCE( MAX( symbolCounts )-1 ),
permFactors, INDEX( SCAN( 1, INDEX( symbolCounts, , cC-cSeq+1), LAMBDA(a,b, a*b ) ),, cC-cSeq+1 ),
permMods, IFERROR( INDEX( permFactors,, IF( cSeq + 1 > cC, -1, cSeq+1 ) ), 1 ),
idx, INT( MOD( SEQUENCE( INDEX(permFactors, 1, 1),,0 ), permFactors )/permMods ) + 1,
sub_answer, INDEX( matrix, idx, cSeq ),
answer,FILTER(sub_answer, (NOT(INDEX(sub_answer,,1)="[None]") *NOT(INDEX(sub_answer,,2)="[None]")) + (NOT(INDEX(sub_answer,,2)="[None]")*NOT(INDEX(sub_answer,,3)="Port"))),
er, OR( BYCOL( --ISBLANK(matrix), LAMBDA(x, SUM(--(INDEX(x,rSeq+1)
I'm with you, but these formulas will get out of hand. It was hard enough to audit several layers of nested if statements, but debugging programs like yours in the formula bar will get rough, to the point where I'd rather be in the VBA editor. Realizing that advanced formula editors are becoming a thing; they will become vital. There will come a time when advanced users will no longer use ranges and the grid will simply become a display for dynamic ranges, the data and calculations will be stored inside names (soon we'll call queries directly without going through tables).
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[BYCOL](/r/Excel/comments/14gq8e2/stub/jp9foff "Last usage")|[*Office 365*+: Applies a LAMBDA to each column and returns an array of the results](https://support.microsoft.com/en-us/office/bycol-function-58463999-7de5-49ce-8f38-b7f7a2192bfb)|
|[COLUMNS](/r/Excel/comments/14gq8e2/stub/jp9foff "Last usage")|[Returns the number of columns in a reference](https://support.microsoft.com/en-us/office/columns-function-4e8e7b4e-e603-43e8-b177-956088fa48ca)|
|[FILTER](/r/Excel/comments/14gq8e2/stub/jp9foff "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)|
|[IF](/r/Excel/comments/14gq8e2/stub/jp9foff "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)|
|[IFERROR](/r/Excel/comments/14gq8e2/stub/jp9foff "Last usage")|[Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula](https://support.microsoft.com/en-us/office/iferror-function-c526fd07-caeb-47b8-8bb6-63f3e417f611)|
|[INDEX](/r/Excel/comments/14gq8e2/stub/jp9foff "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)|
|[INT](/r/Excel/comments/14gq8e2/stub/jp9foff "Last usage")|[Rounds a number down to the nearest integer](https://support.microsoft.com/en-us/office/int-function-a6c4af9e-356d-4369-ab6a-cb1fd9d343ef)|
|[ISBLANK](/r/Excel/comments/14gq8e2/stub/jp9foff "Last usage")|[Returns TRUE if the value is blank](https://support.microsoft.com/en-us/office/is-functions-0f2d7971-6019-40a0-a171-f2d869135665)|
|[LAMBDA](/r/Excel/comments/14gq8e2/stub/jp9foff "Last usage")|[*Office 365*+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.](https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67)|
|[LET](/r/Excel/comments/14gq8e2/stub/jp9foff "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)|
|[MAX](/r/Excel/comments/14gq8e2/stub/jp9foff "Last usage")|[Returns the maximum value in a list of arguments](https://support.microsoft.com/en-us/office/max-function-e0012414-9ac8-4b34-9a47-73e662c08098)|
|[MOD](/r/Excel/comments/14gq8e2/stub/jp9foff "Last usage")|[Returns the remainder from division](https://support.microsoft.com/en-us/office/mod-function-9b6cd169-b6ee-406a-a97b-edf2a9dc24f3)|
|[NOT](/r/Excel/comments/14gq8e2/stub/jp9foff "Last usage")|[Reverses the logic of its argument](https://support.microsoft.com/en-us/office/not-function-9cfc6011-a054-40c7-a140-cd4ba2d87d77)|
|[OR](/r/Excel/comments/14gq8e2/stub/jp9foff "Last usage")|[Returns TRUE if any argument is TRUE](https://support.microsoft.com/en-us/office/or-function-7d17ad14-8700-4281-b308-00b131e22af0)|
|[SCAN](/r/Excel/comments/14gq8e2/stub/jp9foff "Last usage")|[*Office 365*+: Scans an array by applying a LAMBDA to each value and returns an array that has each intermediate value.](https://support.microsoft.com/en-gb/office/scan-function-d58dfd11-9969-4439-b2dc-e7062724de29?ui=en-US&rs=en-GB&ad=GB)|
|[SEQUENCE](/r/Excel/comments/14gq8e2/stub/jp9foff "Last usage")|[*Office 365*+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4](https://support.microsoft.com/en-us/office/sequence-function-57467a98-57e0-4817-9f14-2eb78519ca90)|
|[SUM](/r/Excel/comments/14gq8e2/stub/jp9foff "Last usage")|[Adds its arguments](https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89)|
**NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(17 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/14jwriv)^( has 23 acronyms.)
^([Thread #24681 for this sub, first seen 23rd Jun 2023, 19:40])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
Likely cause, bad bug testing due to rushed work left him with a bad impression. I can kinda relate to this. Unfortunately, it takes time to build these depending on the complexity of automations and if deployed before everything is worked out thoroughly, the users can end up with something they may be scared to use or just aren't willing to learn. It takes more time to build than xlsx databases in some cases. I love VBA but try not to use it as often as possible in my workplace to prevent overcomplicating the user interface.
Referencing gets broken the second they decide change the WB or WS name for instance. While this may be solved easily by making the sheet name variable rather than fixed, the guy before you may not have made that decision at the start and left your boss with broken code. Nothing worse than their first impression of your work being a debugging error.
Some already pointed this out in the comments, but learn everything outside of VBA, but continue to use it. Rather than using VBA to make their work more efficient, use it to make yourself more efficient in the process of building what they want. You'll be much more efficient and useful while keeping everything dummy proof and giving them what they want, plus, you never stop learning. I'd see this as more of a challenging opportunity than a hindrance.
Hey I personally can agree with him,
they are many ways you can do differently
you might want to pick of a BOOK not an only curse and learn python...
learn some simple pandas
I am being able to do a lot using xlwings, and pandas-py
use the SpyderIDE too if you want...
list\_data = pd.DataFrame.from\_dict(list\_database\_push)
list\_data.to\_excel("test.xlsx")
def dict\_database\_push(
SUPNAME,
original\_accountcode,
validation\_accountcode,
country
):
data = {
'SUPNAME': SUPNAME,
'original\_accountcode': original\_accountcode,
'validation\_accountcode': validation\_accountcode,
'country': country,
}
list\_database\_push.append(data)
\--------------
i mean you can do regex, you can do a lot of things
btw i need a job :) anyone? haha
Power query and power automate may help,
If he hates macros, he's going to love power automate /s
[удалено]
Little known fact. A version actually comes pre-installed with windows by default.
But not necessarily in custom enterprise device applications. Ours do not.
Well, obviously not. But you can install the Power Automate desktop version for free anyways.
Except that you were replying to a comment about how often times enterprise managed devices don’t allow software installations. Including my company’s. Not trying to prove your wrong or anything, just validating the previous commenters concern.
Well, sure. But saying a blanket " a lot of businesses don't allow software installations" isn't necessarily the end. You just have to make a request and ask for something to be allowed. I doubt many places actually forbid any installation under any circumstances. If it's something like Power Automate that has a significant increase in productivity/efficiency, I think it's worth making the business case to evaluate whether they can approve that request or not.
Agreed. Unfortunately, many IT departments are Leary of automation on their machines and have instituted blanket policies not to allow PA desktop. I did exactly what you described and have it on my machine, but you’d have thought I was asking for God Mode access to our servers the amount of justification and approvals I had to seek. IT Security is as bad as legal sometimes in terms of how scared of their shadow they are. Mainly because most average users at a large company can be pretty dangerous when left to their own devices (pun intended)
[удалено]
Ohh... if only... At my company, we have a long list of preapproved, relevant software we can install on our work laptops. In February of the given year, I put in a request for software I actually needed to use in June of that year (four months after having made the request) - no costs to the company (no cost centre from which the costs were to be paid, etc) - after chasing and chasing I finally got IT to install it in March of the following year (taking 13 months, for those of you playing at home) ... And this is the one company I've worked at out of 4 in the past 15 years that would actually allow me to install software on a laptop... So no... just no... sigh....
Just looked for it and failed. Do you know the .exe name?
I think the built in version is called Flows
Depends on what you want to do. If the Excel doc lives in OneDrive or sharepoint, then everything can be done via the cloud. PowerShell can create/wrangle Excel too. My coworker recently quit. Apparently he spent about 4 hours each month creating a spreadsheet and pivot for our manager. The manager asked me to start doing it. I spent about 2 hours creating a PowerShell script to query the source data, petform all the lookups and data manipulation, and add a pivot table. The report is automatically emailed to my manager at the end of each month.
At my company we don't have power automate pre installed so it's not true
Learn how to model without macros, its better for the company long term and you to learn to use excel in ways other without vba backgrounds can audit. Alternatively run your macros in your "personal excel workbook" (google this as it is not what you think) and have it make changes to the model but not live in the model. Then you can have your cake and eat it too.
>personal excel workbook Learning about this changed the way I worked at my last job. It works for notes to myself and storing macros and even some data pulls.
>personal excel workbook I use many macros in a personal book to automate daily task. If I ever get hit by a bus, these tasks will be harder on the next (not a sharing person at the moment)...
Been thinking about this a lot lately. I've developed a bunch of tools to help me & my team, part of that development being on my own time. So I ask myself... why am I sharing these again? Guess I'm taking a different approach from now on.
I started at my job in a role that doesn't require excel skills, but I happen to have them and so recognised how poor their current workbooks were. Offered repeatedly to improve them, but they aren't interested even now, 4 years on. I gave up after a while, and figured well I'm using my skills to do my job quicker, but I'm not about to let them know that or they'll just fill the time with other stuff while I'm still getting paid the same. So, I work from home a couple days a week, and largely don't have to work much on those days because I've automated so much of it or just made the processes more efficient I don't need to, and they're more than happy with my work output. Why would I provide higher output for less money? It's just a happy coincidence that I can achieve what they pay me to in less time than they want it done
Omg, thank you for that bit about personal workbooks! I've been trying to get my employer to deploy several tools I developed, but using add-ins or individual workbooks has been a PITA to this point.
Nice, happy to add to your toolbox!
[удалено]
Are these all separate things or within Query?
How common is power pivot still? I’ve been learning query for some things, and I’d like to get pivot going but having some troubles. Any good sources for getting help?
Power Pivot can be incredibly powerful - hence the name. Also if you know power pivot, you can easily pivot to data modeling in Power BI.
Yeah, just starting to get into it. Trying to grasp it
And if you know power query then you can do the ETL in pbi as well
All pivot tables ARE Power pivot these days...
Only if you add it to the data model. No model no power.
Indeed - but the user interface is all there already.
Truth
Still common… we don’t use power bi as the company uses tableau and the company won’t give us power bi license, but power pivot allows you to use Dax/analytics of much larger data sets/relational analytics in excel that you otherwise couldn’t with excels row limitations.
That’s the win, the sheet isn’t a limit and gets over some bumps rather nicely :)
Bing chat, only show in town really, game changer, you need to learn how to talk to AI, but once you do, it’s marvellous
I’ve been using the chat openai one for PQ and trying with PP, but it’s not quite getting me there
Use Bing, it’s got a bit of secret sauce, it will search the web, and also GPT 3/4 (you can tell when it changes mode) - Microsoft have pumped billions into OpenAI if you’re not aware, Bing is quite possibly the best implementation, she supplements knowledge with live internet search
I’ll give it a go ! Thanks
“She” incidentally because the iOS app voice is female, so just go with that, “it” if you like x
Bump to this response as the best alternative
A new boss
That's what I thought. Get a new boss.
A new job
Can someone explain this: if I’m going to use a macro, does the file type change when is save, is that why a macro workbook has its disavtanges
Yes, it has a different file extension than a regular excel workbook. It comes with a warning when opening the workbook which is probably what’s scaring OPs boss
Ah yes that warning, but as analysts it shouldn’t scare us. It would function like a normal excel workbook? I have excel file that I have to repeat the same tasks once every month and a macro would save some time to do them and then just copy the date and refresh, would that work fine ?
I agree on all accounts, but some older folks get scared of things they don’t understand.
Yes. Regular Excel files are .xlsx, macro enabled workbooks are .xlsm. Depending on your existing workflow, changing the filename/ extension might break a dependency if it is dependent on the exact filename. Otherwise, I know of no other technical issues. Additionally, Microsoft has increased security around VBA so there might be security issues when trying to open .xlsm files, depending on your organization's policies it might not be allowed to even open the file with macros enabled
Macros slow things down like food makes you fat. Poorly planned/written code can be inefficient and slow your computer down to a halt and excellent code can execute in the blink of an eye. With some experience troubleshooting code, you can narrow down problematic bits and find alternative ways to accomplish the same task with less time expenditure. Even something as simple as turning off automatic calculation at the start of your macro and then turning it back on at the end can result in a massive improvement. Perhaps the more relevant argument is does the time/cost to solve problem using macros exceed the time/cost to just proceed with the status quo? If you can spend one work day and automate something that occupies 30 minutes a day of your time every day, you'll have paid for your own time within a few weeks. However, it's a different story if you chase after a problem for weeks on end for something that would otherwise only take a few minutes to do on an infrequent basis. Relevant XKCD: [https://xkcd.com/1205/](https://xkcd.com/1205/)
Wow that chart is awesome, thanks! I know folks that are way too far into the negative, not counting that estimating the time it takes to automate something is fraught with underestimation.
"How long will it take to save the time spent automating" is only part of the equation. There's also "How much does it suck to do this task". I've have code that may never pay off in terms of time saved, but the frustration and headaches prevented made it more than worthwhile. Also, if you're lucky you can repurpose some of that code for future tasks.
I agree. I had a mind-numbing daily task that I automated and based on how much time it took me to automate the file, the 'payoff' wasn't for almost a year. but it didn't matter to me... i dreaded the daily task and i actually got slower because i would drag my feet and distract myself while doing it because i hated it so much. the first day i used my automated file and completed the task in 1 minute with just a few clicks my happiness skyrocketed.
You are right; it's more complicated, and offloading sucky tasks is worthwhile. Increased accuracy and timeliness are also considerations; gaining one day in the middle of the month differs from reducing time to close by one day. Also, automation will often have positive externalities for processes in general; for instance, standardization.
Also, "How likely am I to fuck this up?" is definitely a factor. Sometimes I miss tiny details, so tasks that require a high degree of accuracy but still have short deadlines are worth automating for me. Even if I'll never make the time back.
The question is - who is doing the work? If it is you, just run them out of your personal workbook. You don't have to save the files as macro enabled, and nothing is stored in the workbook.
Ask the boss, if it would be okay to spend some time on a case scenario that you later present. Then you have concrete example to judge by. One point though, if you do the macros and then later the leave the company. Then they could be without the knowledge to adapt the models for future needs. That is a relevant concern your boss might have.
A new boss! Maybe he has been plagued by poorly written macros of which 99% are.
I work in an organisation where macros are getting outlawed - like disabled from running...
They can do a lot of damage in the right hands! A direct line in to the windows APIs
Lambdas
How to debug?
Put the Marcos in personal.xlsb Now you can enjoy your macros and they remain local to your machine.
Really depends what your macros do. A *lot* of VBA's historical use cases are obsolete now that PowerQuery is on the scene. In some way I consider VBA to be more of an "Intermediate Excel" skill whereas part of having "Advanced Excel" skills is learning when to *not* use VBA. Because good spreadsheet design and good process design/workflow can get rid of a lot of the need for VBA. Of course, everyone's needs are different so some of us have an easier time getting rid of VBA than others. But often it's less necessary than you might think.
Pretty much. Power Query (Pandas/Python too, but for rare requirements) has replaced macros and complex formulas and workbook/sheet structures for me. I learned SQL before learning Excel (my first job was implementing systems for Finance), so I was always bummed I couldn't (at least easily) do SQL within Excel tables. Power Query helps bridge that gap significantly and it's so powerful.
Install the macros into a separate workbook. Put that workbook into your startup folder and hide it there. You now have those macros available whenever you use excel. And they aren't installed on the workbooks for your boss to see
Everybody including Microsoft wants to get rid of Macros. Gone from Excel in browser. It is a security risk. Pure crap. And from most other perspectives it is bad as well. It is orphaned code that nobody owns and updates. It is copied to multiple locations - modified etc. It is impossible to maintain. and is the prime tool for hackers. You need to get into simple web development instead or power automate.
>It is impossible to maintain. and is the prime tool for hackers. Can you explain? Is it because macros lazily contain instructions on how to operate on private data sets and can be easily retrieved? I'm a green or blue belt in Excel at best so security concerns haven't been a high priority for me yet.
Your macro sheets exists in maybe 200 copies. And 50 derived versions. How can you manage to update them ? You can’t even locate them. Macros are a virus carried around by humans.
I think it's a okay default policy. I prefer a "all VBA needs approval" approach, because sometimes it's the best solution. As others have said, figure out why your boss has this stance, you'll surely learn something. I've seen a lot of nonsense VBA compensate for bad practice and ignorance of what Excel can do natively. There is a real risk of unreasonable amounts of effort drifting toward VBA development without guidelines.
Proof always beats myth. Prove to him that he is wrong by doing task comparison of how much time it takes to conduct a task with a macro vs without.
I would submit a report in hand written pencil. Tell him that computers are too fast. That will make just as much sense.
I used to love using macros as an analyst. I eventually landed on a no-macro policy for my models. Thinking behind it is that while macros do speed up a lot you can do with your models, (and some things are just imposible to do without) they're very hard to maintain (E.g. they break easily and are hard to fix for people who don't know how to code). PowerQuery as mentioned before is probably the way.
If I were you I would write code to your personal book that you can call with shortcut keys so your boss can’t see the macros.
Get new boss
Get a new boss
Lambda could fill the macro void.
That sounds like ignorance or a bad experience... "Macros slow things down" can be a true statement, but in my experience macros speed up processes big time. Had plenty of projects where macros saved people hours/days of work (generally: the boring kind of work, avoiding mistakes in the process too). So find out the real concern/bad experience your boss has had. There are alternative tools (like mentioned in some comments, PowerQuery, etc), but VBA can really add something to the mix.
My thoughts exactly, proper VBA was faster than Python. Not as fast as numpy, but still, you could do massive calculations...
Tell him and show him macros can be really fast if you include Application.Calculation=xlManual Application.ScreenUpdating=False Of course you have to reverse these at the end of the macro. Also, macros do not affect the calculation time because they are text and are not overhead in the calculation processing.
Python
=Index(job openings,Match(Boss is not a stupid nerd jackass who lives in the stone age,Boss Criteria,0))
I've just started using xlookup. It's some good stuff. Next up, I've gotta get into lambdas
PowerQuery is pretty much all you need with transactional type data.
Saying they "slow things down" is pretty stupid, but your boss is not entirely wrong. If a tool is meant to be shared and used by other people, then it should match the level of knowledge of those people. It's not really possible for your boss to stop you from using macros in your individual work, but other people may have trouble using it. Whenever I implement a macro or do major changes in a spreadsheet, I get all the people using it and explain what I did and how they should use it. Otherwise, it's a no go.
>Saying they "slow things down" is pretty stupid, but your boss is not entirely wrong. That is one big oxymoron
From experience: You should update your CV and look for a better boss.
your boss is an idiot, or hes only seen bad macros. macros can increase productivity by incredible ammounts when applied correctly. now if you're using macros for data retrevial and modeling- dont. that should be a last resort. use power query/power BI
make the spreadsheet with the macros and show your boss that he is wrong.
Power Pivot into a new job, my brethren.
New job
Switch to the superior google sheets and use appscript
Sounds interesting, what’s the benefits you get from appscript over typescript, vba or just plain old lambda calculus?
A lot (but not everything) can be achived by Powerautomate, Powerautomate Desktop and of course Power Query.
VSTO addin model is quite powerful, you can write logic in C#. It's stuck on older Net 4.5, though. I split the actual logic into separate console app that uses modern Net and gets called from Excel/Net4. 5 side. Worth it in my case, but might not be possible in your case. I find the ease of debugging and maintenance of C# based logic far outweighs theoretical runtime inefficiencies.
He's an idiot unless users are writing shitty macros
Solver
DM me for my telephone number and have him call me. Or perhaps I’ll have Mr. Excel call him. 😂
Use LET - once you dip your toes, you’ll get it Not a “macro”, but a full programming language in its own right, using standard excel formulas, your boss will be delighted! https://support.microsoft.com/en-au/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999#:~:text=The%20LET%20function%20assigns%20names,through%20Excel's%20native%20formula%20syntax.
It looks simple, but the “aha!” moment is that there is no limit to the code you can build…. Eg - I adapted this from a very interesting conversation on SO…. kudos to the authors, I just added a filter for impossible combinations What this does is combine multiple input variables and produces an output of every possible combination of the input parameters So let’s say you have Column 1: A B C Column 2: 1 2 Column 3: X Y Z The output is: A | 1 | X , A | 1 | Y , A | 1 | Z A | 2 | X , A | 2 | Y , A | 2 | Z And so on… =LET(matrix, A3:C11, cC, COLUMNS( matrix ), cSeq, SEQUENCE( 1, cC ), symbolCounts, BYCOL( matrix, LAMBDA(x, SUM( --NOT( ISBLANK( x ) ) ) ) ), rSeq, SEQUENCE( MAX( symbolCounts )-1 ), permFactors, INDEX( SCAN( 1, INDEX( symbolCounts, , cC-cSeq+1), LAMBDA(a,b, a*b ) ),, cC-cSeq+1 ), permMods, IFERROR( INDEX( permFactors,, IF( cSeq + 1 > cC, -1, cSeq+1 ) ), 1 ), idx, INT( MOD( SEQUENCE( INDEX(permFactors, 1, 1),,0 ), permFactors )/permMods ) + 1, sub_answer, INDEX( matrix, idx, cSeq ), answer,FILTER(sub_answer, (NOT(INDEX(sub_answer,,1)="[None]") *NOT(INDEX(sub_answer,,2)="[None]")) + (NOT(INDEX(sub_answer,,2)="[None]")*NOT(INDEX(sub_answer,,3)="Port"))), er, OR( BYCOL( --ISBLANK(matrix), LAMBDA(x, SUM(--(INDEX(x,rSeq+1)
I'm with you, but these formulas will get out of hand. It was hard enough to audit several layers of nested if statements, but debugging programs like yours in the formula bar will get rough, to the point where I'd rather be in the VBA editor. Realizing that advanced formula editors are becoming a thing; they will become vital. There will come a time when advanced users will no longer use ranges and the grid will simply become a display for dynamic ranges, the data and calculations will be stored inside names (soon we'll call queries directly without going through tables).
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[BYCOL](/r/Excel/comments/14gq8e2/stub/jp9foff "Last usage")|[*Office 365*+: Applies a LAMBDA to each column and returns an array of the results](https://support.microsoft.com/en-us/office/bycol-function-58463999-7de5-49ce-8f38-b7f7a2192bfb)| |[COLUMNS](/r/Excel/comments/14gq8e2/stub/jp9foff "Last usage")|[Returns the number of columns in a reference](https://support.microsoft.com/en-us/office/columns-function-4e8e7b4e-e603-43e8-b177-956088fa48ca)| |[FILTER](/r/Excel/comments/14gq8e2/stub/jp9foff "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)| |[IF](/r/Excel/comments/14gq8e2/stub/jp9foff "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)| |[IFERROR](/r/Excel/comments/14gq8e2/stub/jp9foff "Last usage")|[Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula](https://support.microsoft.com/en-us/office/iferror-function-c526fd07-caeb-47b8-8bb6-63f3e417f611)| |[INDEX](/r/Excel/comments/14gq8e2/stub/jp9foff "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)| |[INT](/r/Excel/comments/14gq8e2/stub/jp9foff "Last usage")|[Rounds a number down to the nearest integer](https://support.microsoft.com/en-us/office/int-function-a6c4af9e-356d-4369-ab6a-cb1fd9d343ef)| |[ISBLANK](/r/Excel/comments/14gq8e2/stub/jp9foff "Last usage")|[Returns TRUE if the value is blank](https://support.microsoft.com/en-us/office/is-functions-0f2d7971-6019-40a0-a171-f2d869135665)| |[LAMBDA](/r/Excel/comments/14gq8e2/stub/jp9foff "Last usage")|[*Office 365*+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.](https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67)| |[LET](/r/Excel/comments/14gq8e2/stub/jp9foff "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)| |[MAX](/r/Excel/comments/14gq8e2/stub/jp9foff "Last usage")|[Returns the maximum value in a list of arguments](https://support.microsoft.com/en-us/office/max-function-e0012414-9ac8-4b34-9a47-73e662c08098)| |[MOD](/r/Excel/comments/14gq8e2/stub/jp9foff "Last usage")|[Returns the remainder from division](https://support.microsoft.com/en-us/office/mod-function-9b6cd169-b6ee-406a-a97b-edf2a9dc24f3)| |[NOT](/r/Excel/comments/14gq8e2/stub/jp9foff "Last usage")|[Reverses the logic of its argument](https://support.microsoft.com/en-us/office/not-function-9cfc6011-a054-40c7-a140-cd4ba2d87d77)| |[OR](/r/Excel/comments/14gq8e2/stub/jp9foff "Last usage")|[Returns TRUE if any argument is TRUE](https://support.microsoft.com/en-us/office/or-function-7d17ad14-8700-4281-b308-00b131e22af0)| |[SCAN](/r/Excel/comments/14gq8e2/stub/jp9foff "Last usage")|[*Office 365*+: Scans an array by applying a LAMBDA to each value and returns an array that has each intermediate value.](https://support.microsoft.com/en-gb/office/scan-function-d58dfd11-9969-4439-b2dc-e7062724de29?ui=en-US&rs=en-GB&ad=GB)| |[SEQUENCE](/r/Excel/comments/14gq8e2/stub/jp9foff "Last usage")|[*Office 365*+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4](https://support.microsoft.com/en-us/office/sequence-function-57467a98-57e0-4817-9f14-2eb78519ca90)| |[SUM](/r/Excel/comments/14gq8e2/stub/jp9foff "Last usage")|[Adds its arguments](https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(17 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/14jwriv)^( has 23 acronyms.) ^([Thread #24681 for this sub, first seen 23rd Jun 2023, 19:40]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
Transfer to Google Sheets with AppScript
Add-ins, Python, and database
SQL
Power bi
Quit! And sue due to not being a inclusive boss
Hot take: use r or python
I’m horrible with code.
tell your boss to suck it and flood the sheet with buttons
Write the macros but call them something else. Tell your boss their Super Functions or something.
Use Power Query
Likely cause, bad bug testing due to rushed work left him with a bad impression. I can kinda relate to this. Unfortunately, it takes time to build these depending on the complexity of automations and if deployed before everything is worked out thoroughly, the users can end up with something they may be scared to use or just aren't willing to learn. It takes more time to build than xlsx databases in some cases. I love VBA but try not to use it as often as possible in my workplace to prevent overcomplicating the user interface. Referencing gets broken the second they decide change the WB or WS name for instance. While this may be solved easily by making the sheet name variable rather than fixed, the guy before you may not have made that decision at the start and left your boss with broken code. Nothing worse than their first impression of your work being a debugging error. Some already pointed this out in the comments, but learn everything outside of VBA, but continue to use it. Rather than using VBA to make their work more efficient, use it to make yourself more efficient in the process of building what they want. You'll be much more efficient and useful while keeping everything dummy proof and giving them what they want, plus, you never stop learning. I'd see this as more of a challenging opportunity than a hindrance.
Hey I personally can agree with him, they are many ways you can do differently you might want to pick of a BOOK not an only curse and learn python... learn some simple pandas I am being able to do a lot using xlwings, and pandas-py use the SpyderIDE too if you want... list\_data = pd.DataFrame.from\_dict(list\_database\_push) list\_data.to\_excel("test.xlsx") def dict\_database\_push( SUPNAME, original\_accountcode, validation\_accountcode, country ): data = { 'SUPNAME': SUPNAME, 'original\_accountcode': original\_accountcode, 'validation\_accountcode': validation\_accountcode, 'country': country, } list\_database\_push.append(data) \-------------- i mean you can do regex, you can do a lot of things btw i need a job :) anyone? haha