Excel Function in VBA hidden rows

[ajamr]

Hi everyone,

I created a function in excel vba, saved it in the personal.xlsb, and it works exactly as I want it, the only problem is that it only works when I "unhide" the header row. I tried searching the problem but I couldn't find anything.

Example Table

	Header 1	Header 2	Header 3
Subitem 1	1	6	0
Subitem 2	4	3	2
Subitem 3	1	5	0
Subitem 4	0	0	3

<tbody>
</tbody>

The function code below...

Public Function BOQTakeOff(subitem As Range, header As Range)

Dim txt As String
For Each cell In subitem
If cell.Value > 0 Then
txt = txt & " " & cell.Value & " x " & Cells(header.Row, cell.Column).Value & "," & vbCrLf
End If
Next cell
BOQTakeOff = txt

End Function

The end result will be something like

1 x Header 1, 6 x Header 2,

4 x Header 1, 3 x Header 2, 2 x Header 3,

1 x Header 1, 5 x Header 2,

3 x Header 3,

<tbody>
</tbody>

The code works, perfectly, but only when I have to unhide the "header" row in my original table...

Really appreciate your help...

 

[Fluff]

How are you using this function? From a macro, or from a sheet?
Using it in a sheet works for me

Excel 2013 32 bit
	D	E	F	G	H
5
7	Subitem 1	1	6	0	1 x Header 1, 6 x Header 2,
8	Subitem 2	4	3	2
9	Subitem 3	1	5	0
10	Subitem 4	0	0	3
Test1

Cell Formulas
Range	Formula
H7	=BOQTakeOff(E7:G7,E6:G6)

 

[ajamr]

I am reading this from a macro of the personal.xlsb file.
If I use it in the same workbook, it works without a flaw, however; when reading it as a macro from the personal.xlsb file, i have to un-hide the source table rows in order to make it work.

 

[Fluff]

a) What macro calls the function?
b) How is the header set in that macro?

 

[lhartono]

Your code working,
but I have to lock the header address, so the result are exactly like yours.

=PERSONAL.XLSB!BOQTakeOff(E2:G2,$E$1:$G$1)

 

[ajamr]

I did lock the header address, it worked.
Thanks.