Simple vba FUNCTION that uses NAMED RANGES

**whburling** · 11-30-2022, 05:19 PM

LOOKING AT THE ATTACHED WORKBOOK, please note that I created a 3x3 array on the only worksheet and defined named ranges as can be seen in the NameManager.

Then go to the code.

You will see the beginnings of a simple UDF and a subroutine that i intend to use to test the function.

The Subroutine must fetch named ranges from the workbook and pass it on to my UDF as the first parameter.

Clearly I do not understand how to successfully pass the named array to the subroutine. After I understand that, I can try and make the subroutine attempt to use that named array as a parameter for my UDF, the beginnings you can see.

Please see the attached workbook to see the code

**TMS** · 11-30-2022, 06:57 PM

UDF

Please Login or Register  to view this content.

PHP Code:


Door01    =ExtractDoorDimensions(G5, 1) 
80.00    =ExtractDoorDimensions(H5:I5, 1) 
24.00    =ExtractDoorDimensions(H5:I5, 2) 
     
Door01    =ExtractDoorDimensions($G$5:$I$5, ROWS($A$1:$A1)) 
80.00    =ExtractDoorDimensions($G$5:$I$5, ROWS($A$1:$A2)) 
24.00    =ExtractDoorDimensions($G$5:$I$5, ROWS($A$1:$A3)) 
     
80.00    =ExtractDoorDimensions(Door01, 1) 
24.00    =ExtractDoorDimensions(Door01, 2) 
     
80.00    =ExtractDoorDimensions(Door01, DoorHeight_Pointer) 
24.00    =ExtractDoorDimensions(Door01, DoorWidth_Pointer)

**MarvinP** · 11-30-2022, 07:08 PM

Hi whburling,

See the attached with a better approach that will answer your question (maybe).
VBA Ed for whburling.xlsm

**whburling** · 11-30-2022, 07:28 PM

Hello Marvin

i downloaded your vba version (new subroutine) and strove to run the debug. I keep getting the message that the macros in this project are disabled.
I went to excel -> developer -> and then to 'code' where i enabled all radio buttons i could that enabled the macro to run. Ie: the radio button beginning
with "enable vba macros..."
"enable excel 4.0 macros..."
and "trust access to vba macros..."

but i still am not permitted to run your workbook. would love to do that.

**whburling** · 11-30-2022, 07:38 PM

Trevor,
I love how you think. PHP....how interesting.

regarding your comment thinking that my pointers need to be between 2 and 3, I can understand how you might think that believing that I am passing the entire array to my udf
But if you look at the name manager and look up doo1 you will see that it has only two columns, height and width. each of teh doorxx (ie: door02, door24) only have two columns.
Hence my pointers are 1 and 2.

to recap, i am not passing the entire 3x3 array.
I am passing only one 1x2 array named doorxx

does that help?

**TMS** · 11-30-2022, 07:49 PM

No, I said:

The Pointers must be 1 and 2 respectively

In your example, if you want to use the Named Ranges for Door01, Door02, Door03, Door04, etc., which are two columns wide, the pointers can only have a value of 1 or 2. You had them set as 2 or 3. That COULD be ok with some of my earlier examples which are not using Named Ranges and, therefore, not constrained by the size of the range.

Hence my pointers are 1 and 2.

No, in the example you uploaded, the pointers were 2 and 3.

PHP Code:


DoorHeight_Pointer    DoorWidth_Pointer 
          2                    3

to recap, i am not passing the entire 3x3 array.
I am passing only one 1x2 array named doorxx

l know that, hence you can only index the first or second element in that array.

**whburling** · 11-30-2022, 07:51 PM

while waiting to figure out how to enable macros on the workbook you sent me,
I looked up the definition of your new named variable, "DoorTopLeft"
i looked in the name manager. it was defined as a single cell C16, with the addition of a dollar sign which i believe means the absolute value?

so i am mystified. i am not sending an array, but only a single address pointing to one cell, c16 with absolute addressing.

the approach i am taking is to use "door01" which is a two dimensional array of door width and door height. see the name manager.
if i reference that single array (and not the whole array), i figure i am passing to my udf the data i really want to pass (height and width for a specific door)
from which i will be extracting just one of the two pieces of data in Doorxx.

later i will be taking that two dimensional array and compute the amount of wiring i will need to get around a door. The actual goal of this project is to
figure out the wiring, outlets etc for a given house (my son's). I will be opassing other parameters that are necessary to make my desired calculation.

But...but...since i am trying to learn....i kept this simple.

**TMS** · 11-30-2022, 07:52 PM

Did you look at the file I uploaded?

**TMS** · 11-30-2022, 07:56 PM

In Windows File Manager, navigate to the folder where you download files. Right click on the .xlsm file and select Properties. Near the bottom, you will see a check box that says Unblock. Click in that and ok out. You should now be able to open the file and enable macros.

**whburling** · 11-30-2022, 08:10 PM

boy do i owe you an apology. sorry. don't know where my head was.
soory for wasting your time. you are right. i was in left field in the wrong ball park.

**TMS** · 11-30-2022, 08:12 PM

No worries.

**MarvinP** · 11-30-2022, 08:12 PM

Hi whburling,
See if this attached makes more sense to you.
I think wanting to program with Arrays and a Userform are more than you really need.
VBA Ed Electrical Calculations from Marv.xlsm
You need to open the attached and then save it to your hard drive and re-open it from there for the VBA to work. That way downloaded Excel with VBA can't unknowingly screw thing up.

**whburling** · 11-30-2022, 08:22 PM

Damn you are good. I exited out of the workbook that i could not enable macros....did exactly as you suggested....and the macros were enabled. Thank you so much.

now...i went back to your spreadsheet i downloaded....and saw all the variations that i could use. CLEARLY YOU UNDERSTAND WHERE I AM COMING FROM.
Now....i am trying to get bacak to my code and use the exact expressions in the subroutine and in my functions...and see if it works.

Did you note i used an input box to pass the named array. is there a better way? do i just put in it exactly what you have? (ie: Door01 without double quotesd).
i am going to experiment with your workbook that you returned to me. and try things out.

but....i am married (at 77) and must give my wife some attention.....so i must put this fantastic stuff temporarily down....and engage it tomorrow with relish.

Thank you again for such awesome ideas and immeasurable patience.

bil

**whburling** · 12-01-2022, 12:36 PM

Hi Trevor!!!

I am looking at your workbook you sent back to me. Damn....you really get it. let me look a bit....try modifying my code to emulate yours....
and then.....i am ready to RUN!!!! MOVE!!!!...HOW EXCITING.... i am jumping up and down.

Give me a few minutes to read and play with your fixes....

Thank you very very much
bil

**whburling** · 12-01-2022, 12:37 PM

finally i did....and wow....yes....you understand. Thank you....wish i could give you another reputation.

**whburling** · 12-01-2022, 02:30 PM

Trevor,
I finally got my code working. it took me awhile...as i kept making small mistakes.

I am compiling a list of questions tht surfaced. i first want to experiment and see if i can find out for myself.... but if i fail, i will ask for more help with this UDF
very grateful to both you and Marvin. You guys are exceptional.

**whburling** · 12-01-2022, 03:28 PM

Ok,
trevor,

attached is a revised file. I got rid of all intermediate values. very clean. it works. thanks to you.
NOW
i want to get my test file working. I want to do that to make sure I can troubleshoot with the debugger.
I can step through it all the way to the line which attempts to obtain the value of DoorWidth_Pointer.
It has an error that point.

Several questions here:
Doorxx is in double quotes. is that because the name is actually a string that happens to point to a range. Since I am asking for a named range, the workbook.names method must match a string and when found, it offers a range of data?

secondly, i noticed that while the debugger stepped through the retrieval of Doorxx range, the locals viewer did not show its values. that worried me.

**TMS** · 12-01-2022, 07:18 PM

Thank you....wish i could give you another reputation.

Me too, but not to worry, it's the thought that counts

Whilst I understand what you are trying to do, I really do NOT understand why you want to do it. There are countless ways of using formulae, with or without Named Ranges, to search for and return the data you want. You really do NOT need VBA for this.

Here is the revised code.

Please Login or Register  to view this content.

Simple vba FUNCTION that uses NAMED RANGES

LinkBack

Thread Tools

Rate This Thread

Display

Simple vba FUNCTION that uses NAMED RANGES

Re: Simple vba FUNCTION that uses NAMED RANGES

Re: Simple vba FUNCTION that uses NAMED RANGES

Re: Simple vba FUNCTION that uses NAMED RANGES

Re: Simple vba FUNCTION that uses NAMED RANGES

Re: Simple vba FUNCTION that uses NAMED RANGES

Re: Simple vba FUNCTION that uses NAMED RANGES

Re: Simple vba FUNCTION that uses NAMED RANGES

Re: Simple vba FUNCTION that uses NAMED RANGES

Re: Simple vba FUNCTION that uses NAMED RANGES

Re: Simple vba FUNCTION that uses NAMED RANGES

Re: Simple vba FUNCTION that uses NAMED RANGES

Re: Simple vba FUNCTION that uses NAMED RANGES

Re: Simple vba FUNCTION that uses NAMED RANGES

Re: Simple vba FUNCTION that uses NAMED RANGES

Re: Simple vba FUNCTION that uses NAMED RANGES

Re: Simple vba FUNCTION that uses NAMED RANGES

Re: Simple vba FUNCTION that uses NAMED RANGES

Thread Information

Users Browsing this Thread

Similar Threads

How to generate a simple quote system using excel and 'named ranges' for hotel booking

IF function to vlookup in named ranges

SUM using ADDRESS function and Named Ranges

[SOLVED] If function Multiple logic tests each with its own outcome. How do I write it.

Excel 2007 : Named Ranges and Max Function

[SOLVED] help me write function?-pretty simple,

[SOLVED] Named ranges-How would I write the code for that?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions