Procedure Too Large - Seperate functions

[Helen Pippard]

Hello,

I have a Function in VBA which is "too large" to run

So what I have done is taken a huge chunk of the procedure and created a seperate function which I need to CALL back into my main function

The original syntex read as:

m_objDoc.Bookmarks("COUNT_DPED_COLECTOMY").Select
m_objWord.Selection.Text = Switch(IsNull(DLookup("[COUNT]", "COUNT_DPED_COLECTOMY")) = True, "", True, Round(DLookup("[COUNT]", "COUNT_DPED_COLECTOMY")))

This took a value from a table and exported the data into my bookmark in MS.word, but as I am repeating this process over 100 times the procedure became too large

I created a new function called:
SetVariablesForSwitch(StrVariable As String)

In this function I declared my variables and did all the switch statements

Now I do not know how to CALL the function correctly in my main function. I currently have it set as

m_objDoc.Bookmarks("COUNT_DPED_COLECTOMY").Select
m_objWord.Selection.Text = StrVariable = SetVariablesForSwitch("StrCOUNT_DPED_COLECTOMY")

All this returns is a TRUE in my word document.

Question

I need to know what correct VBA to use in order to call my sub funtion correctly and so it can recognise that what I am calling is a variable and I want it to export the value into my bookmark in MS.word

Any suggestions?

Thanks for any help?

Helen

 

[revans]

I'd look at this line

m_objWord.Selection.Text = StrVariable = SetVariablesForSwitch("StrCOUNT_DPED_COLECTOMY")

What you're doing is using two = operators which logically is setting your .Text property to whether the StrVariable = SetVariblesForSwitch... is True or Not.

Try this

m_objWord.Selection.Text = SetVariablesForSwitch("StrCOUNT_DPED_COLECTOMY")

Also shouldn't your strVariable be used somehow, perhaps like
m_objWord.Selection.Text = SetVariablesForSwitch(StrVariable). Or not, not sure what the strvariable is in there for.

hth,

Rich

 

[Helen Pippard]

Hello,

Thank you for your help!

Yesturday afternoon I had a break through, with a little bit of help from a colleague of mine.

In my sub function I have the following code for each of my options

Select Case StrVariable
Case "StrCOUNT_DPED_COLECTOMY"
SetVariablesForSwitchX = Switch(IsNull(DLookup("[COUNT]", "COUNT_DPED_COLECTOMY")) = True, "", True, Round(DLookup("[COUNT]", "COUNT_DPED_COLECTOMY")))
Case Else

End Select


In the Main function I call the sub function by:

m_objDoc.Bookmarks("COUNT_DPED_COLECTOMY").Select
m_objWord.Selection.Text = SetVariablesForSwitchX("StrCOUNT_DPED_COLECTOMY")

As you kindly suggested

My Main Function and sub function now work perfectly

Thanks for your input

Helen