0 votes
in VBA by Skilled (673 points)
edited by

Hello everyone

I have a UDF function that has such lines

    If Not IsMissing(page) Then
        If Len(sParameters) = 0 Then
            sParameters = "page=" & page
            sParameters = sParameters & "&" & "page=" & page
        End If
    End If
    If Not IsMissing(zoom) Then
        If Len(sParameters) = 0 Then
            sParameters = "zoom=" & zoom
            sParameters = sParameters & "&" & "zoom=" & zoom
        End If
    End If

Such lines are about 6 blocks, I have put two for illustration

How can this be simplified with another function ..? so as to simplify the 6 blocks and reduce the number of lines in the original UDF?


I have tried such approach

Dim e
For Each e In Array("page", "zoom", "pagemode", "scrollbar", "toolbar", "statusbar", "messages", "navpanes")
    If Not IsMissing(e) Then
        If Len(sParameters) = 0 Then
            sParameters = e & "=" & e
            sParameters = sParameters & "&" & e & "=" & e
        End If
    End If
Next e

There was no problem at the execution of the UDF

But it doesn't react as the original one

** The UDF is on this link


by Skilled (673 points)

I have followed the code and I have found that the items in the array was treated as strings ..


So how this can be fixed to convert the strings to the variables used in the parameters of the UDF?

1 Answer

+1 vote
by Super Expert (2.7k points)
selected by
Best answer

To get your function to work, your best bet is to declare two arrays: one with the variable names stored as strings and one with the actual variable values. A short function like this should demonstrate what I mean:

Function Demo(Optional page As Variant, Optional zoom As Variant, Optional pagemode As Variant, Optional ScrollBar As Variant, Optional Toolbar As Variant, Optional StatusBar As Variant, Optional messages As Variant, Optional navpanes As Variant)
Dim e, i
Dim arr() As Variant
arr = Array("page", "zoom", "pagemode", "scrollbar", "toolbar", "statusbar", "messages", "navpanes")
For Each e In Array(page, zoom, pagemode, ScrollBar, Toolbar, StatusBar, messages, navpanes)
    If Not IsMissing(e) Then
        If Len(sparameters) = 0 Then
            sparameters = arr(i) & "=" & e
            sparameters = sparameters & "&" & arr(i) & "=" & e
        End If
        Debug.Print sparameters
    End If
    i = i + 1
Next e

End Function

The first array, arr, stores the strings you want to include before the "=" sign in your search parameter. The Array function in the For Each statement contains the actual variables. As long as the two arrays have identical dimensions and are typed in the same order, this successfully condenses your 8 blocks of code into one loop.

Recall, the VBA IsMissing function only works if your arguments are variants. 

by Skilled (673 points)
That's amazing Mr. Ryan. Thanks a lot for great help.

I have learned something new ...

But as a question: is there a way in VBA to treat the string as variable .. ?? In that case this will save dealing with two different arrays
Or is there  a way to extract the variable name itself and make it a string ..?
by Super Expert (2.7k points)

There is a way to do this, but it involves creating a custom class. The solution is complex enough that it'd be easier to create two arrays. If you want to go down the custom class route, that link should help! :-)

by Skilled (673 points)
Thank you very much for awesome help.

I will not use the class module .. but I would like to have a look to learn something new

I have found this thread that is related to what I mean



** I have written the post before I have a look at your link .. that is funny!! It is the same link

Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Getting Started
VBA Cheat Sheets (On Sale Now)

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and answers. All you have to do is post and you could get rewarded, like these members:

Hightree $10 Amazon Gift Card
Thales1 $10 Amazon Gift Card
runfunke $10 Amazon Gift Card
coolag $10 Amazon Gift Card
Siew Hun $10 Amazon Gift Card

So, why don't you join us? It really is an encouraging way to motivate members in our VBA and Python community.


For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.