Hi,
I was trying to figure out how to use sumproduct in VBA in the same manner as in excel in lieu of CountIfs and/or Creating Arrays given criteria
Someone else noticed that VBA isn't converting Boolean Array into Integers automatically like Worksheet does. I looped through to keep it moving, but was wondering if anyone has ideas on converting
ex. Worksheet: Sumproduct((Array <> variable)*1)
So I'd hae to convert (Array <> variable) from Boolean to 1, 0)
Shows -1 in the immediate window. So if you use CInt(Put Boolean array here) I would think it would convert.
Please Login or Register to view this content.
Thanks,
Solus
Please remember the following:
1. Use [code] code tags [/code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.Highlight the code in your post and press the # button in the toolbar.2. Show appreciation to those who have helped you by clickingbelow their posts.
3. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.
"Slow is smooth, smooth is fast."
This kind of works but I'm not sure how you would use it.
Please Login or Register to view this content.
If posting code please use code tags, see here.
I would create UDF before think that way.
OOps, CountIfs...Please Login or Register to view this content.
Please Login or Register to view this content.
Last edited by jindon; 11-05-2013 at 05:35 PM.
Only slightly different from Norie's suggestion.Please Login or Register to view this content.
Thanks guys,
1. Tried code below, get Type Mismatch error still
2. Trying to see if I can plug Norie's version in as the overall goal is to see if it's possible to convert from Boolean to Integer without loopingPlease Login or Register to view this content.
3. I may be wrong, but I'm trying to build things without touching Excel as much as possible, trying to avoid UDF, etc.
Why are you trying to do everything in VBA and not take advantage of Excel's built-in functionality?
For example, if you wanted to do a simple, straight down the line, no quirks sum, why not use Excel's SUM worksheet function.
If you really need it in code then use WorksheetFunction or Evaluate.
You should be aware that Evaluate method only accepts the substring less than 256 characters.Originally Posted by cmore
Cool, thanks, forgot about that limit. What's the use of 'Join' in that syntax though?
Agreed not doing everything in code, just mainly the things that are data hogs, like array formulas and trimming down named ranges to only the essentials, and then for formulas, will put formula in ranges and then .value them out.
'data hogs'?
the formulas I was initially using made it really difficult continue to build the model effectively calcs ran too long or required more and more real estate. So I'm trying to manage using both the worksheet and VBA. For instance most of the tool right now is just data manipulation no real calcs
Would you mind explaining the arrInt formula a little? I don't follow the syntax in { }This kind of works but I'm not sure how you would use it.
Please Login or Register to view this content.
Would you mind explaining the arrInt formula a little? I don't follow the syntax and why in the world does the 1-Based array not work?This kind of works but I'm not sure how you would use it.
Please Login or Register to view this content.
Please Login or Register to view this content.
1. The string argument to the Evaluate function is evaluated by Excel's formula evaluator, not VBA, and
2. Your arrBool array is 2D, and Join only accepts a 1D array. 1-based, 0-based, or anything else-based doesn't matter.
Last edited by shg; 11-05-2013 at 07:39 PM.
Entia non sunt multiplicanda sine necessitate
Get the Join part, just cannot get everything else to speak to me, working........
Thanks shg! didn't realize the 2D requirement. Stupid question, Loading range from sheet without looping automatically creates 2D? I thought I could get a nice 1D that way. Used Transpose to get a 1D, but a little baffled
Holy beez wax, this is the most exacting piece of code I've worked with so far. I know I'm obviously missing some things:
Please Login or Register to view this content.
Last edited by cmore; 11-05-2013 at 09:04 PM.
I know you don't want to hear it, but you're wasting your time if the goal is code efficiency. Evaluate is slow.Please Login or Register to view this content.
No trust me, I hear you, I'm still looping. Just wanted to see if I can get this to work and see if I could possibly get Sumproduct or other things that required boolean to int conversion.
Just a note though, I came across something that said Worksheet.Evaluate and [] are relatively faster, not sure if you had any thoughts there?
The brackets are just a shortcut to the evaluate function.
Worksheet.Evaluate controls the context (the worksheet for unqualified range references) in which the formula is evaluated; I doubt there's any difference between that and Application.Evaluate in execution time.
cmore
What is it you are trying to get to work?
Sorry took so long to respond.
So things are workingish, I'm just trying to find easier ways to create some of the sub-arrays needed and easier way for counts. For instance. There are a few formulas on the Summary Tab I haven't automated yet, and then there's info. that populates on the Data tab, based on the Summary Info. that's honestly kind of sluggish right now and finally, I am going to need to populate some chart data.
In Excel going from boolean to to integers makes things a lot more flexible, please let me know if I'm off base
Last edited by cmore; 11-10-2013 at 01:48 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks