Complicated Macros VS. Formulas

nbrcrunch

Well-known Member
Joined
Jan 1, 2003
Messages
2,062
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.

---
addendum:
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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,135
Office Version
365
Platform
Windows
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.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

Richard
 

Superstar31

Active Member
Joined
Nov 10, 2005
Messages
496
Those are some great answers, at least now I know I'm not crazy :)
 

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
Hello,

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:

http://www.mrexcel.com/board2/viewtopic.php?p=849596#849596

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. :)
 

Superstar31

Active Member
Joined
Nov 10, 2005
Messages
496
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.
 

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
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:

http://www.mrexcel.com/board2/viewtopic.php?p=868244#868244

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... :)
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
9,999
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.
 

Forum statistics

Threads
1,077,831
Messages
5,336,653
Members
399,094
Latest member
Learner2019

Some videos you may like

This Week's Hot Topics

Top