Public function string problem

JamesWood · #1 05-27-2021, 04:07 AM

Hi guys

I have this public string code:

Public Function docNameFieldPreview() As String
docNameFieldPreview = ActiveDocument.MailMerge.DataSource.DataFields("Name").Value
End Function

This works, but if I replace the name section with another string I have, it says "The requested member of the collection does not exist."

Public Function docNameFieldPreview() As String
docNameFieldPreview = ActiveDocument.MailMerge.DataSource.DataFields(docNameField).Value
End Function

Any thoughts? docNameField has been dimmed elsewhere, and does work in other areas of the code that use it, so I'm not sure why it won't work in this particular string.

Thanks a lot

James

JamesWood · #2 05-27-2021, 05:56 AM

Actually I got this to work by adding an error handler to it! For some reason it works now.

Guessed · #3 05-27-2021, 03:47 PM

Just because docNameField is a variable within another sub doesn't make it available to other subs or functions. If you want the same variable to be used across different macros then you have two choices.
1. Use a function which takes in in the variable

Code:

'Code calling the function
str = docNameFieldPreview(docNameField)

'Note I've named the variable x here to avoid confusion. You can call it docNameField if you like but it is actually a different variable in this example.
Public Function docNameFieldPreview(x as String) As String
  docNameFieldPreview = ActiveDocument.MailMerge.DataSource.DataFields(x).Value
End Function

2. Declare it at the top of the module (outside of any sub/function) as either a Public or Private variable so the same name can be used anywhere in the module (if Private) or the template (if Public) eg.

Code:

Public docNameField as String  'this is empty until a macro gives it a value
Sub DoSomething()
  Dim str as string
  docNameField = "Name"
  str = docNameFieldPreview
  MsgBox str
End Sub
Public Function docNameFieldPreview() As String
  docNameFieldPreview = ActiveDocument.MailMerge.DataSource.DataFields(docNameField).Value
End Function

JamesWood · #4 05-27-2021, 11:55 PM

Quote:

Originally Posted by Guessed

Just because docNameField is a variable within another sub doesn't make it available to other subs or functions. If you want the same variable to be used across different macros then you have two choices.

Thanks so much for this! I guess I'm happy for now that it's working just by using the error handler, but I'll take a look at your code as I can probably make it a lot tidier now. Thanks!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Mistake in the help file for String "Split Function"?	John 4	Word VBA	6	11-19-2020 06:02 AM
Problem with function	PRA007	Word VBA	2	01-13-2016 01:11 AM
How to get the Right function to return a string that has UPPERCASE letters only	adj	Word VBA	9	08-30-2014 09:21 PM
How to reserve an array in a public function?	tinfanide	Word VBA	2	02-27-2012 06:51 AM
Macro or Function to know wether a string is included in a text	Eduardo	Word VBA	5	06-15-2009 01:55 AM