Coded a Sub to dump a variant array to a sheet and optionally apply some
formatting to the sheet range. The code in the Sub is not the problem, but there is a problem with the arguments in the Sub:

Sub ArrayToSheet(vArray As Variant, _
Optional oSheet As Worksheet, _
Optional oFirstCell As Range, _
Optional bFreezeScreen As Boolean, _
Optional bFormatRange As Boolean, _
Optional bUnderLineHeaders As Boolean, _
Optional strHeadersCSV As String, _
Optional bUnderLineHeaders As Boolean, _
Optional lBottomBorderFirstColumnInRange As Long = -1, _
Optional lBottomBorderLastColumnInRange As Long = -1, _
Optional lMarkingRowsColumnInRange As Long = -1, _
Optional lBorderThickness As Long = xlThin, _
Optional vBottomArray As Variant)

It gives me the error message: Duplicate declaration in current scope, highlighting this bit:

Sub ArrayToSheet(vArray As Variant, _

But this is not the case at all. I even get the same message if I put this Sub in a completely new workbook with no other code at all. Then, strangely, if I take some arguments off that Sub then eventually the error message will disappear.
I rebuild the workbook in case there was some corruption, but didn't help.
Any idea what could be the problem here?
This is Office 2007 on Windows 7.

RBS

Bart,
It appears that you're passing data and settings. Normally I pass the
settings in an array that the routine parses via enums to the relative
positions...

Sub ArrayToSheet(vData, vSettings)

While it shouldn't matter how many args the routine requires, it may be
that vArray has larger scope outside the routine.

Of course, I don't do this as you are here. Instead, I 'dump' the data
into a target range then run a separate routing to do sheet setup. This
is a standard approach for app sheets that the user accesses. Report
sheets are usually pre-formatted templates (depending on layout), but
if not using a template the sheet setup happens independantly as I
described.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

wrote in message


Sub ArrayToSheet(vArray As Variant, _
Optional oSheet As Worksheet, _
Optional oFirstCell As Range, _
Optional bFreezeScreen As Boolean, _
Optional bFormatRange As Boolean, _
Optional bUnderLineHeaders As Boolean, _ <<< !
Optional strHeadersCSV As String, _
Optional bUnderLineHeaders As Boolean, _ <<< !
Optional lBottomBorderFirstColumnInRange As Long = -1, _
Optional lBottomBorderLastColumnInRange As Long = -1, _
Optional lMarkingRowsColumnInRange As Long = -1, _
Optional lBorderThickness As Long = xlThin, _
Optional vBottomArray As Variant)

It gives me the error message: Duplicate declaration in current scope,
highlighting this bit:


Time for new glasses <vbg

Regards,
Peter T

wrote in message


Sub ArrayToSheet(vArray As Variant, _
Optional oSheet As Worksheet, _
Optional oFirstCell As Range, _
Optional bFreezeScreen As Boolean, _
Optional bFormatRange As Boolean, _
Optional bUnderLineHeaders As Boolean, _ <<< !
Optional strHeadersCSV As String, _
Optional bUnderLineHeaders As Boolean, _ <<< !
Optional lBottomBorderFirstColumnInRange As Long =
-1, _
Optional lBottomBorderLastColumnInRange As Long =
-1, _
Optional lMarkingRowsColumnInRange As Long = -1, _
Optional lBorderThickness As Long = xlThin, _
Optional vBottomArray As Variant)

It gives me the error message: Duplicate declaration in current
scope, highlighting this bit:


Time for new glasses <vbg

Regards,
Peter T

Yeah.., right! Or possibly just get more sleep!<vbg

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Peter,

Can't see what I am not seeing there.

RBS

Hi Peter,

Can't see what I am not seeing there.

RBS

4th and 6th args are indentical...

Optional bUnderLineHeaders As Boolean, _
Optional strHeadersCSV As String, _
Optional bUnderLineHeaders As Boolean, _


--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Aaah, yes,of course!!
Glasses wouldn't help there, but second pair of eyes did!
Thanks for that.

RBS

Aaah, yes,of course!!
Glasses wouldn't help there, but second pair of eyes did!
Thanks for that.

RBS

Hey, I missed it 1st time around and only spotted it after a 2nd read
following Peter's post! (I've been speed reading for the last 4 days
trying to proofread my daughter's manuscript for her next book! My eyes
are toast right now so I thought a cruise here would give me a break.
Ha, ha to that!!<bg)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

wrote in message
Hi Peter,

Can't see what I am not seeing there.

Not even my flags with the pair of <<< !

Peter :)

Hi Peter,

Just didn't look at the Sub arguments as I never considered the trouble would be there. VBA could handle this a bit better as it highlighted:
Sub ArrayToSheet(vArray As Variant, _
none of the duplicate arguments.
Thanks for spotting this.

RBS



On Sunday, 1 March 2015 10:17:02 UTC, Peter T wrote:
wrote in message
Hi Peter,

Can't see what I am not seeing there.

Not even my flags with the pair of <<< !

Peter :)

Hi Bart,

VBA could handle this a bit better as it highlighted:
Sub ArrayToSheet(vArray As Variant, _
none of the duplicate arguments.

I guess it missed the continuation lines, without any it would have
highlighted the entire line

Peter