Format function causes compile error in Access 2010

[tfurnivall]

I have a database where I have a need to extract data into an Excel spreadsheet.
I am building the spreadsheet but need to construct some range values, using a numeric variable. For example:

const IndexCol="A"
dim IndexRow as long
dim RangeAddress as string

for IndexRow =3 to 8
    rangeaddress=indexcol+format(IndexRow )
next r

This will construct a range address of A3, A4, A5, etc.

My problem is that the line where I use format(IndexRow ) as an expression causes a compile error.

OK - some details. The Excel spreadsheet is being manipulated via automation. This means that the code to manipulate the spreadsheet is running as part of an Access module (actually as an Access object).

This is valid VBA (Help on the VBA format function shows this to be the case, and thousands of lines of Excel VBA code reinforce this). Indeed, using this expression in the Immediate window:

? IndexRow ,format(IndexRow )

works just fine.

My question is (or questions are):
1) Why doesn't this work?
2) Any workarounds that people might think of?

Yours, as ever, perplexed in Manhattan!

Tony

 

[mdlueck]

The Excel spreadsheet is being manipulated via automation. This means that the code to manipulate the spreadsheet is running as part of an Access module (actually as an Access object).

This is valid VBA (Help on the VBA format function shows this to be the case, and thousands of lines of Excel VBA code reinforce this).

This means Access VBA is in control, not Excel VBA. Seems to me Format() is an Excel specific function.

What do you hope to accomplish with:

for IndexRow =3 to 8     
  rangeaddress=indexcol+format(IndexRow )
next r

I successfully drive Excel from Access VBA.

How to drive Excel with VBA (Access) in order to transfer values into spreadsheet
http://www.access-programmers.co.uk/forums/showthread.php?t=233104#post1190025

Perhaps you mean to input formulas into that cell? Or?

 

[tfurnivall]

Thanks for your response, Michael.

This means Access VBA is in control, not Excel VBA. Seems to me Format() is an Excel specific function.

Hmmm. I wasn't aware that VBA was application specific! Yes - I use format() much more in Excel, but I figured that VBA would still make the function available. It certainly does in the Immediate window, as I noted.

What do you hope to accomplish with:
Code:
for IndexRow =3 to 8 rangeaddress=indexcol+format(IndexRow ) next r
I successfully drive Excel from Access VBA.

All I'm trying to show is that this is the sort of function call that creates a compiler error!. In the actual application I am storing a pointer (name) to a worksheet, so that when I export either a single set of survey results, or results from multiple surveys, I can find out where all the associated worksheets are.

(((Yes - exporting enough data to reconstruct a data base is perhaps a sub-optimal idea, but not everyone uses Access!)))

Bottom line, I can't get done what I want to get done, which is to construct a string expression referring to an Excel range, while executing VBA code in an Access environment.
Boo-hoo :-(

Tony

 

[mdlueck]

which is to construct a string expression referring to an Excel range, while executing VBA code in an Access environment.

Not so fast... calm down!

Sample Access VBA code selecting a range within Excel via Automation:

  'Copy the recordset into the worksheet
  strCellName = "B4"
  objExcelWks.Range(strCellName).CopyFromRecordset adoRS

  'Select the columns which data was transfered into
  objExcelApp.ActiveSheet.Cells.Select

  'Auto-fit the column widths
  objExcelApp.ActiveSheet.Cells.EntireColumn.AutoFit

  'Leave the selected cell as the top/left
  objExcelWks.Range("A1").Select

 

[tfurnivall]

Thanks, again, Michael.

The issue is not using the range, it is constructing the (address of the) range.

So, I can reference a range as wrkSheet.Range("A1") with no trouble.
I can even do it somewhat more elegantly by saying:

const FirstSurveyThatIWantToExtract="G2"
wrkSheet,range(FirstSurveyThatIWantToExtract)

But what I want to be able to do is to calculate the address, rather than just "knowing" it. This means that I have to concatenate the Column ("A" or "G" or whatever) with a Row number. In Excel if I try to get the string value of a number it is prepended with a space (ie 3=_3, where _ is a space). Using the format function allows me to get just the digits in the value - in ASCII format. Works for any (reasonable) numeric value.

However, format() doesn't work, even though the Help system says it is available, and the IDE smiles as I put it in, and the Compiler even swallows it (when I ask it to compile the project). It's just the run-time system that gets p*ssed off!

I need to find a way to construct a string that involves the character representation of a number, with no spaces. That's all I want (for now - see recent posting about FileDialog boxes ;-)

Thanks for listening!

Tony

 

[mdlueck]

However, format() doesn't work, even though the Help system says it is available, and the IDE smiles as I put it in, and the Compiler even swallows it (when I ask it to compile the project).

I appear to be successfully using the Format() function in A2007/A2010...

  'Prep the string with some environment state information
  strErrorMsg = "Date: " & Format(Year(dtmNowDate), "00") & _
                [B]Format[/B](Month(dtmNowDate), "00") & [B]Format[/B](Day(dtmNowDate), "00") & _
                " Time: " & [B]Format[/B](Hour(dtmNowDate), "00") & ":" & _
                [B]Format[/B](Minute(dtmNowDate), "00") & ":" & _
                [B]Format[/B](Second(dtmNowDate), "00") & _
                " UserID: " & getusername_GetUserName()

 

[tfurnivall]

I wonder if the date-like flavor of your parameters had any effect. The message was "Array expected". OTOH, a year value is pretty scalar in nature, isn't it!

Here's the code I was trying to use:

'   SurveyIndex now points to the ROW of the entry for this survey
'   Create/Update the data for the row
RangeAddress = CCCBRIDCol + Format(SurveyIndex)
HeaderSheet.Range(RangeAddress) = Me.SurveyID

RangeAddress = CCCBRNameCol + Format(SurveyIndex)
HeaderSheet.Range(RangeAddress) = Me.Name

RangeAddress = CCCBRDateCol + Format(SurveyIndex)
HeaderSheet.Range(RangeAddress) = Date

and the solution I eventually decided upon:

' SurveyIndex now points to the ROW of the entry for this survey
' Create/Update the data for the row
RangeAddress = CCCBRIDCol + Trim(CStr(SurveyIndex))
HeaderSheet.Range(RangeAddress) = Me.SurveyID

RangeAddress = CCCBRNameCol + Trim(CStr(SurveyIndex))
HeaderSheet.Range(RangeAddress) = Me.Name

RangeAddress = CCCBRDateCol + Trim(CStr(SurveyIndex))
HeaderSheet.Range(RangeAddress) = Date

My problem is now actually far more of an example of a lingering resentment against MS that they do not actually think about what people might want to do, but insist on "Our way is the right way". (The same reason I prefer to use unbound controls, etc)

THanks for your help and comments!

Tony


PS I wonder if the absence of a format-string may have had an effect.....

.....Nope. compiler is still expecting an array. Oh well...

 

[PeterF]

As you are concecating strings use "&" in stead of "+", your trying to add something different what can give unwanted results.
Because you are converting to string the "+" works in your last post, trim isn't needed.

const IndexCol="A"
dim IndexRow as long
dim RangeAddress as string

for IndexRow =3 to 8
    rangeaddress=indexcol & IndexRow
next