Solved: Add-In Help

[jmenche]

Howdy,

I am trying to write my first Add-In and need a little help.

The worksheet that I am trying to convert has a custom function that loops through named ranges on another sheet. However, when I save the worksheet as an add-in, the function returns an error.

Could somebody take a look?

:beerchug:

[mdmackillop]

Segmentme is not dimmed.
On which items is the function meant to operate. (Too lazy to decipher the code to determine this)

[Bob Phillips]

It is not a good idae to hard-code ranges in the UDF, especially if it will work across workbooks, as it will invariably be pointig at the wrong range.

Add 3 range arguiments to the UDF and pass the ranges to the UDF as parameters.

[jmenche]

Thanks fellas.

Here's my dilemma then. I need to use what is in the ranges as part of the UDF. The UDF will essentially look up what is part of an item description and replace it with a more meaningful word. How can I use the ranges then? I was thinking that I would need arrays and then use the arrays in the UDF for matching and not the ranges. Unfortunately, I am not that savvy.

[Bob Phillips]

I am suggesting changing it like so



Public Function SegmentFootcare(Item As Range, _
SubbrandsFootcare As Range, _
BrandsFootcare As Range, _
AttributesFootcare As Range) As String
Dim cel As Range
Dim start As Integer

SegmentMe = Item.Value

Subbrands:

For Each cel In SubbrandsFootcare
On Error Resume Next
If InStr(1, SegmentMe, cel.Text, vbTextCompare) = 1 Then
SegmentMe = Replace(SegmentMe, cel.Value, cel.Offset(0, 1).Value)
start = Len(cel.Offset(0, 1))
GoTo Attributes
End If
Next

Brands:

For Each cel In BrandsFootcare
On Error Resume Next
If InStr(1, SegmentMe, cel.Text, vbTextCompare) = 1 Then
SegmentMe = Replace(SegmentMe, cel.Value, cel.Offset(0, 1).Value)
start = Len(cel.Offset(0, 1))
GoTo Attributes
End If
Next

Attributes:

For Each cel In AttributesFootcare
On Error Resume Next
If InStr(start, SegmentMe, cel.Text, vbTextCompare) > 0 Then
SegmentMe = Application.WorksheetFunction.Trim(Replace(SegmentMe, _
cel.Value, cel.Offset(0, 1).Text))
End If
Next

End Function


and call like this

=SegmentFootcare(A1,SubbrandsFootcare,BrandsFootcare,AttributesFootcare)

[jmenche]

Thanks xld. I'll try it that way.

[Bob Phillips]

BTW, you don't need worksheetfunction Trim, VBA has a Trim function.

[jmenche]

The VBA Trim function only gets rid of leading and trailing spaces. The worksheet function gets rid of extra spaces in between too. Is there a VBA function for that?

[Bob Phillips]

Ah, that is differentr then. There are ways, but they are so convoluted, the function route is probably the best way.

[Zack Barresse]

Convoluted?

cel.Replace " ", ""

???

[mdmackillop]

A word of caution.
Trim and similar functions will not clear Char(160) blanks which I've come across in data exported from other applications into csv files and the like.

[Bob Phillips]

Convoluted?

cel.Replace " ", ""
???
Yes, to do it properly, it is



Dim s
s = "Bob Phillips"

MsgBox Replace(s, " ", "")

MsgBox Application.Trim(s)

[Zack Barresse]

Any reason you'd use that over..

Dim s
s = " a b c "

s = Replace(s, " ", "")

MsgBox s & Len(s)

[Bob Phillips]

I'm not getting the point you are making. I demonstrated that replacing space is no good, it gives the wrong result.

[jmenche]

Firefytr,

Xld's method would return BobPhillips (which is not the desired result).

Thanks everyone for the discussion!

[Zack Barresse]

I'm sorry Bob, how is that the wrong results? I get all spaces replaced. Am I missing something here?

Edit: Nevermind, saw the last post. Misunderstood the OP's wishes. :banghead:

[Bob Phillips]

Firefytr,

Xld's method would return BobPhillips (which is not the desired result).

Thanks everyone for the discussion!

That was not xld's solution!

[Bob Phillips]

I'm sorry Bob, how is that the wrong results? I get all spaces replaced. Am I missing something here?

Edit: Nevermind, saw the last post. Misunderstood the OP's wishes. :banghead:

So you can see why I said it was convolutred toi get the Op's desired result :)

[Zack Barresse]

Yes, I was misunderstanding. Need to read a little better next time! :yes