0 votes
in VBA by Expert (916 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 Expert (916 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 (3.2k 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 Expert (916 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 (3.2k 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 Expert (916 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
Ask any questions you have about VBA and Python and our community will help answer them. wellsr Q&A is the standalone question and answer platform for wellsr.com. If you have a question about one of our specific tutorials, please include a link back to the tutorial.

Getting Started
VBA Cheat Sheets (On Sale Now)

Looking for something else? Hire our team directly through ourVBA Help page, instead.

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