OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

ActiveWorkbook.Names.Add not working

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
millerke
Newbie
Newbie


Joined: 02 May 2011
Posts: 1

PostPosted: Mon May 02, 2011 8:29 am    Post subject: ActiveWorkbook.Names.Add not working Reply with quote

Hi,

I have the following VBA code which works in Excel but not in OOCalc. The intent is for a worksheet named "Nomenclature" to have a list of variable names in colA, there default values in colB, and an alternative in colC. The two (virtually identical) macros code walk through each row assigning values (e.g. variable ranges) until it finds a blank in colA, also highlighting the column that has the new value. I find this useful for building worksheets that require a lot of changes and re-use for different end-users.

What I find on OOCalc is that the variable range never gets (re)-assigned. I even tried deleting the range before the add, but it never assigns.

Any ideas?
Code:

Rem Attribute VBA_ModuleType=VBAModule
Option VBASupport 1

Sub defaultVars()
'
' Initialize variables in ColA (1) to the default values in ColB (2)
'
    Application.ScreenUpdating = False
    curSheet = ActiveSheet.Name
    Sheets("Nomenclature").Select
    thisCol = 2
    thisRow = 1
    ' Clear all highlighting
    lastRow = Cells(65536, 1).End(xlUp).Row
    lastCol = Cells(1, 255).End(xlToLeft).Column
    Cells(1, 1).Resize(lastRow, lastCol).Interior.ColorIndex = 0
    Do While Cells(thisRow, 1) <> ""
        Cells(thisRow, thisCol).Interior.ColorIndex = 4
        thisName = Cells(thisRow, 1).Value
        thisArea = "=Nomenclature!R" & thisRow & "C" & thisCol
        ActiveWorkbook.Names.Add Name:=thisName, RefersToR1C1:=thisArea
        thisRow = thisRow + 1
    Loop
    Sheets(curSheet).Select
    Application.ScreenUpdating = True
End Sub
Sub cVars()
'
' Initialize variables in ColA (1) to the values in ColC (3)
'
    Application.ScreenUpdating = False
    curSheet = ActiveSheet.Name
    Sheets("Nomenclature").Select
    thisCol = 3
    thisRow = 1
    ' Clear all highlighting
    lastRow = Cells(65536, 1).End(xlUp).Row
    lastCol = Cells(1, 255).End(xlToLeft).Column
    Cells(1, 1).Resize(lastRow, lastCol).Interior.ColorIndex = 0
    Do While Cells(thisRow, 1) <> ""
        Cells(thisRow, thisCol).Interior.ColorIndex = 4
        thisName = Cells(thisRow, 1).Value
        thisArea = "=Nomenclature!R" & thisRow & "C" & thisCol
        ActiveWorkbook.Names.Add Name:=thisName, RefersToR1C1:=thisArea
        thisRow = thisRow + 1
        Loop
    Sheets(curSheet).Select
    Application.ScreenUpdating = True


End Sub

Thanks in advance,

== k+ ==
Code:
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group