return values from function

[detlion1643]

Basically, I am passing into this function 2 strings. If it meets both criteria, the function will return one integer and one string which are needed for multiple other functions. I am just looking for a way to organize my code and this function is just that way.

Function brand_category_search_acer(strbrand, strcat)
If strcat = "notebooks" And strbrand = "acer" Then
    pgcnt = 2
    ccurl = "http://www.costcentral.com/product-list/Notebooks/26/ACER/Z00196/"
End If
End Function

I am looking to return pgcnt = *, and ccurl = ** so that I can use them throughout the rest of the sub.

 

[RoryA]

You can either pass a couple of additional variables byref, or you could define a new Type:

Public Type WebInfo
    pageCount As Long
    siteaddr As String
End Type
Function brand_category_search_acer(strbrand, strcat) As WebInfo
If strcat = "notebooks" And strbrand = "acer" Then
    brand_category_search_acer.pageCount = 2
    brand_category_search_acer.siteaddr = "http://www.costcentral.com/product-list/Notebooks/26/ACER/Z00196/"
End If
End Function
Sub test()
    Dim WI As WebInfo
    WI = brand_category_search_acer("acer", "notebooks")
    MsgBox WI.pageCount & " : " & WI.siteaddr
End Sub

 

[detlion1643]

While that is a good suggestion, they only thing is that I am doing multiple checks against multiple brands. EX)

Function brand_category_search_HP(strbrand, strcat)
    If strcat = "input devices" And strbrand = "hp" Then
    pgcnt = 2
    ccurl = http://www.costcentral.com/product-list/Input_Devices/20/HP/Z00023/
End Function

Function brand_category_search_acer(strbrand, strcat)
If strcat = "notebooks" And strbrand = "acer" Then
    pgcnt = 2
    ccurl = "http://www.costcentral.com/product-list/Notebooks/26/ACER/Z00196/"
End If
End Function

This is the main that will check for the brands to get pgcnt and ccurl.

Sub main()
strbrand = InputBox("Please enter brand!")
strcat = InputBox("Please enter category!")
brand_category_search_HP strbrand, strcat
brand_category_search_acer strbrand, strcat

I will eventually end up with about 50 or so functions (or brands to check through), since I think categorizing by brands is the simplest and most organized way. Hope this can clear up some issues.

 

[RoryA]

Why don't you use a lookup table instead of 50 hardcoded funcitons?

 

[detlion1643]

I'll be honest, i've never used anything in excel besides simple formulas and vba. I have everything written in vba that scours 2 different websites for data and urls, and then extracts all the data that pertains to the project.

Since this is all done in vba so far, I thought it would be good to stay with vba.

If a lookup is more efficient, then I will start learning something new.