Using UDF from Name Manager

BDrew

Board Regular
Joined
Jun 8, 2008
Messages
79
My formula, including an UDF to convert a Column number to a Column Name works when placed on the sheet but returns #VALUE! error when named ("Test")in the Name Manager

in cell AO205, Test=INDIRECT(ConvertToLetter(COLUMN(Build!AO204))&Build!$AH205)

=Test returns #VALUE!
=INDIRECT(ConvertToLetter(COLUMN(Build!AO204))&Build!$AH205) returns the text value in cell AO154 because Build!$AH205 holds the value 154


Owing to the size of the application, the formulas are stored in the name manager and changed to values only using VBA which is limited to 255 characters in any formula applied.

I know this is being caused by the Column Function being unable to function from the Name Manager but I am looking for a work around.

Any ideas welcome?

Brendan

Code follows

UDF
Public Function ConvertToLetter(iCol As Integer) As String
Dim iAlpha As Integer
Dim iRemainder As Integer
iAlpha = Int(iCol / 27)
iRemainder = iCol - (iAlpha * 26)
If iAlpha > 0 Then
ConvertToLetter = Chr(iAlpha + 64)
End If
If iRemainder > 0 Then
ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
End If
End Function
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
This works for me:

=INDIRECT("'Build'!"&ConvertToLetter(COLUMN(Build!AO204))&Build!AH205)

Robert
 
Upvote 0
Trebor,

Thanks. this works as a sheet formula but if named Test then "=Test" returns #VALUE!

Brendan
 
Upvote 0
Ah, now I see what the issue is. I believe you are correct in that a named range cannot contain a UDF.

That said, given that...

=ADDRESS(204,41,4)

...returns AO204 and this formula (based from here))...

=LEFT(ADDRESS(204,41,4),MIN(IF(ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},ADDRESS(204,41,4))),FIND({1,2,3,4,5,6,7,8,9,0},ADDRESS(204,41,4))))-1)

...will always return the column letter from the ADDRESS function passed to it, this Named Range formula works (for me anyways):

=INDIRECT("'Build'!" & LEFT(ADDRESS(204,41,4),MIN(IF(ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},ADDRESS(204,41,4))),FIND({1,2,3,4,5,6,7,8,9,0},ADDRESS(204,41,4))))-1) & Build!$AH$205)

Clunky I know.

Robert
 
Upvote 0
Ah, now I see what the issue is. I believe you are correct in that a named range cannot contain a UDF.

That said, given that...

=ADDRESS(204,41,4)

...returns AO204 and this formula (based from here))...

=LEFT(ADDRESS(204,41,4),MIN(IF(ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},ADDRESS(204,41,4))),FIND({1,2,3,4,5,6,7,8,9,0},ADDRESS(204,41,4))))-1)

...will always return the column letter from the ADDRESS function passed to it, this Named Range formula works (for me anyways):

=INDIRECT("'Build'!" & LEFT(ADDRESS(204,41,4),MIN(IF(ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},ADDRESS(204,41,4))),FIND({1,2,3,4,5,6,7,8,9,0},ADDRESS(204,41,4))))-1) & Build!$AH$205)

Clunky I know.

Robert

Thank you, Robert.

I will give that a go.

Yes, Excellent, that has worked for me. Clunky, as you say, but named to a handle like "Test" it does not matter at all.

Thank you again

Brendan
 
Upvote 0
You're welcome :)

If the column is always AO you could simply use this:

=INDIRECT("'Build'!$AO$" & Build!$AH$205)

Just seems odd to me that you're passing in an address to get the column letter (I obviously don't know your process in its entirety).

Regards,

Robert
 
Last edited:
Upvote 0
If I remember correctly, the COLUMN function (and the ROW function) returns a one-element array. However, your UDF expects an integer, hence the #VALUE! error. You can coerce the result of the COLUMN function into a single number by passing it to the MIN function. So the following reference for your name should work...

=INDIRECT(ConvertToLetter(MIN(COLUMN(Build!AO204)))&Build!$AH205)

However, here's another way without the UDF...

=ADDRESS(Build!$AH205,COLUMN(Build!AO204),4)

Hope this helps!
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

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 MrExcel.com.
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 "mrexcel.com".
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
Back
Top