VBA in Excel: How to replace a command with a variable?

Question

I'm building a function that retrieves table elements from a web-page. That works quite fine so far.

To make the function more versatile, I would like to be able to call it using parameters telling it what to look for.

My current code writes the values found into the cell using:

Sub RetrieveWebPage Parameter

... some code here

   rng.Value = cl.innerHTML

... some code here
End Sub

How can I replace the "innerHTML" with a variable parameter so that it retrieves not always the innerHTML element, but possibly the outerHTML, or the Title, etc.?

I tried this

rng.Value = cl.Parameter

but just get the error that the Object does not support this property or method. Is there a way to do that?

Thanks.

mongoose36 · Accepted Answer · 2016-03-17 02:41:04Z

1

All due respect to @Doug, but I prefer to use Boolean variables for this sort of thing. Primarily because a Boolean variable can be only False or True (defaults to false when declared). The advantage to this is you don't have to worry about accidentally passing a misspelled String as a parameter which can happen from time to time when typing lots of code.

Additionally utilizing a Boolean variable makes for less code (albeit not by much) E.g.

Sub RetrieveWebPage(Inner as Boolean)
'... some code here
If Inner = True then
    rng.Value = cl.innerHTML
Else
    rng.Value = cl.outerHTML
End if
'... some code here
End Sub

A situation where a string parameter is more useful is when there are more than 2 outcomes that need to each be handled separately. Just my two cents! :)

answered Mar 17, 2016 at 2:41

mongoose36

7994 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Doug Glancy Over a year ago

Thanks for the respect :-). I agree with your general point. However the OP does refer to a possible third choice, i.e., "Title." I will amend my answer to make that clearer.

Matth Over a year ago

Thanks. I understand the misspelled String, though this limits the possible values to two only, no? As I wrote, I might have more than just two values that I might want to pass on. And for simplicity, I did go with Tim Williams solution for the moment.

Doug Glancy · Accepted Answer · 2016-03-17 03:44:18Z

1

Something like:

Sub RetrieveWebPage (Parameter as String)
'... some code here
Select Case Parameter
    Case "Inner"
        rng.Value = cl.innerHTML
    Case "Outer"
        rng.Value = cl.outerHTML
    Case "Title"
        rng.Value = cl.Title
    Case Else
        MsgBox "Call the programmer and suggest adding error-handling to this routine or using a class"
End Select
'... some code here
End Sub

edited Mar 17, 2016 at 3:44

answered Mar 17, 2016 at 2:28

Doug Glancy

27.5k6 gold badges72 silver badges118 bronze badges

1 Comment

Matth Over a year ago

Thank you very much. I was thinking of something like this, though I went for now with Tim Williams solution as this is what I was exactly looking for. I believe though, your version is probably the more stable and "clean" version to use

Tim Williams · Accepted Answer · 2016-03-17 03:46:21Z

0

Dim P As String
P = "innerHTML"

rng.Value = CallByName(cl, P, vbGet)

answered Mar 17, 2016 at 3:46

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

1 Comment

Matth Over a year ago

Awesome, that was exactly what I was looking for :) Thanks so much

Collectives™ on Stack Overflow

VBA in Excel: How to replace a command with a variable?

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related