return values from function

detlion1643

Board Regular
Joined
Nov 25, 2009
Messages
164
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.

Code:
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.
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,067
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You can either pass a couple of additional variables byref, or you could define a new Type:
Code:
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

Board Regular
Joined
Nov 25, 2009
Messages
164
While that is a good suggestion, they only thing is that I am doing multiple checks against multiple brands. EX)

Code:
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

Code:
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.

Code:
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,067
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Why don't you use a lookup table instead of 50 hardcoded funcitons?
 

detlion1643

Board Regular
Joined
Nov 25, 2009
Messages
164
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,556
Messages
5,596,820
Members
414,104
Latest member
imamalidadashzada

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
Top