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

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Header 1[/TD]
[TD]Header 2[/TD]
[TD]Header 3[/TD]
[/TR]
[TR]
[TD]Subitem 1[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Subitem 2[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Subitem 3[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Subitem 4[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]


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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]1 x Header 1,
6 x Header 2,
[/TD]
[/TR]
[TR]
[TD]4 x Header 1,
3 x Header 2,
2 x Header 3,[/TD]
[/TR]
[TR]
[TD]1 x Header 1,
5 x Header 2,
[/TD]
[/TR]
[TR]
[TD]3 x Header 3,[/TD]
[/TR]
</tbody>[/TABLE]

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.