Complicated Macros VS. Formulas

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
I'm with you when it comes to using formulas, I prefer that method if it exists.

The reason some people prefer macros is that they want to permanently affect the data without creating another column or intermediate step.

Yes, it seems more work than it is worth, but I've just resigned myself to the fact that most of the time the posters have not fully explained themselves and I shouldn't expect that they need to.

This point really came home to me recently when I had a true need for a macro that I knew a formula could handle, but the specifics of the project demanded a macro.

I just remember the particulars: I had two dates fields (columns) that I had imported from a sequel database. For some reason, when the db was setup, the creator didn't specify the fields were actually dates, so they came in as padded text. I needed to strip the blanks. I knew that TRIM() would do the job but since I do this every week, I decided to create a macro to affect the actual data.
Upvote 0
I have asked this question on a number of circumstances myself, and most of the I find that they are asking for a VBA solution because what they are asking is really just a small part of a much larger macro, and/or they are looking to "automate" a process.
Upvote 0
I also think sometimes that all of us (myself included!) enjoy over-complicating various tasks because we then feel like we have achieved something particularly special by proposing/effecting a solution.

Best regards

Upvote 0

Superstar31, in the 2nd thread you originally linked to, what was the function you had in mind? The content of your post makes me wonder why you would even post there? If you had a function in mind, then so be it, but why not share it at that point? Incidentally, I have yet to see a worksheet function generate a MsgBox... :confused:

There are lots of reasons to automate processes and craft user-defined functions, etc... with VBA in Excel. UserForms and ActiveX controls make for nice GUIs, interfacing other Apps can streamline processes and augmenting native functionality is often called for.

I'll give you a UDF example:

A little hairy, perhaps. But, it can be used as a Worksheet Function, or in VBA, and it appears to be very efficient per my tests... Even if you came up with a W.F. combination that could match the functionality, a single, pseudo-random, return-String the length you want, you'd be hard-pressed to match the efficiency of that call...

Look at the name of sub in question: Sub Example_of_Vlookup. It appears that this person is doing what they I think they should be doing; testing smaller aspects to a process in smaller pieces before combining it all into a larger process.

My opinion: Use both; I do. :)
Upvote 0
Hey Nato,

Neither of those post where mine, but listening to others, I do see a reason to use VB/Macros over Functions/Formulas. Since I haven't learned Macro's yet, their use will be very limited and knowledge would be far less. Either way one day i'll figure out how to use them and be able to explain to people like myselfs why they are easier done and used like you guys/girls have down for me.
Upvote 0
Hello again,
Superstar31 said:
Hey Nato,

Neither of those post where mine, but listening to others, I do see a reason to use VB/Macros over Functions/Formulas.
I was thinking of/speaking to this one:

In my estimation, VBA and Worksheet Functions are both tools that ship with Excel.

Often times, VB[A] procedures utilize shipped, native-code functions as well, e.g., Right$() being part of VBA's Strings Class. And, then there are times when VBA uses Worksheet Functions...

My objective is to get Excel to do the work for me, sometimes it makes sense [to me] to do this with Worksheet Functions, and other times, VBA.

When you say complicated, keep in mind that this is in the eye of the beholder, I've seen some (what I would deem to be) complicated Worksheet Function constructs as well... :)
Upvote 0
Hi Superstar,

Since I was one involved in the second thread, (and I'm waiting for my Access+Outlook macro to run,) I'll just take a moment to respond... My interpretation of what I saw was pretty much as Nate described. His sub is named Example_of_Vlookup(). And he is simply outputting a message based on the call to app.wsf.vlookup so he's testing something here. Whether it's part of a larger block of code, or whether he's trying to create an example to teach co-workers or even if he's trying to complete a homework assignment (if he is, at least he's giving the impression of having done some work and then gotten stuck). Whatever his situation, my take on what I saw was that he would know how to write a vlookup formula in a cell. That's why in that particular case, I didn't pursue that idea.

Now, your question is still very valid. There are many times when someone gets locked into a "this has to be done with a macro" solution simply because he does not know what formulas are capable of doing. Aladin has (literally) tens of thousands of posts. He, Fairwinds and some other wunderkinder around here routinely amaze me at what they can pull off with just formulae. I'd say if it can be done by either method, but the OP is trying to use VBA, probably ½ of the time it's because he doesn't know how to do it with a formula. I'd guesstimate about ½ of the time he would know how to do it with a formula and has chosen VBA for reasons along the lines of what Nate or Joe cite above.
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back